Time to Pay Off Mortgage Formula

**antanas** · 02-04-2011, 06:12 PM

Is there a single line formula that calculates the time to pay off a mortgage based on extra principal payments (lump sum or periodic, depending on the payment frequency) or accelerated payments? I can calculate this using an amortization table in Excel but need to do this calculation without the table. Thanks in advance.

**MarvinP** · 02-05-2011, 11:24 AM

Hi antanas,
Here are the fundamental ones http://www.excel-examples.com/micros...-functions.htm
And the full list is on the 3 pages starting with http://office.microsoft.com/en-us/ex...010064537.aspx

The real quesion is there a combination of the above that will calculate the Time based on your given specific numbers (inclucing the balloon).

How about giving us some real numbers and what exactly is given.

**antanas** · 02-07-2011, 03:44 PM

Originally Posted by MarvinP

Hi antanas,
Here are the fundamental ones http://www.excel-examples.com/micros...-functions.htm
And the full list is on the 3 pages starting with http://office.microsoft.com/en-us/ex...010064537.aspx

The real quesion is there a combination of the above that will calculate the Time based on your given specific numbers (inclucing the balloon).

How about giving us some real numbers and what exactly is given.

Here is an example:

Inputs:
Mortgage loan amount: $100,000
Interest rate: 7.00% (compounded semi-annually for a Canadian mortgage)
Payment frequency: Monthly
Amortization: 25 years
Additional monthly payment: $100 per month
Additional annual payment: $1,000 per year

Results (using a spreadsheet with an amortization table)
Monthly payment: $700.42 (does not include the extra principal payments)
Total payments: $162,941.47
Total interest: $62,941.47
Number of payments or time to pay off mortgage: 185 payments (15.4 years).

I can use the NPER function to calculate the number of payments if I make an additional payment per month but I don't know how to calculate this if I also make an additional principal payment once per year.

Thanks,
Tony

**MarvinP** · 02-07-2011, 04:49 PM

Hi Tony,

I just called my brokerage banker, who is working on his CPA and gave him the above problem. I ended with "and I want it to be a single formula in a single cell". After a few concerns that you are wanting too much we decided the best advice is below.

We both decided the problem is hard enough without the last requirement (in a single cell). You should build a step by step table where you can follow the monthly overpays and interest rate increases (that are different timings - making the problem much harder) to a final answer using the numbers above. Then, if possible, build cell references into the spreadsheet/table that allow you to change the givens.

I believe you are looking at a set of formulas and or the Solve Function to calculate the above.