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 $886.56.

Answer 1

K = Nx2

Bond Price =∑ [(Semi Annual Coupon)/(1 + YTM/2)^k]     +   Par value/(1 + YTM/2)^Nx2

k=1

K =10x2

886.56 =∑ [(6*1000/200)/(1 + YTM/200)^k]     +   1000/(1 + YTM/200)^10x2

k=1

YTM% = 7.64

=Rate(Years*number of coupon per year,Coupon rate*Par value/(number of coupon per year*100),-Bond price,par value,,)*number of coupon per year

=RATE(10*2,6*1000/(2*100),-886.56,1000,,)*2