Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

I need a formula to calculate a loan payment with additional principle added to accelerate payoff...

Brass Contributor

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 principle only on a monthly basis to accelerate the payoff term. I have built entire spreadsheets to accomplish this and they work beautifully. In this case I need a one formula in one cell solution to accomplish this as it will need to be available to hundreds, maybe thousands of rows each with its own specific criteria under which this calculation will need to be applied. I have included an attachment of a sample of the more spread out version of the calculation with the cheat of using my HP 17BII+ to calculate the right answer. The additional payment made each month is to be applied directly to principle as opposed to as a prepayment applied to both principle & interest.

 

I really appreciate your collective assistance with this one!

 

Douglas

29 Replies
best response confirmed by Douglas997t (Brass Contributor)
Solution

@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.

 

JoeUser_0-1656721834364.png

 

@Douglas997t 

Modern Excel can look very different.

image.png

Balanceλ 
= LAMBDA(p, r₀, 
    LAMBDA(bf, f, MAX(bf * (1 + r₀) - p, 0)
    )
  )
Great job Joe. I really appreciate your help on this!

Enjoy your weekend and July 4th!

Douglas

@Joe User 

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?"

@Peter Bartholomew 

 

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.

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

@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.

 

JoeUser_3-1656829913286.png

 

 

(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.

 

@Joe User 

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.

@halleyhalligan  wrote: ``Ok, now I see I might get a specialist to help me with that.``

 

Who are you referring?  And what exactly do you need help with:  the loan problem, or a LAMBDA solution per se?

 

-----

@halleyhalligan  wrote:  ``I tried to calculate how much money I would have to return to the bank if I took a five-year loan with an APR of 5-6%. ``

 

That is not sufficient information for anyone to offer a solution.

 

But as I commented in another late "reply" that you posted in another discussion (click here), it  would be better for you to start a new discussion.

 

When you do, we will need to know:

 

1. The principal amount of the loan.

 

2. The actual annual interest rate.  "5 to 6%" is not specific enough.  Also note:  APR is not necessarily the same as the annual interest rate.  (I know:  the terminology is confusing.  Sigh.)

 

3. The payment frequency.  Typically, it is monthly.  But there are a lot of other ways to structure a loan.

 

4. How to convert an annual interest rate to a periodic rate (i.e. rate per payment period).  If you are unsure, at least tell us the lender's country.

 

But in very general terms, the following works in most countries for loans with monthly payments:

 

=PMT(monthlyRate, numberOfMonths, -loanAmount)

 

The reasons for development of this formula are not entirely relevant but I will try to give you the reader's digest version.

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

@DouglasWilsonCAgmail 

"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!
First off, kudos to you for being a financial boss and wanting to pay off your mortgage faster. That's some serious adulting right there. Now, as for the formula, have you considered using Excel? You mentioned building entire spreadsheets to accomplish this, so I'm assuming you're familiar with the program. With Excel, you can create a formula that calculates the loan payment with the additional principle included.

Thanks for your reply @Suegamma!

 

I love Excel and its forefather Lotus 123 which I first used in 1986 before MS evolved from DOS to windows in the late 80s. The formula I asked for help on was but one small component of a rather large spread sheet that acts as the backbone to very detailed analytics I use to run complex commercial real estate calculations.

 

I run a Private Equity Group focused in acquisition and long term hold of commercial income producing real estate assets and use Excel more of an app than a simple singular calculation. I have set up a sheet that calculates acquisition math through post acquisition asset stabilization to conversion of acquisition financing to long term financing including the potential of using a multi-tier capital stack for some or all of the aforementioned stages.

 

Love Excel!!!

 

Thanks again for your response!

 

Douglas

Thanks for your response @soksophara88!

You are certainly right about the breadth and depth of a spreadsheet can take on a life of its own. Fortunately for our purposes the calculations we run are used time and time again each day as we assess the viability of various commercial real estate investments.

My background is as both a real estate and mortgage broker then based in the Calabasas portion of L.A. since 1990. I transitioned into the investment end in mid 2018 and now run a Private Equity Group focused in acquisition and long term hold of commercial income producing real estate assets and use Excel more of an app than a simple singular calculation. I have set up a sheet that calculates acquisition math through post acquisition asset stabilization to conversion of acquisition financing to long term financing including the potential of using a multi-tier capital stack for some or all of the aforementioned stages.

We obtain financing through many sources including mortgage brokers but the types of financing are usually outside the normal financing most brokers are acquainted with. However, we can always discuss our needs with you and see if you have something that fits our model. Feel free to provide your outside email address and I will reach out to schedule a call.

Love Excel!!!

Thanks again for your response!

Douglas

@soksophara88 


@soksophara88 wrote:
While building spreadsheets can be useful for calculations, replicating hundreds or thousands of rows can be time-consuming and challenging.

When I read statements like this it reinforces my opinion that moving to dynamic array versions of Excel is a must.  If the goal is to create a reusable 'app', the last thing one should be doing is manipulating individual numbers and the formula that generates each one.

 

Typically, any output table should be generated by a single function spilling from the top left cell.  The steering data it requires to make it specific to a particular case should be referenced explicitly within the string of arguments.  If the function calls other more detailed steps, the functions implementing those should be thoroughly tested in isolation.  That is far easier once one has moved away from the practice of the relative referencing of individual cells.

 

The downside is that it involved learning new tricks and the temptation is always to revert to the tried and tested.

Hello and thank you for your reply. I have been using Excel since its inception in the early 90s and Lotus 123 prior to MS and Windows 3.1 showing up on the scene. Definitely familiar with Excel and coming from 30+ years as a Real Estate & Mortgage broker in L.A., have devised many creative uses for its capabilities that I just plain couldn't find in a single software package that was capable. When I couldn't find what I needed I jumped in and created it in Excel.

I am the Senior Asset Manager for a small private equity group focused in acquisition and long term hold of a variety of commercial property types. Our need for advanced calculations and a lot of "If/then, If/and and If/or" help to make the functionality of one cell solve many more complicated calculations as other cell's values change around it.

In this case the amortization formula needs were far more complex than I illustrated in this post. I had been having a tough time with a rather lengthy series of nested formula within nested formula to end up with a multi-functional cell or combination of cells as part of a larger, for lack of a better term, application style solution. My skill set in Excel is certainly not current with its current capabilities but generally my older knowledge still works in the end. When it doesn't or I get completely hung up for a solution...I post in here! I have had great success in working through solutions to issues I just couldn't get my head around and very much appreciate the help others have given me these past few years.

Thanks again for your reply and assistance.

Douglas

@Joe User 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!

1 best response

Accepted Solutions
best response confirmed by Douglas997t (Brass Contributor)
Solution

@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.

 

JoeUser_0-1656721834364.png

 

View solution in original post