Forum Discussion

Douglas997t's avatar
Douglas997t
Brass Contributor
Jul 01, 2022
Solved

I need a formula to calculate a loan payment with additional principle added to accelerate payoff...

Hello All!   This relates to a Mortgage Payment scenario. Calculation of the payment ( PMT(Int/12,Term,-Bal.) ) then illustrating the effects of an additional dollar amount being applied to the pri...
Formulas and Functions
  • JoeUser2004's avatar
    JoeUser2004
    Jul 02, 2022

    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.

     

     

AlThePal-78's avatar
AlThePal-78
Copper Contributor
Apr 15, 2024
the problem I have with this is that I was trying to figure this out with formulas that could add an additional payment here and there and actually see the difference
JoeUser2004's avatar
JoeUser2004
Bronze Contributor
Apr 15, 2024

AlThePal-78  wrote:  ``the problem I have with this is [....]``

 

Your comment lacks context.  I don't know what "this" is.

 

Also, this thread is nearly 2 years old, and it takes makes many twists and turns that might be irrelevant to your problem.

 

If you need help with something, I suggest that you start a new thread with a clear explanation of the problem you are having.

 

Attach an Excel file that demonstrates the problem (or if you cannot, post a URL to a file that you upload to a file-sharing website like box.net/files, dropbox.com, onedrive.live.com, etc.  (But not Google Sheets.)

 

If you wish, refer back to whatever part of this thread that is relevant.  But I suspect a new beginning will be sufficient.

Resources