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.
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.
This workbook shows the base payment rounded up and the interest charged rounded down to the nearest dollar. The final period adjustments are pretty large but I assume that is normal.
