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 İn 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
Save for later
Found this helpful?
Pin this article to your Pinterest board and come back to it whenever you need a reminder.
Save to Pinterest
