Forum Discussion

Douglas997t's avatar
Douglas997t
Copper 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.

     

     

Resources