On January 1, 2018, BoomTown Industries issued $1,250,000 of 7% bonds, due in 15 years, with interest payable semi-annually on June 30 and December 31 each year. The market rate of interest was 9% at the time the bonds were issued.(Please show the excel document and how to do ti)

Required:

Prepare a bond amortization schedule using Excel.

See additional instructions below. Required items in Excel:

1. Calculate issue price showing the calculations for each cash flow streams interest and the lump sum.

2. Input cells, which can be hard-keyed, are required for the following items: Issue price, Face value, Stated interest rate, Market interest rate, and Interest payment per period. Interest rates should be prorated to match the frequency of interest payments.

3. The amortization table should have the five column titles we used in class: Date, Cash Interest, Interest Expense, Change, and Carrying Value

4. The entire amortization table should be calculations, no hard-keyed numbers.

a. Carrying Value should equal the input cell for issue price on January 1, 2018

b. Cash interest should equal the Interest payment per period input cell.

c. Interest expense should be a formula remember to lock the cell reference to interest rate.

d. Change should be a formula calculating the change in the proper direction.

e. The carrying value; the first carrying value amount on 1/1/18 should link to the input cell. From then on the carrying value is a formula.