Daily Interest Loan Scheduler | Missed Payments

[aurelius33]

So I'm constructing what will be a large spreadsheet with something like 10k to 12k rows at 15 to 17 columns wide in a single table. I am attempting to keep the formulas simple and not run deep nested IFs and the like. The terms for a loan are defined in what I'm calling the blue zero line. Under this line would be the first payment for the loan defined above in the blue line. With new loans being added every other week, keeping the formulas contained within a line and/or using simple directions is key. Resorting this thing and having to redefine all the fixed cells ($) would be a pain. The terms in the blue line are layed out as:

D2 : beginning date of the loan​

E2 : the expected payment​

F2 : the numbers of expected payments​

H2 : the rate (%)​

I2 : the beginning balance​

In between blue term lines are:

Column A : the who​

Column B : the payment numbers (shortened for this example)​

Column C : the status of the payment (blank = not time yet, 1 = payment made, 2 = partial or special circumstances, 3 = not made)​

Column D : dates of the remaining payments​

Column E : the expected payment I manually copy from the expected payment calculation. A formula could be used once I see how this sheet handles loaded full of data.​

Column F : any additional payments​

Column G : the principal for that row's payment​

Column H : the interest for that row's payment​

Column I : the balance after the payment​

Column J : any and all cumulative missed payments and/or shortages on payments.​

My issues are:

1. The prior column needs to hold a running total of missed payments. I have the interest applying to the balance in the event of a missed payment(s).
2. I have conditional formatting hiding the expected payment in column E so one can tell at a glance where someone is on the loan. I could also use the status column but it just doesn't seem that intuitive to me. Is there a better approach someone knows of?

SP Example.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Leasee	Num	Status	L1 Date	L1 Pmt	L1 Add	L1 Prin	L1 Int	L1 Balance	L1 Prior
2	Fry, Philip J		1	4/23/2024	$ 872.93	86	$ -	16.50%	$ 65,610.00
3	Fry, Philip J	1	1	5/4/2024	$ 872.93	$ -	$ 546.68	$ 326.25	$ 65,063.32	$ 0.00
4	Fry, Philip J	2	1	5/11/2024	$ 872.93	$ -	$ 667.04	$ 205.89	$ 64,396.28	$ 0.00
5	Fry, Philip J	3	3	5/18/2024	$ -	$ -	$ (203.77)	$ 203.77	$ 64,600.05	$ 0.00
6	Fry, Philip J	4	1	5/25/2024	$ 872.93	$ -	$ 668.51	$ 204.42	$ 63,931.54	$ 0.00
7	Fry, Philip J	5	1	6/1/2024	$ 872.93		$ 670.63	$ 202.30	$ 63,260.91	$ 0.00
8	Fry, Philip J	6	1	6/7/2024	$ 872.93		$ 701.35	$ 171.58	$ 62,559.56	$ 0.00
9	Fry, Philip J	7	1	6/14/2024	$ 872.93		$ 674.97	$ 197.96	$ 61,884.59	$ 0.00
10	Fry, Philip J	8		6/21/2024			$ 677.10	$ 195.83	$ 61,207.49	$ 0.00
11	Fry, Philip J	9		6/28/2024			$ 679.25	$ 193.68	$ 60,528.24	$ 0.00
12	Fry, Philip J	10		7/5/2024			$ 681.40	$ 191.53	$ 59,846.84	$ 0.00
13	Fry, Philip J	11		7/12/2024			$ 683.55	$ 189.38	$ 59,163.29	$ 0.00
14	Rodriguez, Bender		1	5/3/2024	$ 738.67	216		12.50%	$ 124,344.00
15	Rodriguez, Bender	1	1	5/10/2024	$ 738.67		$ 440.59	$ 298.08	$ 123,903.41	$ (0.00)
16	Rodriguez, Bender	2	1	5/17/2024	$ 738.67		$ 441.64	$ 297.03	$ 123,461.77	$ (0.00)
17	Rodriguez, Bender	3	1	5/24/2024	$ 738.67		$ 442.70	$ 295.97	$ 123,019.07	$ (0.00)
18	Rodriguez, Bender	4	1	5/31/2024	$ 738.67		$ 443.76	$ 294.91	$ 122,575.31	$ (0.00)
19	Rodriguez, Bender	5	3	6/7/2024	$ -		$ (293.84)	$ 293.84	$ 122,869.15	$ (0.00)
20	Rodriguez, Bender	6	1	6/14/2024	$ 738.67		$ 444.12	$ 294.55	$ 122,425.03	$ (0.00)
21	Rodriguez, Bender	7		6/21/2024			$ 445.19	$ 293.48	$ 121,979.84	$ (0.00)
22	Rodriguez, Bender	8		6/28/2024			$ 446.25	$ 292.42	$ 121,533.59	$ (0.00)
23	Rodriguez, Bender	9		7/5/2024			$ 447.32	$ 291.35	$ 121,086.27	$ (0.00)
24	Total
Pmts

Cell Formulas
Range	Formula
G3:G13,G15:G23	G3	=[@[L1 Pmt]]-[@[L1 Int]]
H3:H13	H3	=ROUND(I2*0.165*([@[L1 Date]]-D2)/365,2)
I3:I13,I15:I23	I3	=IF([@Status]=3,[@[L1 Int]]+I2,I2-[@[L1 Prin]])
J3:J13,J15:J23	J3	=I2-[@[L1 Balance]]-[@[L1 Prin]]+J2+[@[L1 Add]]
D4:D7,D15:D23,D9:D13	D4	=D3+7
E2,E14	E2	=ROUND(([@[L1 Int]]/52+[@[L1 Int]]/52/((1+[@[L1 Int]]/52)^[@[L1 Add]]-1))*[@[L1 Balance]],2)
E3:E13	E3	=IF([@Status]>=3,0,IF(872.93+[@[L1 Add]]<=I2,872.93+[@[L1 Add]],I2+[@[L1 Int]]+[@[L1 Add]]))
E15:E23	E15	=IF([@Status]>=3,0,IF(738.67+[@[L1 Add]]<=I14,738.67+[@[L1 Add]],I14+[@[L1 Int]]+[@[L1 Add]]))
H15:H23	H15	=ROUND(I14*0.125*([@[L1 Date]]-D14)/365,2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E23	Expression	=(C2="")	text	NO