Yield to maturity (YTM) in excel is a function in excel that gives back the yield to maturity of a bond when given its current price, Principal amount and coupon rate, and payment frequency in a year. It can also be understood as the return a debt holder gets by investing in security at its current price. Also known as the internal rate of return, when dealing with compound interest this value has to be accounted for. To calculate YTM in excel you need to have the values of the initial amount invested, the interest rate per year, the duration the principal amount has been invested, the daily rate, monthly rate, or payments quarterly.
This article gives you a step-to-step guide on calculating YTM in Excel workbooks Using Excel's RATE function.
Steps to follow when calculating YTM in Excel using =RATE () function
Let us have the following values to be used in our example. Depending on the values you have you can replace them. Face value = 8000, coupon rate per year = 10% Years to maturity bond = 15 Bond Price = 900. Using the values let us create a YTM.
1. On your computer, open Microsoft Excel.
2. Feed the details given in your cells from A2-A5. I.e. face value, coupon rate per year Years to maturity bond, Bond Price.
3. Adjust the columns to perfectly fit the data put.
4. Input the data as it corresponds to our example in the second column B.
5. In the sixth cell write YTM
6. Here you have the heart of it all; the formula, B6 =RATE(B4, B3*B2, B5, B2) type it in cell B6 and tap Enter to display the answer YTM rate in %.
Where YTM is used
Understanding YTM is very important since it is used to assess anticipated performance.
There is a need to understand YTM because it is used to assess expected performance. The golden rule here is that, despite the constant coupon rate, the expected bond return rate varies with the market price. It is used to indicate how favorable the bond market is. As a result, managing a bond investing portfolio is critical.
The formula for pricing a bond
In order to price a bond you can use this formula
PV = Payment / (1+r)+ Payment / (1+r)+ ..+ Payment + Principle / (1+r)
Syntax derivatives
Pv = Price of the bond
Payment =Also known as the coupon payment. This is the rate of coupon * per value ÷ number of payments per year
r denotes the required rate of return. It is the required rate of return divided * the number of payments per year.
N= Years to the point of maturity
Principal= face value of the bond/per value
From the formula, we can see that the pricing of the bond is dependent on the difference between the rate of coupon and the required rate inferred.
It is easy to calculate YTM in excel just by following the above clear guidelines. Another alternative way that you will need to learn is the IRR function which will relly be resourceful if you dint understand the use of the rate function.