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.

Assumptions

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 Function

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:

Important

When the price of a bond is equal to par, then the yield equals the coupon rate.