formula for early loan payoff

[Matt C.]

I have been able to use formulas for mortgage payments and
amortization. But I can't find one that will incorporate
an early payoff on a loan. And I don't know how to
manually calculate either. Example would be I have a loan
at 8% interest for a term of 30 yrs (360 payments) and the
amount is 100,000.00 , it's easy to calulate all the
regular numbers, but if I wanted to pay an extra 100.00
dollars every month on principle to pay off the loan
faster, I can't find a formula that will calculate it. If
you know of a formula or know how to manually get around
this please let me know. Thanks for your time.

 

[Fred Smith]

The function you want is NPER. It will calculate the number of periods given
PV, Rate and Pmt. So your formulas are:

a1 =PMT(8%/12, 30*12, 100000)
a2 = NPER(8%/12, a1-100, 100000)