Forum Discussion
Solved
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...
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 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.