PLEASE USE EXCEL FUNCTIONS AS INDICATED AND THE CORRESPONDING CELLS TO SOLVE FOR

Question

PLEASE USE EXCEL FUNCTIONS AS INDICATED AND THE CORRESPONDING CELLS TO SOLVE FOR EACH ANSWER

2 Face Amount 3 Number of Years: 4 Stated Interest Rate: 5 Interest payments per vear 6 (Note: the bonds pay interest semi-annually.) 500,000 15 8% 2 8Required: 91) 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 1 function arguments as cell references 12 13 a) Market Interest Rate: 14 Annual Interest Payment: 15 Bond Selling Price: 16 17 b) Market Interest Rate: 18 Annual Interest Payment: 19 Bond Selling Price: 20 21 2. Use the Excel IF function to answer either "Premium" or "Discount" to the following items. 9% 6.0% 23 The bond in (a) sold at a: 24 The bond in (b) sold at a: 25

Explanation / Answer

1) a) Annual Interest Payment = Face Amount*Interest Rate

= $500,000*8% = $40,000

Semi Annual Interest Payment = $40,000*6/12 = $20,000

Semi Annual Market Interest Rate = 9%*6/12 = 4.5%

Semi Annual Periods = 15 yrs*2 = 30 periods

Bond Selling Price = [Semi Annual Interest Payment*PVAF(4.5%, 30)]+[Face Amount*PVF(4.5%, 30)]

= ($20,000*16.28889)+($500,000*0.2670)

= $325,778+$133,500 = $459,278

1) b) Annual Interest Payment = Face Amount*Interest Rate

= $500,000*8% = $40,000

Semi Annual Interest Payment = $40,000*6/12 = $20,000

Semi Annual Market Interest Rate = 6%*6/12 = 3%

Semi Annual Periods = 15 yrs*2 = 30 periods

Bond Selling Price = [Semi Annual Interest Payment*PVAF(3%, 30)]+[Face Amount*PVF(3%, 30)]

= ($20,000*19.60044)+($500,000*0.41199)

= $392,009+$205,995 = $598,004

2) The bond in a) sold at a discount of $40,722 ($500,000 - $459,278)

The bond in b) sold at a premium of $98,004 ($598,004 - $500,000)

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.