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.
