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.
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").