Microsoft Office Tutorials and References
In Depth Information
To Enter the Formulas in the Amortization Schedule
series to cell G20
The next step is to enter the four formulas that form the basis of the amortization schedule in row 3. Later,
these formulas will be copied through row 20. The formulas are summarized in Table 4–2.
Table 4–2 Formulas for the Amortization Schedule
Result of Pressing Tab Key
The beginning balance (the balance at the end of a
year) is the initial loan amount in cell C6.
=IF(G3 <= $E$3, PV($E$2 /12, 12 *
($E$3 – G3), –$E$4), 0)
The ending balance (the balance at the end of a year)
is equal to the present value of the payments paid over
the remaining life of the loan.
Paid On Principal
=H3 – I3
The amount paid on the principal at the end of the
year is equal to the beginning balance (cell H3) less the
ending balance (cell I3).
=IF(H3 > 0, 12 * $E$4 – J3, 0)
The interest paid during the year is equal to 12 times
the monthly payment (cell E4) less the amount paid on
the principal (cell J3).