Computing Bond Issue Price and Preparing an Amortization Table in Excel

On December $31,2021,$ Kaplan, Inc., issues $$400,000$ of $9\%$ bonds that pay interest semiannually and mature in $10$ years $($December $31,2031).$

a$.$ Using the Excel PV worksheet function, compute the issue price assuming that the bonds$$ market rate is $8\%$ per year compounded semiannually. $($Refer to Appendix A for illustration.$)$

Round answer to two decimal places.

Issue price $Answer

b$.$ Prepare an amortization table in Excel to demonstrate the amortization of the book $($carrying$)$ value to the $$400,000$ maturity value at the end of the $20$th semiannual period. $($Refer to Appendix A for illustration.$)$ HINT: The amortization schedule can be completed using the following Excel template: Amortization Schedule

Using the figures from the amortization schedule created in Excel, complete the amortization schedule below for $2022.$

Enter answers using two decimal places.

Date Period Beginning

balance Payment Interest Premium

amortization Premium

balance Book

value

$12/31/210$ Answer

Answer

$06/30/221$

Answer

Answer

Answer

Answer

Answer

Answer

$12/30/222$

Answer

Answer

Answer

Answer

Answer

Answer