I want to calculate how much faster I will pay off a mortgage if I make extra payments toward principal every month. There are plenty of amortization sheets floating around that I can use to produce a full amortization table, with the bottom row showing me the date of the last payment. What I need is a formula that will give me only that: just that date, or alternatively, the number of months it will take me to pay off the loan given: rate, principal, term in years, extra amount I will pay monthly, and start date. Help, can't figure this one out! :-) TIA!
1 Answer
Here are some formula to help you:
- yearly rate, principal, original term: input parameters
- original term (in months):
= B4 * 12 - original monthly payback amount:
=PMT(B2/12,B4*12,B3) - extra payment starting from Nth month: input
- balance before first extra payment:
=-FV(B2/12,B8-1,B6,B3) - new total term in months:
=NPER(B2/12,B6+B7,B9)+B8-1
