Forum Discussion
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 principle only on a monthly basis to accelerate the payoff term. I have built entire spreadsheets to accomplish this and they work beautifully. In this case I need a one formula in one cell solution to accomplish this as it will need to be available to hundreds, maybe thousands of rows each with its own specific criteria under which this calculation will need to be applied. I have included an attachment of a sample of the more spread out version of the calculation with the cheat of using my HP 17BII+ to calculate the right answer. The additional payment made each month is to be applied directly to principle as opposed to as a prepayment applied to both principle & interest.
I really appreciate your collective assistance with this one!
Douglas
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.
For users of 365, the solution can look very different. For example, the outstanding loan amount would be given by
= LET( month, SEQUENCE(term), payment, monthlyPayment + IF(month>=170, additionalPayment), balance, SCAN(loanAmount, payment, ClosingBalanceλ), loanAmt, VSTACK(loanAmount, balance), reduced, XMATCH(0, balance), TAKE(loanAmt, reduced) )where
ClosingBalanceλ = LAMBDA(priorBalance, pmt, MAX(priorBalance * (1 + fixedRate / 12) - pmt, 0) )The other elements of the result table follow in a straightforward manner as arrays which are combined using HSTACK.
- 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!
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.
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.
- 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!
- 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.
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
Modern Excel can look very different.
Balanceλ = LAMBDA(p, r₀, LAMBDA(bf, f, MAX(bf * (1 + r₀) - p, 0) ) )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.
- I mean you could of just used the arrows to subtract the extra decimal places and it would of rounded it up lol but great job
AlThePal-78 wrote: ``you could of just used the arrows to subtract the extra decimal places and it would of rounded it up``
One of the problems with commenting on a thread that is nearly 2 years old, especially without context, is that memories lapse. Sometimes I cannot remember what I was talking about 2 days ago, much less 2 years. LOL.
I'm not sure what comments about rounding (up?) you are referring to.
But if "[use] the arrows to subtract the extra decimal places" refers to formatting options (whether on the toolbar or not), I have a couple cautionary comments that might be relevant. (Forgive if they are not.)
-----
First, changing the format to reduce the number of decimal places that are displayed results in rounding, not necessarily rounding up, the displayed value.
Sometimes, rounding looks like rounding up because, by coincidence, the digits to the right are 5... or more. For example, formatting 1234.56 to display zero decimal places does appear to round up to 1235. But 1234.45 will appear to round down to 1234.
If you referring to my comments about rounding up NPER and PMT results, my intention is to always round up; for instance, resulting in 1235 even in the latter example.
-----
Second and more importantly, changing the format alters only the appearance, not the actual value. (Unless you set "Precision as displayed", which I usually do not recommend.)
In my examples above, the values remain 1234.56 and 1234.45, even if they appear to be 1235 and 1234 respsectively. Consequently, changing their format has no impact on dependent calculations.
Again, if you are referring to my comments about rounding up NPER and PMT, my intention is to change the actual values, not merely their appearance. IOW, I intend for the change to affect dependent calculations.
- the problem I have with this is that I was trying to figure this out with formulas that could add an additional payment here and there and actually see the difference
AlThePal-78 wrote: ``the problem I have with this is [....]``
Your comment lacks context. I don't know what "this" is.
Also, this thread is nearly 2 years old, and it takes makes many twists and turns that might be irrelevant to your problem.
If you need help with something, I suggest that you start a new thread with a clear explanation of the problem you are having.
Attach an Excel file that demonstrates the problem (or if you cannot, post a URL to a file that you upload to a file-sharing website like box.net/files, dropbox.com, onedrive.live.com, etc. (But not Google Sheets.)
If you wish, refer back to whatever part of this thread that is relevant. But I suspect a new beginning will be sufficient.
JoeUser2004 Hello @Joe User. I hope all is well this 4th of July weekend. You and I had discussed a similar scenario to this one a while back and the dynamic has changed a bit leaving me struggling to find a way to accomplish the enhanced need. I would greatly appreciate your help if you have some time.
I am including an excel document with a short narrative to explain what I am trying to accomplish. Let me know if you have a solution or commentary.
Narrative...
What you see here is the long hand sample of what I need to accomplish in a single cell formula whereby the cells you see formatted in light blue are variable entry cells and unformatted/white cells contain formulas that interact with the colored cells data.
The table is conditionally formatted (Red with black top/bottom outline) to highlight the row that represents the target field to begin the application of additional principal payments from that point forward until the loan is paid off.
The re-amortization of the balance as it is affected by the additional principal payments must happen in this manner as opposed to simply making this a two step loan (as has been suggested) whereby at the point where the additional payments begin we simply begin an entirely new loan. This won't work.
The reasoning: The original amortization schedule in the included example is in month 170 at the time of the initiation of the additional principal payments. The proportion of Principal/Interest must be maintained to give an accurate portrayal of the revised early payoff date. If we began a new loan at this stage, the distribution of Principal/Interest would be skewed due the front end loading of the interest in an amortization schedule and would throw off the revised payoff point to be illustrated.
The solution would need to be a single cell formula based on the criteria reflected in B4:B13 and interfacing relating to today's date as the conversion point for the additional principal payments being added. If this cannot be accomplished without moving to some form of heavier programming, maybe a helper cell would be helpful and this would be ok as a last resort.
Thanks Joe!
(Updated Excel attachment 7/4/2023 11:40 AM PT.)
ERRATA.... Sorry for the incessant responses, but I just noticed another error in your original Excel file.
Also, I should note that in the "new loan" worksheet, the formulas starting with payment #170 are a hack -- the minimum changes to incorporate the new loan terms in F4:F13. They are not robust enough to allow the starting payment to be anything other than #170.
As for your errors, refer to the image below.
Note that the "scheduled payment" in D295 covers only the remaining principal in B295. It does not include the interest in E295. It should.
And also note that the full "additional principal" payment in G295 is unnecessary.
IMHO, the correct last "scheduled payment" in D295 should cover all of the remaining principal plus interest, even if that might exceed the regular payment in B9.
Thus, there should not be any "additional principal" payment in G295.
That is implemented by changes in the formulas starting in D17, F17 and G17. Note that some of the changes are subtle.
The corrected image is below. See the attached Excel file for details.
Corresponding changes are implemented in the formulas starting in C186, D186, F186 and G186 in the "new loan" worksheet.
