Update loan balance every month

[malaki1974]

Hello,
Assume a loan start amount, current loan amount, and a start date and todays date. I’d like to see in cell b2 (Current loan amount) an updated remaining amount every month until 0.
The monthly loan payment amount can be in the formula or in another column. Assume 1000 dollars monthly payment for the below to be showing correctly with this new formula someone can help me write I hope!

a	b	c	d
10000	5000	Jan 1 2022	May 1 2022

thank you,

me

 

[ouvay]

Not sure if this is what you're looking for...

but if it isn't please explain in detail what you expect from the solution

Table1 is the little table to the right - this will serve as the master table where you will be able to input information about the loans handed out.

Table 2 will automatically calculate each loan's payment plan

P.S - Please ignore/delete column F, unless you require it

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Entry Number	Loan Applicant	Total Loan Amount	Total Paid (taking for granted that monthly payments are made)	Current Payment Amount	Starting Date	Monthly Payments (1st of Every Month)		Helper	Applicant	Total Loaned	Monthly Payments	Date	Loan Duration
2	1	Tim	10000	1000	1000		2022/07/01		1	Tim	10000	1000	2022/06/14	10
3	2	Tim	10000	2000	1000		2022/08/01		11	Burton	50000	15000	2021/08/10	4
4	3	Tim	10000	3000	1000		2022/09/01		15	Drew	15000	4000	2022/04/20	4
5	4	Tim	10000	4000	1000		2022/10/01		19	Barrymore	1750	300	2022/05/14	6
6	5	Tim	10000	5000	1000		2022/11/01		25
7	6	Tim	10000	6000	1000		2022/12/01
8	7	Tim	10000	7000	1000		2023/01/01
9	8	Tim	10000	8000	1000		2022/02/01
10	9	Tim	10000	9000	1000		2022/03/01
11	10	Tim	10000	10000	1000		2022/04/01
12	11	Burton	50000	15000	15000		2021/09/01
13	12	Burton	50000	30000	15000		2021/10/01
14	13	Burton	50000	45000	15000		2021/11/01
15	14	Burton	50000	50000	5000		2021/12/01
16	15	Drew	15000	4000	4000		2022/05/01
17	16	Drew	15000	8000	4000		2022/06/01
18	17	Drew	15000	12000	4000		2022/07/01
19	18	Drew	15000	15000	3000		2022/08/01
20	19	Barrymore	1750	300	300		2022/06/01
21	20	Barrymore	1750	600	300		2022/07/01
22	21	Barrymore	1750	900	300		2022/08/01
23	22	Barrymore	1750	1200	300		2022/09/01
24	23	Barrymore	1750	1500	300		2022/10/01
25	24	Barrymore	1750	1750	250		2022/11/01
26	25
27
28
29
30
31
Sheet1

Cell Formulas
Range	Formula
A2:A31	A2	=IF(B1="","",ROW()-1)
B2:B31	B2	=IFERROR(IF(VLOOKUP(A2,Table1[[Helper]:[Applicant]],2)=0,"",VLOOKUP(A2,Table1[[Helper]:[Applicant]],2)),"")
C2:C31	C2	=IFERROR(INDEX(Table1[Total Loaned],MATCH(B2,Table1[Applicant],0)),"")
D2:D31	D2	=IFERROR(MIN(INDEX(Table1[Monthly Payments],MATCH(B2,Table1[Applicant],0))+IFERROR(LOOKUP(2,1/($B$1:B1=B2),$D$1:D1),0),C2),"")
E2:E31	E2	=IFERROR(D2-LOOKUP(2,1/($B$1:B1=B2),$D$1:D1),D2)
I3:I6	I3	=I2+N2
N2:N6	N2	=IF([@Applicant]="","",ROUNDUP([@[Total Loaned]]/[@[Monthly Payments]],0))
G2:G31	G2	=IF([@[Loan Applicant ]]="","",DATE(YEAR(INDEX(Table1[Date],MATCH(B2,Table1[Applicant],0))),MONTH(IFERROR(LOOKUP(2,1/($B$1:B1=B2),$G$1:G1),INDEX(Table1[Date],MATCH(B2,Table1[Applicant],0))))+1,DAY(1)))

 

[malaki1974]

Hello,

I will look at this later this evening. I appreciate you trying to help me out.

 

[malaki1974]

Thank you for the direction help. In looking at yours it made me think something like this: 100000−(1000×(DATEDIF(B3,TODAY(),"M"))) so that every month the number next to my mortgage would be reduced by $1000. b3 represents the date the mortgage began. So Jan 1, 2022 would be the date, $100,000 is loan amount, and $1000 is mortgage payment (fake data). In my liability column it would show $95,000 as soon as we hit June. This way I have a semi-accurate representation (ignoring interest etc) of the current liability for a bunch of loans I have without having to manually update whenever I open the sheet to check.

 

[ouvay]

Thank you for the direction help. In looking at yours it made me think something like this: 100000−(1000×(DATEDIF(B3,TODAY(),"M"))) so that every month the number next to my mortgage would be reduced by $1000. b3 represents the date the mortgage began. So Jan 1, 2022 would be the date, $100,000 is loan amount, and $1000 is mortgage payment (fake data). In my liability column it would show $95,000 as soon as we hit June. This way I have a semi-accurate representation (ignoring interest etc) of the current liability for a bunch of loans I have without having to manually update whenever I open the sheet to check.

Ah yeah! That is a clever way to handle it too.. good on you

Be sure to add the MIN function in it to prevent it from subtracting more than the total amount and going in the negative.

hope that helped!

 

[malaki1974]

Good idea. I might do it but I was thinking if it goes negative the loan is paid off and then I delete the row! But seriously, not sure how to add the MIN so I might just try it.