Yield On A Coupon Date
You can calculate the yield to maturity on a coupon date by:
1) Using the RATE function.
2) Using the Goal Seek Iterative technique.
The following assumptions are made to simplify the calculation:
* The calculation can only be performed on a coupon date.
* It is assumed that the bond is held to maturity
* Every cash flow is discounted at the same discount factor or rate. (flat yield curve)
There are plenty of problems in finance that require solving for a single parameter in a function where it is difficult (or impossible) to isolate a parameter exactly.
Lets imagine we have the following bond:
2 year treasury bond
Coupon rate of 6% (paid semi-annually)
Principal of $1,000
Current price is $950
What is the yield to maturity (or bond yield) ?
The following equation needs to be solved, where r represents the yield to maturity.
RATE(nper, pmt, pv [,fv] [,type] [,guess])
RATE - Returns the interest rate for a series of equal cash flows at regular intervals.
This function can only be used to calculate the yield to maturity on an coupon date (or interest payment date).
The yield returned by this function is actually the semi-annual yield so we need to multiple our answer by 2.
ammend the formula in SS and include all the arguments in this screen shot.
This returns the BEY
above - rows 22 and 23 - remove the (*2) - these are semi-annual IRR
The equivalent annualised IRR would be (1 + C22)^2 - 1
Goal Seek - Iterative Approach
An iterative approach can be used by using Goal Seek
The yield returned here is the 'periodic yield. (or semi-yield)
We can annualise this periodic yield by multiplying by 2.
The annualised semi-annual periodic yield is (A * 2)
This gives us the semi-annual bond equivalent yield (BEY)
We can cross reference our answer:
When the price of a bond is equal to par, then the yield equals the coupon rate.
© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext