How to Calculate YTM With Coupon Rate
The yield to maturity (YTM) calculation provides a way to compare bonds of different coupon rates and prices. YTM also gives an accurate representation of the actual return an investor will receive and hold the bond until maturity and the face amount is repaid.
Calculating YTM by hand is not possible, but it can be done using a simple spreadsheet.
4 Steps to Calculate YTM With Coupon Rate
1. List the data needed to calculate the YTM of the bond.
In addition to the coupon rate, you need the purchase price, the par value (value of the bond at maturity) and the maturity date.
2. Set up two columns in a spreadsheet, one will be the label and the second will be the associated value.
The entries in the label column will be settlement date, maturity date, coupon rate, price, face value, payment frequency and yield to maturity or YTM.
3. In the second column enter the appropriate values for the bond.
Settlement date is the purchase date of the bond. Coupon rate is expressed as an annual percentage. The bond price and face value should be expressed as a percentage of par. Face value is usually 100, and price will look like 98.472 if you paid $98,472 for a $100,000 bond. Payment frequency for most bonds is twice a year, so the value will be 2.
4. Next to the YTM label use the YIELD function with the other data as parameters.
If your date is in the “B” column starting in the first row, the function will look like this:
=YIELD(B1,B2,B3,B4,B5,B6,0).
Once this function is set up in the cell, the resulting value will be the yield to maturity.
Tips and Warnings
- The YIELD function with the required values listed is: YIELD(settlement,maturity,rate,pr,redemption,frequency,basis). If something is not working, the spreadsheet help function can help with the definitions.
- Do not forget the = sign at the beginning and the last 0 inside the parentheses.
- The interest rate and date data need to be formatted correctly. Use format cells menu to set the up the individual spreadsheet cells.
- The YIELD function is available in most spreadsheet programs including the free Open Office Calc.
- This rough results check can be used to determine if your results make sense:
- If the bond sold at a discount, the coupon rate should be less than the YTM.
- A premium bond should have a coupon rate greater than the YTM.
You Might Also Like :: How to Find Out How Much Saving Bonds Are Worth
