The `ODDLTYIELD` function in Excel is used to calculate the yield of a security with an odd last period, which occurs when the final coupon period is shorter or longer than usual. This is common for bonds with irregular first or last payment periods.
Syntax
ODDLTYIELD(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis])
Parameters
- settlement: The settlement date of the security. This is the date after the issue date when the security is traded to the buyer.
- maturity: The maturity date of the security. This is when the security expires or the final payment is due.
- last_interest: The date of the last coupon payment.
- rate: The annual coupon rate of the security.
- pr: The price per $100 face value of the security.
- redemption: The redemption value per $100 face value of the security.
- frequency: The number of coupon payments per year. It can be:
- 1 for annual payments,
- 2 for semi-annual payments,
- 4 for quarterly payments.
- basis (optional): The day-count basis to be used. It can be:
- 0 or omitted for 30/360,
- 1 for actual/actual,
- 2 for actual/360,
- 3 for actual/365,
- 4 for 30/360 (European).
Example
Suppose you have a bond with:
- Settlement date: January 1, 2022.
- Maturity date: December 31, 2022.
- Last interest date: July 1, 2022.
- Annual coupon rate: 5%.
- Price: $95.
- Redemption value: $100.
- Payments: Semi-Annual.
Here’s how you would write the `ODDLTYIELD` formula:
=ODDLTYIELD(DATE(2022,1,1), DATE(2022,12,31), DATE(2022,7,1), 0.05, 95, 100, 2)
Notes
- Ensure that the settlement and maturity dates are inputted using the DATE function or result from other date functions/formulas. If these are entered as text, Excel may not recognize them correctly, leading to errors.
- The `ODDLTYIELD` function can be particularly useful in financial modeling and for anyone dealing with bond investments that encounter odd or short final periods.
By using the `ODDLTYIELD` function correctly, you can effectively calculate the yield on bonds with non-standard last coupon periods, which provides a more accurate measure of investment return in such scenarios.
Unlock Your Potential
Excel
Access
Power BI
