Forum Discussion
I need a formula to calculate a loan payment with additional principle added to accelerate payoff...
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.
No need to apologise, I am perfectly happy to take positive suggestions or even criticism on board. I freely admit that I had not taken rounding into account, both because the rules to be implemented had not been specified and because I didn't want to add complexity to what is already a somewhat alien approach to spreadsheet usage.
Your background as a compiler writer did come as a little bit of a surprise, my background was scientific programming using Fortran IV, so not as fundamental in IT terms. My point in describing the approach as 'closer to the world of professional programmer' was not as a comment of professionalism but a recognition of the task as a programming exercise rather than merely the manipulation of numbers that tends to characterise normal spreadsheet use.
The formulas I had in mind were those that underpin the PMT function that, itself, generates an unrounded value. Rounding it up generates an over-payment which accumulates but I could adjust the calculation within the final payment period to compensate. I think that will happen automatically but I will check.
Something I aim to achieve, is to create a formula that can be modified to give results for variable interest rates or periods of grace etc, without touching the spreadsheet itself, other than creating a lookup table to show the changes against the period for which they scheduled. I certainly do not prioritise 'concise'; in general I am for 'readability' and avoid direct cell referencing despite it being the industry standard for spreadsheets.
- 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.
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!
Douglas997t wrote: ``The solution would need to be a single cell``
See ERRATA in subsequent two responses.
I'm confused. Can you tell me exactly the cell references of the multiple formulas that you want to combine, and exactly the cell reference where the single formula would go?Frankly, I like what you did in B10:B13 as-is. I do not see anything that must be changed.
-----
Douglas997t wrote: ``additional principal payments must happen in this manner as opposed to simply making this a two step loan [....] 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 ``
That is not right.Or I don't understand what you mean by "a two-step loan".The reason not redo the terms of the loan after payment #169 is contractual and practical, but not mathematical.
I will explain what mean by "contractual and practical" below. But mathematically....
Refer to the attached Excel file, which compares your original amortization schedule (worksheet '06 - Amort Sched - Addt'l Princ') with new loan starting with payment #170 (worksheet '06 - Amort Sched - New Loan').
Note: Freeze Pane is set after A17 in both worksheets. Sometimes, that can be confusing. If it is, click View > Freeze Panes > Unfreeze Panes.
Original Loan:
New Loan:
The formulas in column I, J and K demonstrate that the line-by-line interest, principal and balance are essentially the same (or relatively infinitesimally difference due to binary arithmetic anomalies).
LMK if you want me to explain any of the formulas.
-----
Re: ``The reason not redo the terms of the loan after payment #169 is contractual and practical``
If you simply want to present the schedule of additional payments in the form of the "New Loan" worksheet, the above demonstrates that there is no harm. Again, the mathematics are the same.
However, I agree with keeping the original loan "for real".
And to that end, I agree with presenting the additional payments as you do, even if they are consistent and equal starting with payment #170 to the (new) end.
If you restructure the loan "for real", the borrower is contractually obligated to make equal and consistent "additional payments", which are folded into the new regular payment amount.
Conversely, by retaining the original loan, the borrower has the option of not making additional payments later, or making different additional payments later.
Moreover, as a practical matter, if you restructure the loan "for real", that is called "refinancing the loan". Usually, the incurs "refi" loan fees that are similar to a completely new loan.
-----
Finally, a minor improvemnt....
In B5, you essentially have the expression SUM(B8)-(B8-DATEDIF(B4,TODAY(),"M")).
Algebraically, that can be reduced to just DATEDIF(B4,TODAY(),"M").
ERRATA.... I think I see what you mean by a "two-step loan" and changing the proportions of principal and interest per payment, as well as the issue with multiple formulas vs a single formula.
And to that end, you can consider my previous response as a "solution" that avoids all that.
In my previous response, I arbitrarily set the "new loan" payment to be the original payment plus the additional payment.
There is nothing wrong with that. The payment can be any arbitrary that the borrower wants.
But in contrast, I suspect that you calculated the "new loan" payment by something like the following, based on the design of my "new loan" worksheet:
=ROUNDUP(PMT(F7/12,F8,-F6), 2)
where the formula in F8 is =B12-B5.
That results in a regular payment of $18,299.04, with a final payment of $18,206.92.
But the value in B12 is based on an NPER calculation that assumes a regular payment that is the same as the "arbitrary" payment that I used.
(Note the inconsistent assumptions!)
Presumably, that is the "multiple formulas" that you want to avoid.
In contrast, with the arbitrary regular payment of $18,361.71, which naturally follows from the input date in column B, namely =ROUND(B9+B11,2), the final payment is very different, namely $9154.01.
There is nothing wrong with that. It is a consequence of the borrower's choices.
(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.
- Hi Joe. Thanks for your guidance on this. I see what you are saying and will implement your changes! Your help is very much appreciated!
- 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.
- 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.
