Question

please use excel with as much detial as possible

38. Suppose that you purchase a bond that matures in five years and pays a 13.76 percent coupon rate. The bond is priced to yield 10 percent. (LG 3-6) a. Show that the duration is equal to four years. b. Show that if interest rates rise to percent next year and your investment horizon is four years from today., you will sill lam a 10 percent yield on your investment.

Answer 1

1.

There is a function in excel DURATION

the syntax is =DURATION(settlemtdate,maturitydate,coupon,yield,frequency of coupon payments)

Given:

Coupon rate=13.76%

Yield=10%

Maturity=5 years

So we can use random settlement date as 1/1/2013 and maturity date as 5 years away from the settlement

Use the below formula

=DURATION(DATE(2013,1,1),DATE(2018,1,1),13.76%,10%,1)

=4 years

2.

Purchase price=PV(10%,5,13.76%*100,100)=114.253

Price after 4 years=PV(11%,1,13.76%*100,100)=102.486

Returns=((13.76*(1+1.11+1.11^2+1.11^3)+102.486)/(114.253))^(1/4)-1=10%