### 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).

IRR - This uses a flat yield curve. The word internal refers to the assumption that is uses the same rate throughout and is held to maturity.

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

© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext