Can you please help solve the formula portions and show the formulas used? PROBL
ID: 2417090 • Letter: C
Question
Can you please help solve the formula portions and show the formulas used?
PROBLEM DATA
The University Club recently issued $1,500,000 of 10-year, 9% bonds at an effective interest rate of 10%. Bond interest is payable annually.
REQUIREMENTS
1. You have been asked to calculate the issuance price of the bonds and prepare amortization schedules for any discount or premium. The worksheet BONDS has been provided to assist you. Note that the worksheet contains a scratch pad at the bottom that has been preprogrammed to automatically compute and display the relevant cash flows needed for bond pricing.
2. The bond pricing formula will utilize the NPV (Net Present Value) function on your spreadsheet program. The NPV function will automatically compute the net present value of annual future cash flows discounted at a specific interest rate. The interest rate should be expressed as a cell address.
BONDS Bond Pricing and Amortization Data Section Face value of bond $1,500,000 Years to maturity * 10 Stated interest rate 9.0% Effective interest rate 10.0% * Worksheet is designed for use with bonds having a maturity of 12 years or less and paying interest annually. Answer Section Bond issue price FORMULA1 Amortization Schedule - Straight Line Method Cash Interest (Disc.) Bond Year Paid Amortization Expense Premium Carrying Value 0 FORMULA2 FORMULA3 1 FORMULA4 FORMULA5 FORMULA6 FORMULA7 FORMULA8 2 FORMULA9 FORMULA10 FORMULA11 FORMULA12 FORMULA13 3 0 0 0 0 0 4 0 0 0 0 0 5 0 0 0 0 0 6 0 0 0 0 0 7 0 0 0 0 0 8 0 0 0 0 0 9 0 0 0 0 0 10 0 0 0 0 0 11 0 0 0 0 0 12 0 0 0 0 0 Amortization Schedule - Effective Interest Method Interest Cash (Disc.) Bond Year Expense Paid Amortization Premium Carrying Value 0 FORMULA14 FORMULA15 1 FORMULA16 FORMULA17 FORMULA18 FORMULA19 FORMULA20 2 FORMULA21 FORMULA22 FORMULA23 FORMULA24 FORMULA25 3 0 0 0 0 0 4 0 0 0 0 0 5 0 0 0 0 0 6 0 0 0 0 0 7 0 0 0 0 0 8 0 0 0 0 0 9 0 0 0 0 0 10 0 0 0 0 0 11 0 0 0 0 0 12 0 0 0 0 0 Scratch Pad Display of relevant cash flows Annual Bond Year Interest Maturity 1 135000 0 2 135000 0 3 135000 0 4 135000 0 5 135000 0 6 135000 0 7 135000 0 8 135000 0 9 135000 0 10 135000 1500000 11 0 0 12 0 0Explanation / Answer
Solution:
Please ask one question per post
Present value of bond = Present value of redeemable value + Present value of coupon payments Redeemable value 1,500,000 Coupon Payment - 9% * 1,000 135,000 Periods, 10 years 10 Present value of redeemable value Redeemable value 1,500,000 PVIF @ 10 % for 10 years 0.386 Present value of redeemable value 578,250.00 Present value of coupon payment Coupon Payment - 9% * 1,000 135,000 PVAF @ 10 % for 10 years 6.145 Present value of coupon payment 829,575 Present value of bond 1,407,825 The bond is at discount