Challenge!!! Working out monthly rent considering monthly dates and Prorated charges.

[AussieGal]

Hi,

I am working on a Database to get rid off a nightmare Excel Spreadsheet for tracking rentals for a homeless program I work for. I am trying to work out how to get an accurate rent calculation that needs to keep track of rent to date changed, payments etc. It will often need to factor in pro rated rents.

EG Rent table shows Date the rent Started lets say 8/23/12 with a rent amount of $650 a month, the calculation for rent would be something like this:
August $650/31(days)*(8/23/12 to 8/31/12 = 8 days) = $167.74
September $650 - total Rent to Date is $ 817.74
October $650 - total Rent due is $1467.74 Etc

I would like to see this result in a field that is shows totals for clients like such:
Client Jane Doe


Rent Start Date End Date Rent to Date
650 8/23/12 1/2/12 1511.07
550 11/2/12 128.33 (this is until today's date)

I hope that makes sense... more so I hope someone is inspired enough to tackle this issue and get me to the result I need.

[June7]

How would Access know when to prorate and when not to? Access needs rules to follow. What condition could be programmed in a query that would instruct 'do this calculation to arrive at rent else use this fixed value'? This would mean the rent is a calculated entity which could be changed if the formula is changed, not a discrete piece of data that exists independently of other data.

You need to pull out a calculator, arrive at a number, collect payment, and enter a record in table for that prorated payment received. Then enter records for each month after that for the amounts paid.

The aggregate calc of receipts to date is another matter and can easily be done on a report.

[AussieGal]

I was thinking that it would only need to look at prorating for the months that the start date or end dates were entered. eg. If start date 8/23/12, work out a calculation to count the days from 8/23/12 to the end of month 8/31/12 (referring to a Month table with this information in it). This would give us 8 days. That could then be used to TIMES that against the Daily charge.

Month Table
Month Last Date Daily Charge
Jan 31 Rent/31
Feb 28 Rent/28
March 31 Rent/31
April 30 Rent/30

Maybe I need to rethink this. I guess I could have 2 separate columns that go along with Start and end dates that allow me to manually calculate the prorated amounts and somehow add them on to a monthly tally sheet. It is complex as many of the clients are waiting on funding so we continue to tally up each month until funding is worked out and then sometimes they pay portions over a set amount of time. Then they can get jobs and then the rent changes again, and sometimes they move to another rental, which we also have to keep a track off, as well as making sure that all the rent they have been charged to date regardless at what rate or in which house is correctly calculated so we know much they owe us vs how much they paid. Due to all the changes and mid month events, it has become extremely hard to keep a clear record on all the different factors. I guess I am just trying to get my head around the best way to do this, so I can begin programming a new database to try and have this work a little more effortlessly. Any suggestions very appreciated.

[recyan]

Don't have the solution, just wondering,
Is the data for Rents stored like below :

RentID	StartDate	RentPerMonth
1	05-Sep-12	400
2	23-Oct-12	650
3	02-Nov-12	550

Thanks

[June7]

So you want to separately keep track of rental obligation and the actual payments. These should be treated as independent transactions. The rent obligation would be like a billing invoice and the payment a remittance on account. Both transactions would be related to an account ID. If you want code to calculate the rent when entering a rental obligation, then needs to be in an event that will perform the calc and save the result to the record. The code itself is not difficult. The real trick is figuring out what event.

[AussieGal]

I am not sure how I am even going to store the rent yet, I am just trying to get the overall picture clear so I can begin programming. I am envisioning a form where I can select the client and the property, and then enter a start date with the amount of Monthly rent that client would be charged (this could be the event?). When there is a rent change, I would enter a stop date (another event) and then create a new record with a new start date date and have that still link to that same client. Payments would be done separately yet linking to the same client so I can run queries as to what is currently due for each client.

[June7]

Rent is a monthly obligation and should be a record for each month. The question is how will that monthly record be generated? Even before that discussion, need to get data relationships defined.