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

AutoSave Book1 Excel Sign in File Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Share Home .111 . AA ーーラ▼ 悲Wrap Text Calibri General Conditional Fornat as Cell Insert Delete Fornat Formatting Table Styles Paste Filter Select Clipboard Font Alignment Number Styles Cells Editing A4 fSettlement date 4 Settlement date 5 Maturity date 6 Annual coupon rate 7 YTM 8 Redemption value 9 Coupon payments 10 DATE(2012,7,31) DATE(2030,5,15) 0.0625 0.079 100 12 Flat price 13 Days since last coupon 14 Days in coupon period 15 Accrued interest 16 Invoice price 17 18 -PRICE(B4,B5, B8,B7,B8,89) COUPDAYBS(B4,B5,2,1) COUPDAYS(B4,B5,2,1) (B13/B14) *B6 100/2 B12+B15 Sheet1 Ready +100% ENG 4:36 AM