Excel deduct amounts monthly – automatically?
Hello everyone!
I have the following question:
I have currently entered my debts and repayments into Excel to keep track of the remaining debt and monthly payment.
Now I thought, instead of doing it manually every month, there must be a way to have it deducted automatically on a certain day of the month.
For example, 150 euros remaining balance. Monthly installment on the 15th – 30 euros.
That 30 euros are deducted every month without you having to enter anything yourself.
I hope you understand what I mean.
Can you tell me how to do that?
Thanks in advance! 🙂
(1 rating, 1 votes, rated)You need to be a registered member to rate this.
Loading...
Here is another visual example:
Logically you would go that you could define a start date to calculate the current date so you get a difference and after a fixed cycle e.g. Month change or exceed the start date date date you will count X on it.
=if(day(start date) <=day(today(today());month(today(today()))month(start date);month(today(today()))month(start date)-1)
If necessary, further tests are necessary.
The whole multiplies you then with your post x that you want to pay monthly.
If you want to make a table, use EDatum function
eg
in A1: January 15, 2024
in B1: 150
in A2: =EDATE(A1;1)
in B2: =B1-30
then copy the line 2 into the lines below.
Hello, thank you for the answer 🙂
but that's what it says:
current remaining debt=500€
and the 500€ on the 15th of each month are automatically reduced by, for example, 30€, that is not possible?
Not quite, but so similar
in A1 you write the date on which your debt was 500 euros (eg September 15, 2024), in B1 the 500, in C1 the 30 and in D1 the day of the month in which you repay your part (15)
in A2 you write: =TODAY()
in B2 you write: =B1-C1*(12*YEAR(A2)+MONTH(A2))-(12*YEAR(A1)+MONTH(A1))-1+ROUND(INT(DAY(A2)/D1)/DAY(A2);0))
please write in a no line break!!!!
The formula is so complicated because the months have different many days. The part behind the rounding calculates whether the payday has already passed.
Thank you. I'll try tomorrow!