Forum Discussion

Douglas997t's avatar
Douglas997t
Brass Contributor
Jul 01, 2022

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.

     

     

Suegamma's avatar
Suegamma
Copper Contributor
May 02, 2023
First off, kudos to you for being a financial boss and wanting to pay off your mortgage faster. That's some serious adulting right there. Now, as for the formula, have you considered using Excel? You mentioned building entire spreadsheets to accomplish this, so I'm assuming you're familiar with the program. With Excel, you can create a formula that calculates the loan payment with the additional principle included.
  • DouglasWilsonCAgmail's avatar
    DouglasWilsonCAgmail
    Copper Contributor
    May 03, 2023

    Thanks for your reply Suegamma!

     

    I love Excel and its forefather Lotus 123 which I first used in 1986 before MS evolved from DOS to windows in the late 80s. The formula I asked for help on was but one small component of a rather large spread sheet that acts as the backbone to very detailed analytics I use to run complex commercial real estate calculations.

     

    I run a Private Equity Group focused in acquisition and long term hold of commercial income producing real estate assets and use Excel more of an app than a simple singular calculation. I have set up a sheet that calculates acquisition math through post acquisition asset stabilization to conversion of acquisition financing to long term financing including the potential of using a multi-tier capital stack for some or all of the aforementioned stages.

     

    Love Excel!!!

     

    Thanks again for your response!

     

    Douglas

Resources