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.
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.
- Great job Joe. I really appreciate your help on this!
Enjoy your weekend and July 4th!
Douglas I accept that the calculation I presented is a mathematical abstraction of the problem and does not capture practical considerations; no business practice is going to work with millionths of a cent. The calculation produces an array of balance figures (columns H and M) and the other columns form no part of the calculation; they are derived for information only.
The 'simplicity' I set out to achieve is to generate each table from a single formula rather than the original 2240 individual formulas. The method is far closer to the world of professional programmer than it is to that of a normal spreadsheet end-user.
You found the use of Lambda functions off-putting. All a Lambda function does is allow one to write a formula in terms of parameters passed to it as variables. The idea is that such a formula is less prone to errors of consistency than a traditional formula copied across a range.
The end of the Einstein quote you mentioned was "... and no simpler". The challenge to be answered by traditional spreadsheet methods is "does an excess of simplicity itself create impenetrable and error-prone solutions?"
I apologize for the several deleted attempts to express my thoughts about your implementation. I struggled with suggestions that might make it work.
Re: ``The method is far closer to the world of professional programmer``
Speaking as a professional programmer with 40 years experience in compiler and operating system design, I can say with impunity that there is nothing about our two implementations that makes one more "professional" than the other.
On the contrary, as a professional, I pride myself on creating the simplest implementations that are readable, maintainable, efficient and, most importantly, correct.
Your implementation reminds of the challenges in the 1960-70s to write one-line APL expressions that would do the calculation of a multiline function in any other language. They were never considered "professional". In fact, the more obscure they were the better in order to challenge the reader, or so the game went.
- Hey Joe.
I just realized something that will likely change the approach to what I was attempting and I apologize for the detour in advance.
My use for the formula you came up with has one nuance I didn't consider. The scenario is where there is an existing loan in place and we are say 8 yrs 3 mos into the amortization schedule of a 30 yr loan. The calculation I need will illustrate my taking over that loan 8 yr 3 mos into its life and in this illustration, at that point adding additional principle of a a given amount. The exact amount is not important but the method of calculating a way point at any given point in time as we will intervene at a different points in the existing loan cycle every time. If we are taking over financing midstream the allocation of principle and interest will be at a different point than if we began adding principle on day one of a 30 yr loan for instance. I have done this before in a broadly broken out column by column layout but is there a way to accomplish the same thing in the same neat consolidated format as your first solution?
I apologize again for not being clearer but I hadn't thought through what I actually needed from a formula thoroughly enough.
Let me know if you have an idea of how to accomplish this.
Thanks again,
DouglasDouglas997t wrote: ``we are say 8 yrs 3 mos into the amortization schedule of a 30 yr loan``
Not a problem. We simply treat it as a new loan.
See the image and explanation below. See the attached Excel file for formulas.
(Aarrgghh! The lefthand table title has a copy-and-paste error (it should be "without" not "with"), and I am inexplicably unable to replace the image the way I want to. But I was able to update the attached file.)
As you can see, I copied the relevant data for the original loan into C75:D78.
And I added D80, which calculates the number of pmts made already. You might need to modify that formula, since your description ``we are say 8 yrs 3 mos`` is vague.
Then I redefined the data in C64:D71 for the new loan.
I also replaced the formulas in columns F:I to be similar to the more flexible formulas in columns K:N. The only difference is the pmt expression: $D$68 in column G; and $D$68+$D$69 in column L.
(I could have done that the first time. But I wanted to keep the formulas in the lefthand table simple, like what I thought you might be used to.)
The new loan in D64 is the remaining balance of the original loan after the number of payments in D80. Verify that that matches your actual numbers.
If it does not match and you want help to understand why not, I will need actual numbers for D75, D78, D80 and D64. Or you could try the most common remedy: use =ROUND(PMT(...),2) in D68 and D78.
(Errata.... I always use ROUNDUP, not ROUND (typo), for the payment to ensure that the last payment is less. It's a legal disclosure issue. You might try it both ways, just to see if you can get a match.)
Also note that the "min pmt" in D68 and D78 should the same, or nearly so, if the interest rates in D66 and D76 are the same.
In my example, they differ by 2.73E-12, an infinitesimal and usually insignificant difference due to binary arithmetic anomalies.
Modern Excel can look very different.
Balanceλ = LAMBDA(p, r₀, LAMBDA(bf, f, MAX(bf * (1 + r₀) - p, 0) ) )- 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
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!
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.
