1
$\begingroup$

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?

|improve this question
$\endgroup$
0
$\begingroup$

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:

enter image description here

|improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.