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.

     

     

RogerTrock's avatar
RogerTrock
Copper Contributor
Aug 10, 2023

Kudos to you for diving into the intricate world of mortgage payment calculations and extra principal payments. It's like financial wizardry, right? Your dedication to finding a streamlined solution is pretty inspiring.

Now, I get that having a one-formula solution would make life a whole lot easier, especially when dealing with heaps of rows. Speaking of navigating complex financial situations, have you ever considered connecting with a Mortgage Broker in Bridgend? They're like the pros who can help you make sense of all this and provide expert advice.

Douglas997t's avatar
Douglas997t
Copper Contributor
Aug 10, 2023
I appreciate your kind words Roger. The solution I was shooting for would help to shortcut a number of steps and columns in an already cumbersome spreadsheet we use for commercial real estate analytics. I have created the long hand version of the net result I ultimately got to with the help of the knowledgeable and very helpful participants within this arena! Ultimately, the solution I received WAS inspired to say the least!

Thanks again for your kind words!

Resources