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.

     

     

Douglas997t's avatar
Douglas997t
Brass Contributor
Aug 10, 2023
I am a 365 user and this 2nd solution does look interesting and probably superior. But for my purpose the other solution was a bit less complicated, given that I do have some above average Excel knowledge but my skills are nowhere close to that level of complexity. Your alternative is nonetheless very much appreciated Peter!
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 11, 2023

Douglas997t 

I can well understand your hesitancy in committing to the new shiny toolset!  A year on and I am still refining my programming technique to take advantage of the (huge) benefits of Lambda and mitigate the remaining shortcomings.  But then, most users have developed their traditional spreadsheet techniques over a considerably greater period of time, and that supported by a myriad of posted 'tips-and-tricks'!

 

What I have done in the attached is move from the single measure of Loan Amount to creating the full amortisation schedule as a table [the formula being in cell B17].  Since you have 365, you may like to adjust some of your blue-shaded assumption cells to examine the manner in which the model responds.  I wouldn't trouble yourself with the 'how' of what it does, that will wait until you have the time to indulge any curiosity.

 

  • Douglas997t's avatar
    Douglas997t
    Brass Contributor
    Aug 11, 2023
    Hey Peter. Your explanation really helps. You mentioned how some people have developed their spreadsheet techniques over many years and I would include myself in that group, at least to some degree. I began working with spreadsheets before Windows and Office had come on the scene. I started in Lotus 123 in 1986 and when DOS gave way to Win 3.1 and the 1st iteration of Excel came out, I moved to Excel and have used it every day since. My uses are somewhat rudimentary by comparison to what it is capable of and since I don't make a living from creating excel solutions for clients don't really want to go to deep within its new functionality. On the other hand, I do use it it within our practice when assessing or managing commercial real estate and love what I have been able to create using my rather old school techniques. And most importantly, I love Excel...in all of its forms! What a useful tool MS created here!

    I really appreciate your help and guidance Peter. Have a great weekend!

    Douglas

Resources