Question

The spreadsheet shows you how to calculate invoice price for 6-year maturity bond with a coupon rate of 2.25% (paid semiannually). The market interest rate given is 7.9%. Now, please modify the spreadsheet and calculate invoice price of bond with 6.25% coupon (paid semiannually), settlement date July 31,2012, maturity date May 15, 2030, YTM 7.9%.

                                                2.25% coupon bond,     

                                               maturing July 31, 2018                 Formula in column B

                       

Settlement date                                     7/31/2012                     =DATE(2012,7,31)

Maturity date                                        7/31/2018                     =DATE(2018,7,31)

Annual coupon rate                               0.0225

Yield to maturity                                   0.079   

Redemption value (% of face value)       100      

Coupon payments per year                     2                            

                       

Flat price (% of par)                              73.4101                        =PRICE(B4,B5,B6,B7,B8,B9)

Days since last coupon                          0                                  =COUPDAYBS(B4,B5,2,1)

Days in coupon period                           184                               =COUPDAYS(B4,B5,2,1)

Accrued interest                                    0                                  =(B13/B14)*B6*100/2

Invoice price                                         73.4101                        =B12+B15

Answer 1

Invoice price = $85.67047

Settlement date	7/31/2012
Maturity date	5/15/2030
Annual coupon rate	0.06
YTM	0.079
Redemption value	100
Coupon payments	2
Flat price	84.3627228
Days since last coupon	77
Days in coupon period	184
Accrued interest	1.307745
Invoice price	85.67047

WORKINGS