Microsoft Office Tutorials and References

In Depth Information

**Calculating the Interest and Principal Components**

Early loan payoff

During times of declining interest rates, a homeowner might refinance his home mortgage. You

can use NPER to calculate how many fewer payments you would have to make due to refinancing.

This example assumes a $200,000 mortgage at 7.5%, with monthly payments of $1,611.19 for the

next 20 years. If you refinance to 5.75% but keep making the same payment, this formula

computes how many years you can shave off of the loan (see Figure 11-13):

=(20*12)–NPER(5.75%/12,PMT(7.5%/12,20*12,200000,0),200000,0,0)

The pmt argument is a PMT function that computes the $1,611.19 that you’re paying based on the

terms of your existing mortgage. Subtracting the result from 240 (20 years of 12 months) shows

that you can reduce your mortgage term by 51 months by refinancing under these terms.

Figure 11-13:
Calculating the effect of an early loan payoff.

Although NPER can produce fraction results (for example, 4.26 months), you probably

would not make a payment 26% of the way through a month. Instead, you would make

a payment on the fifth month for an amount that’s less than the payments you made

previously.

Calculating the Interest and Principal Components

This section discusses four Excel functions that enable you to

h
Calculate the interest or principal components of a particular payment.

h
Calculate cumulative interest or principal components between any two time periods.

The examples in this section are available on the companion CD-ROM in a file named

payment components.xlsx
.