Question

Please explain how to answer the following question using Excel:

Find the yield to maturity on a 10-year, 6% semiannual coupon bond, selling for $1,282.78.

Answer 1

Given

Face value of Bond F=$1000

coupon rate =6%

Coupon C=6%*1000/2=$30 per semiannual

N=10*2=20 semi annuals

Price of Bond P=$1282.78

Let r be the semi annual yield to maturity

P=C*(1-(1+r)^-N)/r+F/(1+r)^N

1282.78=30*(1-(1+r)^-20)/r+ 1000/(1+r)^20

Solving in excel for r we get r=1.37% per semiannual

So Annual Yield to maturity YTM=2*r=2*1.37%=2.75%

In Excel we use RATE function which is =RATE(NPR,PMT,PV,FV)

So

RATE(20,30,-1282.78,1000)

1.37%

We use price of bond as negative because it is cash out flow and rate from above equation is semi annual rate . To calculate annual rate you have to multiply semi annual rate by 2.

NPR	20	Number of Payment
PMT	30	Payment amount
PV	-1282.78	Price of Bond
FV	1000	Face value