Use the following template to create an amortization table for a 30-year mortgage. Assume the following:

* Monthly payments and compounding period

* Equal total monthly payments

Make your spreadsheet dynamic, such that interest rate and/or loan amount can be easily changed and everything will adjust.

Use conditional formatting to identify the payment at which the loan is 50% paid off.

Self-check:

1) Set loan amount to $200,000

2) Set APR to 6%

3) Your formatting should indicate that the principal drops below $100,000 in the 252nd month of the loan

4) The amount of interest that accrues in that same month should be $502.87

Loan Amount Annual rate Per period rate Loan term (yr) Loan term (mo) Interest Principal End Balance Month Beg. Balance Tot. Pmt 4 10 12 13 14 15 16 17 18 19 20 21 23 24 25 26 27 28 29 30 31 32 34 35 36 37 38 29