Assume 10 year, $100,000 face value bonds with an annual stated (coupon) interes

Question

Assume 10 year, $100,000 face value bonds with an annual stated (coupon) interest rate of 7.0% are sold on January 1, 2016. Coupon (interest) payments are made semiannually on July 1 and January 1 of each year with the first payment on July 1, 2016. The bonds are sold on January 1, 2016 at a price that will result in an annual effective rate of 7.4%.

Use your financial calculator to calculate the selling price of the bonds or enter a formula using one of the Excel functions into Cell F13 that will calculate the selling price of the bonds using the appropriate variables in Cells F7 to F12. Enter the selling price into Cell F13 if you used your financial calculator to calculate the selling price of the bonds

Prepare an amortization schedule starting with the original loan balance (the selling price of the bonds) in Cell F18. Enter formulas into Cells C19 to F19 that will compute the amounts and round them to the nearest dollar. Use =ROUND(formula,0) See the formula in cell C39. (The amount in Cell C 39 is the payment of the face value on the maturity date.) Then copy the formulas down to the remaining cells.

After the amortization schedule has been completed, enter a formula into cells C41 to E41 that will compute the sum of the Payment, Interest and Principal columns.

Also use the amortization schedule to compute the financial statement amounts required in Cells J9, J13, J14 and J18 and enter those amounts.

Explanation / Answer

Selling Price of the Bond 1 Annual Interest payment (100000 x 7%) 7000 2 Present value of Bond (100000 x 0.48973) 48973 3 Present value of Interest payment (7000 x 6.8953) 48267 4 Bond Price 97240 Amortization Schedule Date Payment Interest Change in Balance Loan Balance 1/1/2016 $97,240 1/7/2016 3638 3500 $97,378 $97,378 1/1/2017 3638 3500 $97,516 $97,516 1/7/2017 3638 3500 $97,654 $97,654 1/1/2018 3638 3500 $97,792 $97,792 1/7/2018 3638 3500 $97,930 $97,930 1/1/2019 3638 3500 $98,068 $98,068 1/7/2019 3638 3500 $98,206 $98,206 1/1/2020 3638 3500 $98,344 $98,344 1/7/2020 3638 3500 $98,482 $98,482 1/1/2021 3638 3500 $98,620 $98,620 1/7/2021 3638 3500 $98,758 $98,758 1/1/2022 3638 3500 $98,896 $98,896 1/7/2022 3638 3500 $99,034 $99,034 1/1/2023 3638 3500 $99,172 $99,172 1/7/2023 3638 3500 $99,310 $99,310 1/1/2024 3638 3500 $99,448 $99,448 1/7/2024 3638 3500 $99,586 $99,586 1/1/2025 3638 3500 $99,724 $99,724 1/7/2025 3638 3500 $99,862 $99,862 1/1/2026 3638 3500 $100,000 $100,000 1/7/2026 100000 $0 $0 Working Note: Present value of $1 at 7.4%, period 10 0.48973 Present value of annuity @7.4% for 10 years 6.8953

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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