question archive solve problems using excel formula 1
Subject:FinancePrice:5.94 Bought20
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%