Microsoft Office Tutorials and References
In Depth Information
To Enter the Formulas in the Amortization Schedule
Excel extends
series to cell G20
Auto Fill
Options
button
Figure 4–35
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
Tab Stop
Tab Marker
Result of Pressing Tab Key
Example
H3
Beginning Balance
=C6
The beginning balance (the balance at the end of a
year) is the initial loan amount in cell C6.
I3
Ending Balance
=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.
J3
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).
K3
Interest Paid
=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).

Search JabSto ::

Custom Search