 Paying Off Your Mortgage: How to​ Run the​ Numbers
If you​ have Microsoft Excel running on​ your computer at​ home or​ work,​ you​ can use Excel’s NPER function to​ calculate how quickly you​ can pay off a​ loan such as​ 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 type-of-annuity switch is​ a​ little complicated,​ but here's how it​ works .​
If you​ set the​ type-of-annuity switch to​ 1,​ Excel assumes payments occur at​ the​ beginning of​ the​ period,​ following the​ annuity due convention .​
If you​ set the​ annuity switch to​ 0 or​ you​ omit the​ argument,​ Excel assumes payments occur at​ the​ end of​ the​ period following the​ ordinary annuity convention.
But let me show you​ how the​ function works in​ theory and in​ practice .​
All of​ this will become quite clear,​ I'm sure.
The function uses the​ following syntax:
=NPER(rate,​pmt,​pv,​fv,​type)
For example,​ to​ calculate the​ number of​ \$1,​000 monthly payments required to​ pay off a​ 9% mortgage that still has a​ \$100,​000 mortgage balance,​ you​ enter the​ following formula into an​ Excel worksheet cell:
=NPER(.09/12,​-1000,​100000,​0,​0)
The function returns the​ value 185.53,​ representing roughly 185 payments and then another roughly half payment .​
Notice that to​ convert the​ 9% annual interest to​ a​ period interest,​ the​ formula divides the​ annual interest rate by 12 .​
Notice,​ too,​ that the​ payment amount,​ as​ a​ cash outflow,​ shows as​ a​ negative value while the​ loan balance,​ as​ an​ implicit cash inflow,​ shows as​ a​ positive value.
One final note: the​ NPER function rarely returns an​ integer,​ or​ whole-number result .​
As in​ the​ preceding example,​ it​ commonly returns a​ fractional value,​ indicating that after the​ last regular payment,​ an​ additional fractional payment will also need to​ be made.