Forum Discussion
Douglas997t
Jul 01, 2022Brass Contributor
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 pri...
- Jul 02, 2022
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
Jul 02, 2022Bronze Contributor
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
Jul 03, 2022Brass Contributor
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,
Douglas
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,
Douglas
- JoeUser2004Jul 03, 2022Bronze Contributor
Douglas997t 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.