What formula will automatically subtract the loan payment amount every month including the interest on my spreadsheets?

I'm looking to get a formula that will automatically subtract the loan payment amount every month including the interest on my spreadsheets.  This will help me keeping track on my loan payments we have for the company. Also, it would be nice if there was an option to make a lump sum payment to the loan and it would automatically calculate the change.
david HoferAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlanConsultantCommented:
Hi,

I would lay out the calculation in columns, being:

Opening Balance For Period, Interest For Period, Payments In Period, Closing Balance For Period

The payments might usually all be the same, but if you make a one off, you just add it in for that period.

The interest would calculate based on the length of the period, the rate, and any other factors specific to your loan agreement.


Hope that helps,

Alan.
0
david HoferAuthor Commented:
Thanks for the reply Alan.
Sorry I'm not that familiar to setting up these loan payment formulas.  Here is the copy of the formula I'm using now.  It works great except it doesn't account for interest and I'm not sure how to set it up if I want to make extra payments to the loan  
A1: total
A2: payment
A3: first payment date
A4: balance =A1-A2*(12*(YEAR(TODAY())-YEAR(A3)) + MONTH(TODAY())-MONTH(A3)-(DAY(A3)>1)+(DAY(TODAY())>1))
A5: remaining months =CEILING(A4/A2,1)

Can you please show me how you would set it up to include the suggestions you made earlier.

Thanks in Advance.

David
0
AlanConsultantCommented:
Hi,

See attached template.

The figures in yellow are the variables.

If you want to add an additional payment, add or change the amount in the appropriate period.  This example is currently setup to pay off over the period shown.  If you add an additional payment, that will change the total loan duration (all other things being equal) of course.

Alan.
EE-29092644-LoanExample-Version1.xlsx
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

david HoferAuthor Commented:
ok thank you very much
0
AlanConsultantCommented:
No problem - happy to help.

Don't forget to close the question.

Alan.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony MellorChartered AccountantCommented:
I hate to interfere, but I'm not convinced that answers the question.
The first example.xls period is interest free (unlikely),and we don't know the company loan compounding periods.
We need details of a loan (dates, rates, compounding and is it fixed or variable so we can replicate it otherwise it's guesswork.

Anthony

p.s. that's a neat trick with min().
0
david HoferAuthor Commented:
Hi Anthony.

The formula seemed to do the trick, however in the future I would like to set up a  spread sheet that is even simpler to use. I'll give you an example how i would like to set it up. Here is an example of one of our vehicle loans.

Loan amount.              $36218.86
First payment date.    April 1, 2108
Interest rate.                5.90%
Amorization(years).     7
Monthly payment.       $527.36        
Lump sum payment
Amount remaining.    


Would it be possible  to set it up this way so all I have to do is plug in the first 5 pieces of info on a loan amount.  Our loans are all Fixed rates.  I don't need to see the actual amorization table as this takes up to much room in the spread sheets.  Also, I would like the program to automatically make the calculation on the given loan payment dates.
This is the formula I found online but it doesn't calculate for interest and there is no option to put lump-sum payments toward the principle.
A1: total
A2: payment
A3: first payment date
A4: balance =A1-A2*(12*(YEAR(TODAY())-YEAR(A3)) + MONTH(TODAY())-MONTH(A3)-(DAY(A3)>1)+(DAY(TODAY())>1))

And i actually tried to it. I went into my computer time settings and manually set the date ahead by a few months. When i opened the program it automatically adjusted the remaining balance by the correct amount.

I hope i'm not being to complicated.
Thank you very much.
David
0
Anthony MellorChartered AccountantCommented:
Hi David, Did you mean to mark my comment as the "accepted answer"? As answers go Alan's was a working solution. I was just quibbling about details.- Anthony
0
david HoferAuthor Commented:
Sorry,  no i just replied in the box below your question.  I wasn't aware that it would mark the comment. I guess ill have to get more familiar with this program.
David
0
Anthony MellorChartered AccountantCommented:
really we are re-inventing the wheel here. Google "Loan Amortisation" to see a vast array of possibilities, such as here:
https://www.thebalance.com/excel-loan-calculator-315509

In real life I don't use spreadsheets at all to do what you are doing, which is essentially a single cell calculation, I use a Hewlett Packard HP12C (many years ago the HP38C) Financial Calculator, about $50 bucks.. These are a carefully guarded secret weapon of accountants and financial people generally.  https://www.amazon.com/HP-HP12C-12C-Financial-Calculator/dp/B00000JBLH
It comes with a booklet (or used to) packed with every formula you will ever need, and which are built in.You can have hours of happy fun trying to get excel to arrive at the same (correct!) answers.

What I do use spreadsheets for is the amortisation tables (like Alan's), because I can see for myself what is happening.

I know this isn't the type of answer you asked for, but I commend you to it as a hardware solution instead of a software solution - that fits in your pocket with batteries that last for ever -  or at least a long time.

Anthony

p.s. come to think of it there are now some phone "apps" that replicate the 12C. I use both because the real calculator is faster to use as the real thing. Note it uses RPN, which is also faster.


I have asked for moderator assistance for you regarding the "solution" confusion. No worries, you will get the hang of it soon enough :-)
0
david HoferAuthor Commented:
Thanks Anthony.
All in all. Do you think it's possible though to rework this formula to include interest. And include an option to put lump-sum payments. This would then give me exactly  what I'm looking for.

A1: total
A2: payment
A3: first payment date
A4: balance =A1-A2*(12*(YEAR(TODAY())-YEAR(A3)) + MONTH(TODAY())-MONTH(A3)-(DAY(A3)>1)+(DAY(TODAY())>1))



David
0
Anthony MellorChartered AccountantCommented:
Well, the interest should be ok, the problem i see is the "uneven cashflows" which means the "lump sum payments". Without some form of amortisation table (which makes it trivial to include lump sums) I'd have to explore as off the bat I don't know. Maybe using an array (which I view as a form of table we cannot see, that is stored and worked on by excel) - I think my best suggestion right now is create a fresh question, because Alan's solution is pretty much the answer to the question you have posted.

For example:
----------------------------------------
"Formula calculation of loan balance, with lump sum payments? " 

Looking for a single cell solution not a loan amortisation table solution.

------------------------------------------

although if you can find a way to phrase the question so it is more maths based and doesn't sound so much like a finance question you'll perhaps get attention from more of the purist mavens in here (who in my experience can solve anything). Be sure to mention if you want multiple lump sum payments or just one.

Anthony
0
Anthony MellorChartered AccountantCommented:
David - this EE place works based on "points for solutions" so ask another. I have found a series of simple questions gets much better response than one complex one. I might be inclined to get a single cell formula solution, (which is relatively easy)  WITHOUT lump sum payments. THEN a "follow up" question asking to incorporate future lump sums - my current thoughts are around this being a discount to the main solution, assuming you don't then change the payments. a.
0
Anthony MellorChartered AccountantCommented:
David, go to this  post by Alan and accept it as your solution, because it does answer the question as asked, then create another question asking for the single cell formula, I suggest omit the lump sum payments requirement, I expect you will get a good answer or two, choose the one you like best and accept that.
THEN post a follow up question, linked to your accepted solution, asking to include lump sum payments.

Baby steps gets best results on here and keeps the problem clear.

Avoid as far as possible making it sound like an accounting or finance problem, because really it's a mathematics problem.

are my suggestions - because the "lump sum payments in a single cell" is a REAL challenge, I think.

Anthony
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.