|Tenant rent spreadsheet|
I own rental property in which tenants can pay weekly, bi-weekly or monthly. I am trying to create a spreadsheet in which I can see how much rent I collect FOR EACH MONTH I INVOICE. It is not as simple as just adding up what I collect for each month because sometimes the tenants pay early, and sometimes they pay late. So if a tenant is 30-days behind on their rent, and they pay two months rent today, half of that payment needs to be applied (automatically) to last month. If I just add up what I collected for each month, then last month will be missing a payment and this month will have too much. It gets even more complicated with weekly and bi-weekly payments, especially when they overlap their billing cycles over two different months.
I have created a spreadsheet already with the following columns:
A1-A365: January 1, 2010 through December 31, 2010
B1-B365: Invoice amounts in dollars. Each row represents what I bill the tenant and when. If a tenant is invoiced $150 "weekly," then every 7th cell (row) in this column has a "$150" in it. Bi-weekly, every 14 days. Both always on Fridays. And monthly tenants get charged (usually) on the first, but not always because of pro-rations if they move in during the middle of a month.
C1-C365: This column shows all the payments the tenant made, and of course, each payment is entered on the row that corresponde to the date they paid.
D1-D365: This column represents a simple calculation of their "running deficit/credit." So if a tenant is invoiced $600 on June 1st, but doesn't pay until the 5th, then the rows for June 1st to June 4th will have a "$600" in them, and then after that they will have zeroes. Overpayments show up as negative dollar amounts.
E1-E365: This column contains a formula I created representing how many days each charge represents. All payments, for example, between $100 and $249 MUST be weekly, and therefore a "7" appears in that cell ONLY. $250-$449 are bi-weekly, and the row corresponding to that charge will show a "14." Anything $450 or over is monthly, and I have already created a formula which puts a "28," "29," "30" or "31" in that cell, depending on the month. It even puts a pro-rated number of days in the cell if the tenant moves in in the middle of the month, say, so that is also taken care of.
So here is what it kinda looks like for a weekly tenant getting invoiced $150 every Friday, who paid $50 on Jan 2 and $70 on Jan 5, and $200 on Jan 8:
(COL A) (COL B) (COL C) (COL D) (COL E)
(DATE) (INVOICE) (PAYMENT) (BALANCE) (DAYS PER CHARGE)
01/01/10 $150 $0 $150 7
01/02/10 $0 $50 $100
01/03/10 $0 $0 $100
01/04/10 $0 $0 $100
01/05/10 $0 $70 $30
01/06/10 $0 $0 $30
01/07/10 $150 $0 $180 7
01/08/10 $0 $200 -$20
01/09/10 $0 $0 -$20
Of course, this is easy until it gets into the next month.
Does anyone have a solution as to how I can use this to determine what I am really collecting per month BASED ON WHAT I INVOICE THAT MONTH? I created column E because I thought perhaps I could use those numbers (7,14,31, etc.) to somehow divide the invoiced amount to determine what I am actually charging PER DAY. So if I invoice a tenant $600 in a month that has 30 days, I am really charging him $20/day. Weekly rent of $175 is really $25/day, etc. This would allow me to create a column representing what each day really costs, and then the final step would be to somehow apply the collected rents to that column and show how far behind the tenant actually is.
But I figure if I can break it down to daily amounts using the Column E data, then I can simply total up the months and I'm done.