Microsoft Office Tutorials and References
In Depth Information
Calculating the Balance is simple. For the first month only, subtract the Principal
payment from the original loan. For all the other months, subtract the monthly
Principal payment from the previous month
s Balance.
The calculations and the results are shown in Figure 32.11. For Month 1,
subtract in cell I2 the principal of month 1 from the original loan of cell B3, named
Loan. For month 2, in cell I3 subtract the principal of month 2 from the balance of
month 1, located in cell I2 (
G3). The rest of the calculations for the Balance
are the same. Just double-click the drag handle at the lower right corner of cell I3 and
the formula will be carried down for all the cells.
The amortization table reflects what was said before. The Principal repayment
portion is low in the beginning and the Interest portion is high. The Balance
diminishes over time.
Two more Excel functions are part of the loan amortization functions family:
CUMIPMT and CUMPRINC. The CUMIPMT function calculates the cumulative
interest between two specific periods, or
over a given length of
time. CUMPRINC calculates the cumulative principal between two specific periods.
The former is important when a person can deduct the interest paid for income tax
in other words
FIGURE 32.11 Loan Balances
Search JabSto ::

Custom Search