Calculate Total Owed with Multiple Non-consistent Payments

kdoederlein

New Member
Joined
May 10, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:
1620671355051.png


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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
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
ABCDEF
5DateReferenceDescriptionCommentAmount
66/30/2020Security Deposits304
76/30/2020Security DepositsTenant import-304
87/14/2020Beginning BalanceStart up rent manager489.13
97/15/2020Water Utility13.2
107/15/2020Sewer21.83
117/17/2020Registration FeeLate Registration Fee20
128/1/2020School Tax3
138/1/2020Rent Charge422
148/1/2020Pet Fee Non-Refundable20
158/6/2020Late Charge35
168/6/2020Late Charge2.5
178/14/2020Water UtilityWater and Sewer Usage12.4
188/14/2020SewerWater and Sewer Usage20.99
199/1/2020School Tax3
209/1/2020Rent Charge422
219/1/2020Pet Fee Non-Refundable20
229/8/2020CCPayment Received-505.05
239/10/2020Late Charge35
249/15/2020Water Utility14.4
259/15/2020Sewer14.4
269/18/2020Registration FeeLate Registration Fee20
2710/1/2020School Tax3
2810/1/2020Rent Charge422
2910/1/2020Pet Fee Non-Refundable20
3010/2/2020CCPayment Received-1,000.00
3110/6/2020Late Charge35
3211/1/2020Water UtilityWater and Sewer Charges17.6
3311/1/2020SewerWater and Sewer Charges10.06
3411/1/2020School Tax3
3511/1/2020Rent Charge422
3611/1/2020Pet Fee Non-Refundable20
3711/1/2020Registration FeeLate Registration Fee20
3811/9/2020Late Charge35
3912/1/2020Water UtilityWater and Sewer Usage18.8
4012/1/2020SewerWater and Sewer Usage11.31
4112/1/2020School Tax3
4212/1/2020Rent Charge422
4312/1/2020Pet Fee Non-Refundable20
4412/1/2020Registration FeeLate Registration Fee20
4512/7/2020Late Charge35
461/1/2021Water UtilityWater and Sewer Usage20
471/1/2021SewerWater and Sewer Usage12.57
481/1/2021Registration FeeLate Registration20
491/1/2021School Tax3
501/1/2021Rent Charge422
511/1/2021Pet Fee Non-Refundable20
521/1/2021Street Light-10
532/1/2021Registration FeeLate Registration20
542/1/2021School Tax3
552/1/2021Rent Charge422
562/1/2021Pet Fee Non-Refundable20
572/1/2021Street Light-10
582/1/2021Water Utility21.2
592/1/2021Sewer13.83
602/4/2021Late Charge35
612/8/2021Late Charge352669.17
622/17/2021CKPayment Received-1,000.001669.17
633/1/2021Water UtilityWater and Sewer Charges20.41689.57
643/1/2021SewerWater and Sewer Charges12.991702.56
653/1/2021School Tax31705.56
663/1/2021Rent Charge4222127.56
673/1/2021Pet Fee Non-Refundable202147.56
683/1/2021Street Light-102137.56
693/1/2021Registration FeeLate Registration202157.56
703/2/2021Late Charge352192.56
713/2/2021CCPayment Received-488.391704.17
723/8/2021Late Charge351739.17
734/1/2021Registration FeeLate Registration201759.17
744/1/2021Water UtilityWater and Sewer charges181777.17
754/1/2021SewerWater and Sewer charges10.481787.65
764/1/2021School Tax31790.65
774/1/2021Rent Charge4222212.65
784/1/2021Pet Fee Non-Refundable202232.65
794/1/2021Street Light-102222.65
804/8/2021Late ChargeLate Fee37.52260.15
815/1/2021Registration FeeLate Registration202280.15
825/1/2021Water UtilityWater and Sewer Charges19.62299.75
835/1/2021SewerWater and Sewer Charges12.152311.9
845/1/2021School Tax32314.9
855/1/2021Rent Charge4222736.9
865/1/2021Pet Fee Non-Refundable202756.9
875/1/2021Street Light-102746.9
885/5/2021CCPayment Received-1,000.001746.9
895/6/2021Late Charge351781.9
Transactions_2105071539
 
Upvote 0
Actually I had to change the layout somewhat as the columns were off. This is the updated version:

Transactions_2105071539.xlsx
ABCDEFG
5DateReferenceDescriptionCommentAmountBalance
66/30/2020Security Deposits304304
76/30/2020Security DepositsTenant import-3040
87/14/2020Beginning BalanceStart up rent manager489.13489.13
97/15/2020Water Utility13.2502.33
107/15/2020Sewer21.83524.16
117/17/2020Registration FeeLate Registration Fee20544.16
128/1/2020School Tax3547.16
138/1/2020Rent Charge422969.16
148/1/2020Pet Fee Non-Refundable20989.16
158/6/2020Late Charge351024.16
168/6/2020Late Charge2.51026.66
178/14/2020Water UtilityWater and Sewer Usage12.41039.06
188/14/2020SewerWater and Sewer Usage20.991060.05
199/1/2020School Tax31063.05
209/1/2020Rent Charge4221485.05
219/1/2020Pet Fee Non-Refundable201505.05
229/8/2020CCPayment Received-505.051000
239/10/2020Late Charge351035
249/15/2020Water Utility14.41049.4
259/15/2020Sewer14.41063.8
269/18/2020Registration FeeLate Registration Fee201083.8
2710/1/2020School Tax31086.8
2810/1/2020Rent Charge4221508.8
2910/1/2020Pet Fee Non-Refundable201528.8
3010/2/2020CCPayment Received-1,000.00528.8
3110/6/2020Late Charge35563.8
3211/1/2020Water UtilityWater and Sewer Charges17.6581.4
3311/1/2020SewerWater and Sewer Charges10.06591.46
3411/1/2020School Tax3594.46
3511/1/2020Rent Charge4221016.46
3611/1/2020Pet Fee Non-Refundable201036.46
3711/1/2020Registration FeeLate Registration Fee201056.46
3811/9/2020Late Charge351091.46
3912/1/2020Water UtilityWater and Sewer Usage18.81110.26
4012/1/2020SewerWater and Sewer Usage11.311121.57
4112/1/2020School Tax31124.57
4212/1/2020Rent Charge4221546.57
4312/1/2020Pet Fee Non-Refundable201566.57
4412/1/2020Registration FeeLate Registration Fee201586.57
4512/7/2020Late Charge351621.57
461/1/2021Water UtilityWater and Sewer Usage201641.57
471/1/2021SewerWater and Sewer Usage12.571654.14
481/1/2021Registration FeeLate Registration201674.14
491/1/2021School Tax31677.14
501/1/2021Rent Charge4222099.14
511/1/2021Pet Fee Non-Refundable202119.14
521/1/2021Street Light-102109.14
532/1/2021Registration FeeLate Registration202129.14
542/1/2021School Tax32132.14
552/1/2021Rent Charge4222554.14
562/1/2021Pet Fee Non-Refundable202574.14
572/1/2021Street Light-102564.14
582/1/2021Water Utility21.22585.34
592/1/2021Sewer13.832599.17
602/4/2021Late Charge352634.17
612/8/2021Late Charge352669.17
622/17/2021CKPayment Received-1,000.001669.17
633/1/2021Water UtilityWater and Sewer Charges20.41689.57
643/1/2021SewerWater and Sewer Charges12.991702.56
653/1/2021School Tax31705.56
663/1/2021Rent Charge4222127.56
673/1/2021Pet Fee Non-Refundable202147.56
683/1/2021Street Light-102137.56
693/1/2021Registration FeeLate Registration202157.56
703/2/2021Late Charge352192.56
713/2/2021CCPayment Received-488.391704.17
723/8/2021Late Charge351739.17
734/1/2021Registration FeeLate Registration201759.17
744/1/2021Water UtilityWater and Sewer charges181777.17
754/1/2021SewerWater and Sewer charges10.481787.65
764/1/2021School Tax31790.65
774/1/2021Rent Charge4222212.65
784/1/2021Pet Fee Non-Refundable202232.65
794/1/2021Street Light-102222.65
804/8/2021Late ChargeLate Fee37.52260.15
815/1/2021Registration FeeLate Registration202280.15
825/1/2021Water UtilityWater and Sewer Charges19.62299.75
835/1/2021SewerWater and Sewer Charges12.152311.9
845/1/2021School Tax32314.9
855/1/2021Rent Charge4222736.9
865/1/2021Pet Fee Non-Refundable202756.9
875/1/2021Street Light-102746.9
885/5/2021CCPayment Received-1,000.001746.9
895/6/2021Late Charge351781.9
Transactions_2105071539


Any Help would be appreciated
 
Upvote 0
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.
 
Upvote 0
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
CD
94Payment Received-2,488.39
95Rent charge3,376.00
96Street Light-50.00
97Pet Fee Non-Refundable160.00
98School Tax24.00
99Sewer97.79
100Water Utility150.00
101Late Charge352.50
102Registration Fee160.00
1031,781.90
104
105
106Sep 10 - May 6 2021
1ff
Cell Formulas
RangeFormula
D94D94=SUMIFS($F$23:$F$89,$C$23:$C$89,C94)+1000
D95:D102D95=SUMIFS($F$23:$F$89,$C$23:$C$89,C95)
D103D103=SUM(D94:D102)
 
Upvote 0
Solution

Forum statistics

Threads
1,223,885
Messages
6,175,187
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top