Yield Between Coupon Dates

There are several ways you can calculate the yield to maturity for dates that fall between coupon dates:
1) Using the IRR function
2) Using the YIELD function
3) Using the XIRR function
4) Using the Secant Method
5) Using the Bisection Method
6) Using the Newton Raphson

Assumptions

The following assumptions are made:
* It is assumd that the bond is held to maturity
* Every cashflow is discounted at the same discount factor or rate (flat yield curve)

IRR Function

IRR(values [,guess]) 

IRR - Returns the interest rate for a series of unequal cash flows at regular intervals (without reinvestment).
This function is an acronym for Internal Rate of Return.
The word internal refers to the assumption that is uses the same rate throughout and is held to maturity.
This uses a flat yield curve.
Each cash flow is discounted at the same rate.
This function can calculate the yield to maturity on any date.

The yield returned by this function is actually the semi-annual yield so we need to multiple our answer by 2.

YIELD Function

YIELD(settlement, maturity, rate, price, redemption, frequency [,basis]) 

YIELD - Returns the interest rate (annual) for a series of equal cash flows at regular intervals.
This function can calculate the yield to maturity on any date.

This function automatically includes the accrued interest. This is the convention in the US and Canadian markets.

annual / semi-annual / compounding ??

When you use this function be aware of the following implicit assumptions:
1) Bond will be held to maturity
2) All cash flows are discounted at the same flat yield (which rarely exists in the real world)
3) All cash flows are assumed to be received prompty with no delays and invested immediately at the same rate ??

XIRR Function

XIRR(values, dates [,guess]) 

XIRR - Returns the interest rate for a series of unequal cash flows at irregular intervals.
This function can calculate the yield to maturity on any date.

This function returns the effective annual yield
This function returns the annualised IIR.

Is this the Effective Annual Yield (EAY) with semi-annual compounding done properly ?

There are also YIELDMAT and YIELDDISC functions

The Bisection Method

More details Mathematics > Root Finding > Bisection Method
You could use a binary search ?
Guess a value for "r"
If the resulting PV > price, pick a higher "r"
If the resulting PV < price, pick a lower "r"

The Secant Method

More details Mathematics > Root Finding > Secant Method
A well known root finding algorithm that can be used iteratively to find a solution.
The main advantage of this method over the other techniques is that we do not need to calculate the first derivative
Root finding algorithm assumes a function is approximately linear at the point of interest.
Assumptions: coupons paid annually
Assumptions: we are always on a coupon payment
EQ
This is an iterative process
This is a root finding algorithm
This method requires two initial values
In our case this would be the minimum possible yield and the maximum possible yield.
Lets take the minimum to be 0.1% and the maximum to be 50%

To speed things up we can use cumulative multiplication rather than exponentials to compute the new discount factor each time through the inner loop which computes the PV

Newton Raphson

More details Mathematics > Root Finding > Newton Raphson Method
This requires the evaluation of the first derivative.

Conventions

We divide / multiple by 2 to convert between annual yield and semi-annual periodic yield
We usually quote semi-annual yield, not effective annual yield.
For periods below semi-annual (quarterly or monthly) the following formula can be used to convert:

Alternative Calculation

It is possible to remove these assumptions by doing:
1) Instead of thinking of 1 bond with several cash flows, change this to a stream to zero-coupon bonds
2) Value each zero-coupon bond at a different rate based on its maturity