Calculate Total Owed with Multiple Non-consistent Payments

[kdoederlein]

I don't believe that this particular question has been asked so if it has I apologize in advance. I am provided with a pdf of a transaction log from my client/landlord for each tenant that they are requesting Eviction proceedings for. It looks like this:

Now I have to convert this to an excel spreadsheet and then try to determine the amounts I need. When we file the documents with Court I have to determine the total amount of Rent owed to date, the amount of Water/Sewer, Late/Registration and Pet fees all separately. This is further complicated because there are late payments made and I need to apply these payments to the oldest debt (applied to all of the fees and rent combined) to find out when the tenant has actually paid their rent through (the month they owed no rent but may owe other fees). How can I calculate the amounts owed to date for each line item while applying payments to the oldest debt and then (as this is a process that can take months) apply new rent and costs that are due in successive months? I don't know if I have explained this well because quite frankly I have tried to solve this on paper and can't figure it out! I can't quite conceptualize the layout or process needed to make this simple as payments can be made three months from now and there are sometimes 3 pages of transaction logs! THANK YOU!

 

[Dave Patton]

N.B. You can post a concise example of a sheet with the forum's XL2BB.

If you post data that we can use (not a picture), we may be able to provide assistance.

 

[kdoederlein]

I apologize, I am not actually given any data except a pdf file. I converted the file to a csv file and saved as an Excel workbook/sheet. This is what results:

Transactions_2105071539.xlsx
	A	B	C	D	E	F
5	Date	Reference	Description	Comment		Amount
6	6/30/2020		Security Deposits			304
7	6/30/2020		Security Deposits	Tenant import		-304
8	7/14/2020		Beginning Balance	Start up rent manager		489.13
9	7/15/2020		Water Utility			13.2
10	7/15/2020		Sewer			21.83
11	7/17/2020		Registration Fee	Late Registration Fee		20
12	8/1/2020		School Tax			3
13	8/1/2020		Rent Charge			422
14	8/1/2020		Pet Fee Non-Refundable			20
15	8/6/2020		Late Charge			35
16	8/6/2020		Late Charge			2.5
17	8/14/2020		Water Utility	Water and Sewer Usage		12.4
18	8/14/2020		Sewer	Water and Sewer Usage		20.99
19	9/1/2020		School Tax			3
20	9/1/2020		Rent Charge			422
21	9/1/2020		Pet Fee Non-Refundable			20
22	9/8/2020	CC	Payment Received			-505.05
23	9/10/2020		Late Charge			35
24	9/15/2020		Water Utility			14.4
25	9/15/2020		Sewer			14.4
26	9/18/2020		Registration Fee	Late Registration Fee		20
27	10/1/2020		School Tax			3
28	10/1/2020		Rent Charge			422
29	10/1/2020		Pet Fee Non-Refundable			20
30	10/2/2020	CC	Payment Received			-1,000.00
31	10/6/2020		Late Charge			35
32	11/1/2020		Water Utility	Water and Sewer Charges		17.6
33	11/1/2020		Sewer	Water and Sewer Charges		10.06
34	11/1/2020		School Tax			3
35	11/1/2020		Rent Charge			422
36	11/1/2020		Pet Fee Non-Refundable			20
37	11/1/2020		Registration Fee	Late Registration Fee		20
38	11/9/2020		Late Charge			35
39	12/1/2020		Water Utility	Water and Sewer Usage		18.8
40	12/1/2020		Sewer	Water and Sewer Usage		11.31
41	12/1/2020		School Tax			3
42	12/1/2020		Rent Charge			422
43	12/1/2020		Pet Fee Non-Refundable			20
44	12/1/2020		Registration Fee	Late Registration Fee		20
45	12/7/2020		Late Charge			35
46	1/1/2021		Water Utility	Water and Sewer Usage		20
47	1/1/2021		Sewer	Water and Sewer Usage		12.57
48	1/1/2021		Registration Fee	Late Registration		20
49	1/1/2021		School Tax			3
50	1/1/2021		Rent Charge			422
51	1/1/2021		Pet Fee Non-Refundable			20
52	1/1/2021		Street Light			-10
53	2/1/2021		Registration Fee	Late Registration		20
54	2/1/2021		School Tax			3
55	2/1/2021		Rent Charge			422
56	2/1/2021		Pet Fee Non-Refundable			20
57	2/1/2021		Street Light			-10
58	2/1/2021		Water Utility			21.2
59	2/1/2021		Sewer			13.83
60	2/4/2021		Late Charge			35
61	2/8/2021		Late Charge		35	2669.17
62	2/17/2021	CK	Payment Received		-1,000.00	1669.17
63	3/1/2021		Water Utility	Water and Sewer Charges	20.4	1689.57
64	3/1/2021		Sewer	Water and Sewer Charges	12.99	1702.56
65	3/1/2021		School Tax		3	1705.56
66	3/1/2021		Rent Charge		422	2127.56
67	3/1/2021		Pet Fee Non-Refundable		20	2147.56
68	3/1/2021		Street Light		-10	2137.56
69	3/1/2021		Registration Fee	Late Registration	20	2157.56
70	3/2/2021		Late Charge		35	2192.56
71	3/2/2021	CC	Payment Received		-488.39	1704.17
72	3/8/2021		Late Charge		35	1739.17
73	4/1/2021		Registration Fee	Late Registration	20	1759.17
74	4/1/2021		Water Utility	Water and Sewer charges	18	1777.17
75	4/1/2021		Sewer	Water and Sewer charges	10.48	1787.65
76	4/1/2021		School Tax		3	1790.65
77	4/1/2021		Rent Charge		422	2212.65
78	4/1/2021		Pet Fee Non-Refundable		20	2232.65
79	4/1/2021		Street Light		-10	2222.65
80	4/8/2021		Late Charge	Late Fee	37.5	2260.15
81	5/1/2021		Registration Fee	Late Registration	20	2280.15
82	5/1/2021		Water Utility	Water and Sewer Charges	19.6	2299.75
83	5/1/2021		Sewer	Water and Sewer Charges	12.15	2311.9
84	5/1/2021		School Tax		3	2314.9
85	5/1/2021		Rent Charge		422	2736.9
86	5/1/2021		Pet Fee Non-Refundable		20	2756.9
87	5/1/2021		Street Light		-10	2746.9
88	5/5/2021	CC	Payment Received		-1,000.00	1746.9
89	5/6/2021		Late Charge		35	1781.9
Transactions_2105071539

 

[kdoederlein]

Actually I had to change the layout somewhat as the columns were off. This is the updated version:

Transactions_2105071539.xlsx
	A	B	C	D	E	F	G
5	Date	Reference	Description	Comment		Amount	Balance
6	6/30/2020		Security Deposits			304	304
7	6/30/2020		Security Deposits	Tenant import		-304	0
8	7/14/2020		Beginning Balance	Start up rent manager		489.13	489.13
9	7/15/2020		Water Utility			13.2	502.33
10	7/15/2020		Sewer			21.83	524.16
11	7/17/2020		Registration Fee	Late Registration Fee		20	544.16
12	8/1/2020		School Tax			3	547.16
13	8/1/2020		Rent Charge			422	969.16
14	8/1/2020		Pet Fee Non-Refundable			20	989.16
15	8/6/2020		Late Charge			35	1024.16
16	8/6/2020		Late Charge			2.5	1026.66
17	8/14/2020		Water Utility	Water and Sewer Usage		12.4	1039.06
18	8/14/2020		Sewer	Water and Sewer Usage		20.99	1060.05
19	9/1/2020		School Tax			3	1063.05
20	9/1/2020		Rent Charge			422	1485.05
21	9/1/2020		Pet Fee Non-Refundable			20	1505.05
22	9/8/2020	CC	Payment Received			-505.05	1000
23	9/10/2020		Late Charge			35	1035
24	9/15/2020		Water Utility			14.4	1049.4
25	9/15/2020		Sewer			14.4	1063.8
26	9/18/2020		Registration Fee	Late Registration Fee		20	1083.8
27	10/1/2020		School Tax			3	1086.8
28	10/1/2020		Rent Charge			422	1508.8
29	10/1/2020		Pet Fee Non-Refundable			20	1528.8
30	10/2/2020	CC	Payment Received			-1,000.00	528.8
31	10/6/2020		Late Charge			35	563.8
32	11/1/2020		Water Utility	Water and Sewer Charges		17.6	581.4
33	11/1/2020		Sewer	Water and Sewer Charges		10.06	591.46
34	11/1/2020		School Tax			3	594.46
35	11/1/2020		Rent Charge			422	1016.46
36	11/1/2020		Pet Fee Non-Refundable			20	1036.46
37	11/1/2020		Registration Fee	Late Registration Fee		20	1056.46
38	11/9/2020		Late Charge			35	1091.46
39	12/1/2020		Water Utility	Water and Sewer Usage		18.8	1110.26
40	12/1/2020		Sewer	Water and Sewer Usage		11.31	1121.57
41	12/1/2020		School Tax			3	1124.57
42	12/1/2020		Rent Charge			422	1546.57
43	12/1/2020		Pet Fee Non-Refundable			20	1566.57
44	12/1/2020		Registration Fee	Late Registration Fee		20	1586.57
45	12/7/2020		Late Charge			35	1621.57
46	1/1/2021		Water Utility	Water and Sewer Usage		20	1641.57
47	1/1/2021		Sewer	Water and Sewer Usage		12.57	1654.14
48	1/1/2021		Registration Fee	Late Registration		20	1674.14
49	1/1/2021		School Tax			3	1677.14
50	1/1/2021		Rent Charge			422	2099.14
51	1/1/2021		Pet Fee Non-Refundable			20	2119.14
52	1/1/2021		Street Light			-10	2109.14
53	2/1/2021		Registration Fee	Late Registration		20	2129.14
54	2/1/2021		School Tax			3	2132.14
55	2/1/2021		Rent Charge			422	2554.14
56	2/1/2021		Pet Fee Non-Refundable			20	2574.14
57	2/1/2021		Street Light			-10	2564.14
58	2/1/2021		Water Utility			21.2	2585.34
59	2/1/2021		Sewer			13.83	2599.17
60	2/4/2021		Late Charge			35	2634.17
61	2/8/2021		Late Charge			35	2669.17
62	2/17/2021	CK	Payment Received			-1,000.00	1669.17
63	3/1/2021		Water Utility	Water and Sewer Charges		20.4	1689.57
64	3/1/2021		Sewer	Water and Sewer Charges		12.99	1702.56
65	3/1/2021		School Tax			3	1705.56
66	3/1/2021		Rent Charge			422	2127.56
67	3/1/2021		Pet Fee Non-Refundable			20	2147.56
68	3/1/2021		Street Light			-10	2137.56
69	3/1/2021		Registration Fee	Late Registration		20	2157.56
70	3/2/2021		Late Charge			35	2192.56
71	3/2/2021	CC	Payment Received			-488.39	1704.17
72	3/8/2021		Late Charge			35	1739.17
73	4/1/2021		Registration Fee	Late Registration		20	1759.17
74	4/1/2021		Water Utility	Water and Sewer charges		18	1777.17
75	4/1/2021		Sewer	Water and Sewer charges		10.48	1787.65
76	4/1/2021		School Tax			3	1790.65
77	4/1/2021		Rent Charge			422	2212.65
78	4/1/2021		Pet Fee Non-Refundable			20	2232.65
79	4/1/2021		Street Light			-10	2222.65
80	4/8/2021		Late Charge	Late Fee		37.5	2260.15
81	5/1/2021		Registration Fee	Late Registration		20	2280.15
82	5/1/2021		Water Utility	Water and Sewer Charges		19.6	2299.75
83	5/1/2021		Sewer	Water and Sewer Charges		12.15	2311.9
84	5/1/2021		School Tax			3	2314.9
85	5/1/2021		Rent Charge			422	2736.9
86	5/1/2021		Pet Fee Non-Refundable			20	2756.9
87	5/1/2021		Street Light			-10	2746.9
88	5/5/2021	CC	Payment Received			-1,000.00	1746.9
89	5/6/2021		Late Charge			35	1781.9
Transactions_2105071539

Any Help would be appreciated

 

[Dave Patton]

The data shows that the balance was 1000 at Aug 9 and that balance was paid on Oct 2.

If you recap the various categories the net charges from Aug 9 is 4272.29 and receipts 2488.39.

You can secure the detail with Sumifs or by manually putting the amounts in adjacent columns and summing.

You would have to apply the receipts according to the rules in your local.

You could then summarize the required information.

 

[Dave Patton]

The data did not import cleanly. I converted the dates. Edit the wording above. The balance Sep 8 2020 G22 was 1000;
this was paid Oct 2 2020. Recap the transactions subsequent to Sep 8 and exclude the Oct 2 amount of 1000.

T202105a.xlsm
	C	D
94	Payment Received	-2,488.39
95	Rent charge	3,376.00
96	Street Light	-50.00
97	Pet Fee Non-Refundable	160.00
98	School Tax	24.00
99	Sewer	97.79
100	Water Utility	150.00
101	Late Charge	352.50
102	Registration Fee	160.00
103		1,781.90
104
105
106	Sep 10 - May 6 2021
1ff

Cell Formulas
Range	Formula
D94	D94	=SUMIFS($F$23:$F$89,$C$23:$C$89,C94)+1000
D95:D102	D95	=SUMIFS($F$23:$F$89,$C$23:$C$89,C95)
D103	D103	=SUM(D94:D102)