Question

Bond Pricing Excel FILE HOME INSERT PAGE LAYOUT FORMULAS Sign In DATA REVIEW MEW Calibri 11 A A Paste Conditional Format as Formatting Table Styles Styles Alignment Number Cell I U Cells Editing Clipboard Font H11 H E G 1 On January 1, Ruiz Company issued bonds as follows: 2 Face Value: 3 Number of Years: 4 Stated Interest Rate: 5 interest payments per year 6 (Note: the bonds pay interest semi-annually.) 500,000 30 7 % 2 8 Required: 9 1) Given the different market interest rates below, calculate the following items. 10 Calculate the bond selling price USING THE EXCEL PV FUNCTION (fx). Note: Enter all 11 function arguments as cell references. 12 13 ja) Market Interest Rate: 9% 14 i Semiannual Interest Payment: 15 Bond Selling Price: 16 17 b) Market Interest Rate: 5.5% 18 Semiannual Interest Payment: 19 Bond Selling Price: 20 21 2. Use the Excel IF function to answer either "Premium" or "Discount" to the following items. 22 23 The bond in (a) sold at a: 24 The bond in (b) sold at a: Sheet1 Niovt

Answer 1

Answer 1(A)

Calculation of PV of Interest Payments
Semi Annual Interest Payments	17500
PVIFA @ 4.5% for 60 semi annual years	20.63802
PV of Interest Payments (A)	3,61,165.39
PV of Face Value Redemption:
Bond Maturity Value	500000
PVIFA @ 4.5% for 60 semi annual years	0.07129
PV of Redemption (B)	35,644.50
Selling Price of the Bond	3,96,809.89

Answer 1(B)

Calculation of PV of Interest Payments
Semi Annual Interest Payments	17500
PVIFA @ 2.75% for 60 semi annual years	29.22266
PV of Interest Payments (A)	5,11,396.59
PV of Face Value Redemption:
Bond Maturity Value	500000
PVIFA @ 2.75% for 60 semi annual years	0.19638
PV of Redemption (B)	98,188.40
Selling Price of the Bond	6,09,584.98

Answer 2

Where the market interest rate is 9% p.a, the bond was selling at $ 396809.89. i.e less than the face value of the bond and thus was sold at discount of $ 103,190

Where the market interest rate is 5.5% p.a, the bond was selling at $ 609584.98. i.e higher than the face value of the bond and thus was sold at premium of $ 109,584.

Where the market rate is higher than interest rate of the bond, the bond will be issued at discount and if the marker rate is lesser than interest rate on bond, the bond will be issued at a premium.