Forum Discussion
I need a formula to calculate a loan payment with additional principle added to accelerate payoff...
Douglas997t wrote: ``The additional payment made each month is to be applied directly to principle as opposed to as a prepayment applied to both principle & interest.``
Ostensibly, the Excel formula is: =NPER(D66/12, D68 + D69, -D64)
But that results in 200.466253023484, which rounds to 200.47, not 200.48.
And IMHO, NPER should be rounded up because humans cannot count non-integer periods.
So the formula should be: =ROUNDUP(NPER(D66/12, D68 + D69, -D64), 0)
Mathematically, the amount of periodic interest is always prevBal*intRate. It is not affected by the amount of the payment.
So, any additional payment does indeed reduce only principal.
However, as principal is reduced periodically, so is the amount of interest each period.
This is demonstrated below. See the attached Excel file for formulas.
(Updated Excel attachment 7/4/2023 11:40 AM PT.)
ERRATA.... Sorry for the incessant responses, but I just noticed another error in your original Excel file.
Also, I should note that in the "new loan" worksheet, the formulas starting with payment #170 are a hack -- the minimum changes to incorporate the new loan terms in F4:F13. They are not robust enough to allow the starting payment to be anything other than #170.
As for your errors, refer to the image below.
Note that the "scheduled payment" in D295 covers only the remaining principal in B295. It does not include the interest in E295. It should.
And also note that the full "additional principal" payment in G295 is unnecessary.
IMHO, the correct last "scheduled payment" in D295 should cover all of the remaining principal plus interest, even if that might exceed the regular payment in B9.
Thus, there should not be any "additional principal" payment in G295.
That is implemented by changes in the formulas starting in D17, F17 and G17. Note that some of the changes are subtle.
The corrected image is below. See the attached Excel file for details.
Corresponding changes are implemented in the formulas starting in C186, D186, F186 and G186 in the "new loan" worksheet.
