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 01, 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.

     

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Feb 04, 2023

DouglasWilsonCAgmail 

"Essentially, I am developing a spreadsheet that acts more like full functioning coded software but done in a way that I can use somewhat simplistic Excel formulae"

 

Do not think of Excel formulae as, in any way, 'simplistic'.  May sure you only use 365 and the formulas are expressed in terms of LAMBDA/LET combinations to create your 'apps'.  Though an individual formula may become more complicated, the overall complexity of the app reduces and the workbooks can be made far more flexible in the way they adapt to new datasets.

 

DouglasWilsonCAgmail's avatar
DouglasWilsonCAgmail
Copper Contributor
Feb 07, 2023
Hi Peter. I appreciate your comments and will take them into consideration as much as my technical capabilities will allow that is. I run a Private Equity Group focused in acquisition and long term hold of commercial income producing real estate assets with a reasonably strong Excel background building analytics for our group's acquisition model. It kind of comes down to knowing what I know and expanding that base as needed to solve specific needs. I am not at all familiar with LAMBDA or LET and it may not make sense for me to learn these new and I am sure better ways to use Excel given that I don't make money by the additional knowledge if I can get away with existing knowledge even if a bit antiquated by today's more advanced standards.

Either way, I do appreciate both your guidance and exposure to new ways to use my old friend, Excel!

Resources