Microsoft Office Tutorials and References

In Depth Information

**CUMIPMT and CUMPRINC**

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.

ΒΌ

I2

CUMIPMT and CUMPRINC

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

purposes.

β

in other words

β

FIGURE 32.11
Loan Balances