# Calculate yield of maturity for a certain price in excel

I have a bond with a time to maturity of 5, a nominal value of $100, coupons of \$3,- and an yield price that I need to calculate so that the bond price equals \$100,-. This yield value is symbolized by y in the following formula:$P_{markt}=\sum\limits_{t=1}^{5}\frac{3}{(1+y)^t}+\frac{100}{(1+y)^{11}}$Is there a way to easilly calculate this value of$y\$ in excel? At the moment, I sum all the bond payments and the present value, and change the interest rate until I have the right value. Can this be done more efficient?

## 1 Answer

This formula in excel should do it:

=RATE((5*1),((3/100)*100),-100,100)


You'll find it's 3% since YTM = coupon when the nominal value = market price.

Thus, for the bond's market price to = 100 (face value), the YTM will be the same as the coupon which in this case = (payment/face value) = (3/100) or 3%. You can change the market price of the bond (-100 in the formula) and y will adjust to the appropriate figure.

By hand, guessing & checking is the only way to get an exact figure (unless you have a financial calculator). However, for an estimate you can use the following: