YTM stands for yield to maturity. It is a common term used in the financial industry. Whether you are buying or selling a bond then, you need to calculate YTM. Bonds are mostly sold by companies, financial institutions, and even Governments. As an investor, it is important to calculate the YTM of a bond in order to find out its projected returns. One of the best ways to calculate the yield to maturity of a bond is by using Excel.
In this tutorial, I will guide you on different ways to calculate YTM in Excel.
Method 1: Use direct Formula to calculate the YTM of a bond
This method we the actual YTM of a bond formula and apply it to Excel. The actual Formula to calculate YTM can be written as Follows.
YTM=(C+(FV-PV)/n)/(FV+PV/2)
Where
C: is the Annual Coupon Amount
FV: is the Face Value
PV: is the Present Value
N: refers to years to maturity
Let’s use the following data to illustrate how to calculate YTM using direct Formula
Face value = $2000
Present Value = $2100
Annual Coupon = $40
Years of Maturity = 15
Yield to maturity =?
Follow these steps to calculate in Excel.
1. Launch your Excel program and create a new worksheet
2. Create all the filled we have created above and fill in the values in the adjacent cells.
3. Click on cell B8 and type in the following formula
=(B6+((B4-B5)/B7))/(B4+B5/2)
4. Press enter, and Excel will compute the Yield to Maturity automatically
5. In case you get an answer like the one below, then you need to format the cell as a percentage
6. Right Click on it and then select format cells
7. On the new pop-up window, select percentage, then click ok
8. Now your answer should appear as shown in the image below
Method 2: Use the Rate() function to calculate YTM in Excel
The simplest way to calculate Yield to Maturity in Excel is by using the inbuilt Rate function. This function features arguments that are computed to produce YTM. Follow these simple steps:
1. Arrange the following fields in your Excel file
Par Value of Bond: $3000
Coupons Per Year: 6
Current Bond Price: $1900
Number of Coupons: 50
Periods: 20
Yield To Maturity (YTM):
2. Type the following Formula on cell 9B
=RATE(B8,B7,-B6,B4)*B5
3. Press the enter button to apply the Rate formula
As you can see from the image above, we have utilized the rate function to calculate YTM
Method 3: Calculate the YTM of a Bond using the IRR function
We can get the YTM of a bond by multiplying the by the number of coupons per year. In this method, I will show you how to use the IRR function to calculate YTM. The IRR function takes the values that we have provided to calculate the internal Rate of return.
1. Arrange the field and their respective values as follows.
2. Write the following Formula in the IRR section
=IRR(C5:C9)
3. Press enter
4. Write the coupons per year
5. Multiply the IRR by the number of coupons per year to get the YTM
=C10*C11
Method 4: Use the YIELD function
The inbuilt Excel YIELD() function can help users calculate YTM. To calculate yield to maturity using the YIELD function, you need the following fields.
SD: Settlement Date
MD: Maturity Date
Rate: Coupon Rate
PR: Market value
Redemption: Call Price, also known as Par Value
Frequency: Frequency of payments
Basis: Day Count basis
The Yield function can be written as
= YIELD (SD, MD, Rate, pr, redemption, frequency, [basis])
Follow these steps
1. Arrange your fields and values as shown in the image below.
2. Enter the following Formula on cell 11B
=YIELD(B6,B7,B5,B10,B4,B8)
3. Press Enter, and Excel will compute the YTM of your values automatically
Similar Read: How to calculate per capita in Excel
Method 5: Using VBA code to calculate YTM in Excel
Excel is one of the best finance software thanks to the expandable features using VBA. It stands for Visual Basic Application. You can use it to create Macro programs that run inside Excel Software. Therefore, you can write a VBA code Tailored to calculate the YTM of a bond. Follow these simple steps.
1. Open an existing or create a new Excel File
2. Click on the developer tab, then click on Visual Basic (In case you don’t see the developer tab on your Excel menu. Click on File>Options>Customize Ribbon and Add the Developer tab)
3. After clicking Visual Basic, a new window will pop up. Click on Insert>Module
4. Copy and Paste the VBA code below
Function YTM(F As Double, P As Double, C As Double, N As Integer, Optional Guess As Double = 0.1) As Double
'This function calculates Yield to Maturity (YTM) using Excel's built-in RATE function.
YTM = WorksheetFunction.Rate(N, C, -P, F, Guess)
End Function
5. Head over the back to the worksheet and calculate your BOND using the function that we have just created
For example, if you have a bond with a face value of $1000, a price of $950, a coupon rate of 5%, a coupon frequency of semi-annual, and five years to maturity, you can use the following Formula on the Excel cell
==YTM(1000,950,0.05,5)
As you can see from the image above, we have used the VBA code to calculate the YTM of a bond
YTM Excel Calculator Download
Download the YTM Excel calculator template for free
Final Thoughts
Use any of the five methods we have discussed above to calculate YTM. You can choose any method that conforms to the values that we have. You can use the free template provided for practice. In case you have any questions, let us know in the comment section.
