We help IT Professionals succeed at work.

Subtract from total amount each month

Tierix
Tierix asked
on
Hi,

My question is linked to this one: Excel Question

I would like to subtract a fixed amount to a total on the 1st of each months.


See an example of what I would like please (no interests calculation needed):
Let's say the first cell is A1 then A2, A3, A4.

The first 2 cells should have no formula whatsoever, these are fixed amounts.



total amount left to repay  | Amount paid every month | balance (A1-A2 calculated automatically every 1st of each month) | Number of months left until balance (A3) is 0


Is this possible please?
thanks so much
Comment
Watch Question

CERTIFIED EXPERT
Awarded 2010
Top Expert 2013
Commented:
You need one more cell for the date of the first payment. Let's put that in A3
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)

Author

Commented:
THank you so much again for your help! Works like a charm