Excel mortgage calculator with extra payments: How to get payoff date without full amortization table

microsoft excelworksheet-function

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, I can't figure this one out!

Best 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

enter image description here

Related Question