MICROSOFT EXCEL / WRITING ASSIGNMENT AMORTIZATION TABLE 30 pt

Requirement 1 Excel 20 pts:

In Excel, create an interactive Amortization Table for a Long-Term Note Payable, similar to the one we learned in Chapter 12 (see below). In your spreadsheet, have a separate area for inputting the parameters (Note Amount, Interest Rate, and Note Length in Years). All cells in the amortization table (excluding the Date column) should contain formulas such that the parameters of Note Amount, Interest Rate and Years can fluctuate. The note will be for 6 years and payments will be annual.

Test your table by setting the parameters to a $24,000 Note, Interest Rate of 5% and Length of 6 years. The note will start on 3/31/2020. Include a total that shows the total amount of interest that will be paid over the course of the loan. The total interest that would be paid on this Note is $4,200.

If your entire table is structured with formulas in each cell, then a user should be able to change the amount of the Note and Interest Rate in your Input section and the table should still correctly calculate the columns with the Ending Balance being at $0 after the 6^th year.

Now, change the parameters to a $72,000 Note with an Interest Rate of 7% (still for 6 years) and leave the table set on these parameters when you turn in the assignment, including your formula to determine total interest paid.

Perform Excel functions:

• Label the tab as Amortization Schedule - required
• Somehow format/highlight the data entry fields for the parameters so that the input cells stand out so a user could easily find where to input the data
• Format the parameter entry fields appropriately (ex.Interest Rate could be formatted as %, etc.)
• The amounts in the table should update automatically when the variables are changed by using appropriate formulas
• Format the table to look clean headings centered, number formats appropriate, etc.

Requirement 2 Writing 10 pts:

On a separate tab in your Excel file labeled Instructions, write instructions for anyone who would be using your table to determine the annual payments they would need to make on the long-term note payable. Explain where/how to enter the variables for the note and what each column in the amortization table represents.

Use appropriate full sentences, grammar, spelling, punctuation, etc. when writing your instructions. Format the instructions so that they are easy to read since the instructions are in Excel, format the cells appropriately (centered, merge and centered, widen the column, etc.).

Exhibit 12-1 Long-term Notes Payable Amortization Schedule 15,000 Beginning Principal Interest Total Ending Balance Payment Expense Payment Balance $ 20,000 $ 20,000 $5,000 $1,200 $ 6,200 15,000 5,000 900 5,900 10,000 10,000 5,000 5,600 5,000 5,000 5,000 5,300 $ 20,000/ 53,000 $ 23,000 12/31/2018 12/31/2019 12/31/2020 12/31/2021 12/31/2022 Total Beginning balance - Principal payment -$20,000 - $5,000 - $15,000 600 300 0 Beginning balance x Interest rate x Time - $20,000 x 0.06 x 1 year - $1,200 Principal payment + Interest expense -$5,000 + $1,200 - $6,200 Exhibit 12-1 Long-term Notes Payable Amortization Schedule 15,000 Beginning Principal Interest Total Ending Balance Payment Expense Payment Balance $ 20,000 $ 20,000 $5,000 $1,200 $ 6,200 15,000 5,000 900 5,900 10,000 10,000 5,000 5,600 5,000 5,000 5,000 5,300 $ 20,000/ 53,000 $ 23,000 12/31/2018 12/31/2019 12/31/2020 12/31/2021 12/31/2022 Total Beginning balance - Principal payment -$20,000 - $5,000 - $15,000 600 300 0 Beginning balance x Interest rate x Time - $20,000 x 0.06 x 1 year - $1,200 Principal payment + Interest expense -$5,000 + $1,200 - $6,200