YTM Excel Function
Written by:Abraham A.Learn to find YTM with MS Excel formula, download easy to use Excel template. YTM, How to use MS Excel to Find Yield till maturity on non zero couon bonds. Get Free Template. On this web page we show you how to use MS Excel formula or function to compute YTM or yield to maturity on bonds.
What is YTM
YTM or yield to maturity is akin to IRR or internal rate of return, YTM is the market rate at which the discounted sum of bond's interest payment and bond's terminal value equals the bond's current market price.
YTM MS Excel Example
We will illustrate how you can use RATE formula in MS Excel to find YTM or Yield till matuity on Bonds, The RATE function is used is different scenarios depending on what sort of rate we are trying to find. I will start off with an example calculation of YTM that will show you how the RATE formula in Excel is used to find YTM.
The Heymann Company's bonds have a 4 years remaining to maturity. Interest is paid annually, the bonds have a $1,000 par value and the coupon interest rate is 9 percent. What is the yield to maturity at a current market price of (1) $829 and (2) $1,104
In Cell A1, Type the text Par Value. In Cell B1 enter the value 1000. In Cell C1 type 1000
In Cell A2, Type the text Market Price, In Cell B2 type 829. In Cell C2 type 1104
In Cell A3, Type the text Coupon Rate, In Cell B3 type 9%. In Cell C3 type 9%
.
In Cell A4, Type the text Years to Maturity. In Cell B4 type 4. In Cell C4 type 4
.
In Cell A5, Type the text YTM.
In Cell B5 type in the following formula
.
In Cell C5 type in the following formula
.
Semiannual Compounding
Most bonds in the US make semi annual payments thus if the bond interest rate has semi annual compounding we will tweak the above mention formula a bit as follows to take into consideration the twice a year interest payment.
In Cell B6 type in the following formula
.
In Cell C6 type in the following formula
.
MS Excel YTM Template
Download this MS Excel Template to find Yield till Maturity| A | B | C | D | |
| 1 | Par Value | $1,000 | $1,000 | |
| 2 | Market Price | $829 | $1,104 | td align="center">|
| 3 | Coupon Rate | 9% | 9% | |
| 4 | Years to Maturity | 4 | 4 | |
| 5 | YTM | 15% | 6% | |
| 5 | YTM (Semi Annual) | 7% | 3% |
Related Bonds Excel Templates
Following is a list of related Excel templates that cover other aspects of bond valuation used in financial management:YTM Calculator
You can opt to use this YTM Calculator that calculates yied to maturity of a bond when you provide bond's maturity value, market price, coupon rate, and years to maturity on bonds. It also lets you select the frequency of interest payments whether annual or semi-annual.