9. Assume the Beginning Balance is column B,
Monthly Payment is column C, Towards Interest
is column D, Towards Principal is column E, and
Ending Balance is column F and the row number
is one more than the payment number. The first
entry for beginning balance is the original
principal amount. The formula for the remaining
Beginning
Balance Monthly
Payment Towards
Interest Towards
Principal Ending
Balance
5 195,337.39 2,322.17 1,139.47 1,182.70 194,154.69
6 194,154.69 2,322.17 1,132.57 1,189.60 192,965.09
7 192,965.09 2,322.17 1,125.63 1,196.54 191,768.55
8 191,768.55 2,322.17 1,118.65 1,203.52 190,565.03
10. In order to calculate the last year of the
amortization table, you will need to complete the
entire amortization table using formulas.
Assume the Beginning Balance is column B,
Monthly Payment is column C, Towards Interest
is column D, Towards Principal is column E, and
Ending Balance is column F and the row number
determine interest is, =B2*7/1200. The formula
169 57,114.22 4,902.50 261.77 4,640.73 52,473.49
170 52,473.49 4,902.50 240.50 4,662.00 47,811.49
171 47,811.49 4,902.50 219.14 4,683.36 43,128.13
172 43,128.13 4,902.50 197.67 4,704.83 38,423.30