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.
My background from 1990-2018 was as a Real Estate and Mortgage Broker based in L.A. I actually owned a Mortgage Company from 2002-2012 and have a strong background working with Lotus 123 (1986-1990) then Excel since its inception. I used it to create business tools that weren't available in the software marketplace with specific calculations and reports I used with a generally upscale clientele that wanted more in depth calculations run. I moved into the investment side of the business in 2018 and had the same trouble finding software capable of running the math my appetite for more advanced analysis was once again a problem. My duties are as a Asset & Portfolio manager for a small Private Equity Group focused in acquisition and long term hold of commercial income producing real estate. In an almost short story, I needed to calculate a specific mortgage-based calculation that just wasn't working within the context I needed so I came here to flush it out. Essentially, I am developing a spreadsheet that acts more like full functioning coded software but done in a way that I can use somewhat simplistic Excel formulae.
Hope that answers your curiosity...
"Essentially, I am developing a spreadsheet that acts more like full functioning coded software but done in a way that I can use somewhat simplistic Excel formulae"
Do not think of Excel formulae as, in any way, 'simplistic'. May sure you only use 365 and the formulas are expressed in terms of LAMBDA/LET combinations to create your 'apps'. Though an individual formula may become more complicated, the overall complexity of the app reduces and the workbooks can be made far more flexible in the way they adapt to new datasets.
- Hi Peter. I appreciate your comments and will take them into consideration as much as my technical capabilities will allow that is. I run a Private Equity Group focused in acquisition and long term hold of commercial income producing real estate assets with a reasonably strong Excel background building analytics for our group's acquisition model. It kind of comes down to knowing what I know and expanding that base as needed to solve specific needs. I am not at all familiar with LAMBDA or LET and it may not make sense for me to learn these new and I am sure better ways to use Excel given that I don't make money by the additional knowledge if I can get away with existing knowledge even if a bit antiquated by today's more advanced standards.
Either way, I do appreciate both your guidance and exposure to new ways to use my old friend, Excel!