If you have Microsoft Excel on your computer at home or at work, you can use the NPER function to figure out how quickly you can pay off a loan like a mortgage.
The NPER function calculates the term, or number of regular payments, on a loan given its interest rate, payment amount, present loan balance, balloon payment (if any), and, optionally, the type-of-annuity switch.
The switch between different types of annuities is a bit complicated, but here's how it works. If you set the type-of-annuity switch to 1, Excel assumes that payments happen at the start of the period, which is when annuities are due. If you set the annuity switch to 0 or don't include the argument, Excel assumes that payments happen at the end of the period, as is typical for annuities.
But let me show you how the function is supposed to work and how it actually does work. I'm sure all of this will become clear.
This is the syntax for the function:
=NPER(rate,pmt,pv,fv,type)
For example, to figure out how many $1,000 monthly payments are needed to pay off a $100,000 mortgage with a 9 percent interest rate, you can type the following formula into a cell on an Excel worksheet:
=NPER(.09/12,-1000,100000,0,0)
The function gives back the number 185.53, which is about 185 payments followed by another half payment. Notice that the formula divides the annual interest rate by 12 in order to turn the 9 percent annual interest rate into a period interest rate. Also, notice that the payment amount, which is an outflow of cash, shows up as a negative value and the loan balance, which is an implicit inflow of cash, shows up as a positive value.
One last thing to keep in mind is that the NPER function rarely returns a whole number. As in the previous example, it often gives back a fractional value, which means that after the last regular payment, a fractional payment must also be made.
