solve problems using excel formula

1. Compute the price of a 3.8 percent coupon bond with 18 years left to maturity and a market interest rate of 7 percent. Compute the price again if interest payments are paid semi-annually (solve using semi-annual compounding). Par value is $1000. determine semi and annual compounding need both answers.

 

2. A 6.50 percent coupon bond with 18 years left to maturity is offered for sale at $1,035.25. What yield to maturity [interest rate] is the bond offering? Assume interest payments are paid semi-annually, and solve using semi-annual compounding. Par value is $1000. determine semi and annual compounding need both answers.

 

3. You have just paid $1,135.90 for a bond, which has 10 years before it, matures. It pays interest every six months. If you require an 8 percent return from this bond, what is the coupon rate on this bond? Par value is $1000. 

View CommentsFlag Question

1) Computation of Price of Bond (annual compounding) using PV Function in Excel:

=-pv(rate,nper,pmt,fv)

Here,

PV = Price of Bond = ?

Rate = 7%

Nper = 18 Years 

PMT = $1,000*3.8% = $38

FV = $1,000

Substituting the values in formula:

=-pv(7%,18,38,1000)

PV or Price of Bond = $678.11

 

Computation of Price of Bond (semiannual compounding) using PV Function in Excel:

=-pv(rate,nper,pmt,fv)

Here,

PV = Price of Bond = ?

Rate = 7%/2 = 3.5%

Nper = 18 Years * 2 = 36 Periods

PMT = $1,000*3.8%/2 = $19

FV = $1,000

Substituting the values in formula:

=-pv(3.5%,36,19,1000)

PV or Price of Bond = $675.35

 

2) Computation of Yield to Maturity (annual compounding) using Rate Function in Excel:

=rate(nper,pmt,-pv,fv)

Here,

Rate = Yield to Maturity = ?

Nper = 18 years 

PMT = $1000*6.50% = $65

PV = $1,035.25

FV = $1,000

Substituting the values in formula:

=rate(18,65,-1035.25,1000)

Rate or Yield to Maturity = 6.1703% or 6.17%

 

Computation of Yield to Maturity (Semiannual compounding) using Rate Function in Excel:

=rate(nper,pmt,-pv,fv)*2

Here,

Rate = Yield to Maturity = ?

Nper = 18 years*2 = 36 Periods 

PMT = $1000*6.50%/2 = $32.50

PV = $1,035.25

FV = $1,000

Substituting the values in formula:

=rate(36,32.5,-1035.25,1000)*2

Rate or Yield to Maturity = 6.1729% or 6.17%

 

3) Computation of Coupon Payment using PMT Function in Excel:

=pmt(rate,nper,-pv,fv)

Here,

PMT = Coupon Payment = ?

Rate = 8%/2 = 4% 

Nper = 10 Years * 2 = 20 Periods

PV = $1,135.90

FV = $1,000

Substituting the values in formula:

=pmt(4%,20,-1135.90,1000)

PMT or Coupon Payment = $50 (Semiannual)

 

Coupon Rate = Annual Coupon Payment/Par Value 

= ($50*2)/$1,000

= $100/$1,000

Coupon Rate = 10%