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

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.

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

ok thank you very much

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

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

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

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

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 :-)

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

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