Bond yield to maturity (YTM) is the total rate of return that an investor would earn by buying a bond at its current market price and holding it until it matures. It is based on the assumption that all the interest payments (coupons) and the principal (face value) are received on time and reinvested at the same rate as the YTM.

To calculate the YTM, one needs to find the interest rate that makes the present value of all the future cash flows from the bond equal to its current market price. This is not easy to do, because the interest rate affects both the present value and the reinvestment rate. Therefore, one has to use a trial-and-error method or a mathematical tool to find the YTM.

The YTM is different from the coupon rate, which is the fixed percentage of the face value that the bond pays every year. The coupon rate does not change, but the YTM does, depending on the bond’s market price. If the bond’s price is lower than its face value, the YTM is higher than the coupon rate, because the investor can buy the bond at a discount and earn more interest. If the bond’s price is higher than its face value, the YTM is lower than the coupon rate, because the investor has to pay a premium and earn less interest.

The YTM is important for investors because it shows the true return of a bond, taking into account both the interest payments and the capital gain or loss. It also allows investors to compare different bonds with different prices, coupons, and maturities. However, the YTM is only an estimate, and it may not be accurate if the bond is called, put, or sold before maturity, or if the interest payments or the reinvestment rate change over time.

## Basic Theory

The Yield to Maturity (YTM) of a bond is the internal rate of return (IRR) of its expected cash flows, considering the bond’s current market price. It represents the total return an investor can expect to receive if the bond is held until maturity, assuming all interest and principal payments are made as scheduled.

The formula for YTM involves solving a present value equation for the bond’s future cash flows, accounting for the periodic interest payments and the return of principal at maturity.

## Procedures for Calculating YTM in Excel

### Step 1: Understand the Variables

**Face Value (FV):**The nominal value of the bond.**Coupon Payment (C):**The periodic interest payment.**Years to Maturity (n):**The number of years until the bond matures.**Yield to Maturity (YTM):**The rate of return expected by the investor.

### Step 2: Set Up Excel Sheet

Create an Excel table with columns for:

**Period (t):**representing each period until maturity.**Cash Flow (CFt):**including coupon payments and the final principal payment.

### Step 3: Calculate Cash Flows

In the “Cash Flow” column, input the coupon payments for each period and the principal payment at maturity.

### Step 4: Use Excel’s RATE Function

Use the `RATE`

function to calculate YTM. The formula would be:

`=RATE(n, C, -B2, FV, 0)`

Where B2 is the initial cash outflow (the bond’s purchase price).

## Comprehensive Example

Let’s consider a scenario:

**Face Value (FV):**$1,000**Coupon Rate:**5%**Years to Maturity:**5**Current Market Price:**$950

### Excel Table

Period | Cash Flow |
---|---|

1 | 50 |

2 | 50 |

3 | 50 |

4 | 50 |

5 | 1,050 |

### Calculation

Assuming the bond is currently priced at $950:

`=RATE(5, 50, -950, 1000, 0)`

### Result

The calculated YTM is approximately 6.22%.

## Other Approaches

### Trial and Error

You can also use Excel’s Goal Seek function to iteratively find the YTM by setting the Net Present Value (NPV) of cash flows to zero.

### Excel Add-Ins

There are Excel add-ins, like Bloomberg Excel Tools, that provide functions for bond analytics, including YTM calculation.