Question Automatically Increase Rent after 12 months @ 10%

[Mohsin Malik]

I have tenants that are agreed in agreement table of my Property Management ms-access database to increase the rent after every 12 months @ 10%, how can ms-access auto-calculate the increase rent after every 12 months @ 10%, for example, the agreement date of tenant is 01-07-2010 with the above terms and conditions of starting rent is $1000 per month, the rent from 01-07-2010 to 01-07-2011 is @ ($1000 x 12 =$12000) and from 01-07-2011 x 01-05-2012 ($1000+($1000*10%)=$1100 x 11 = $12100) it means that the total rent due from 01-07-2010 to 01-05-2012 is ($12000+$12100=$24100), how can ms-access will do this in automation, please help me out, thanks in advance

 

[plog]

I understand your calculation, I just don't understand where this goes. Do you want a query? If so can you provide sample data from the table that this query will be built on and the sample data you want this query to return?

 

[Pat Hartman]

This is something you want to save. Once a month, you need to run an update query. The query will search for leases that expire the following month and for which there is an automatic escallation agreement. The query will update the rent with the rent * the increase percent and change the expiration date.

 

[Mohsin Malik]

I understand your calculation, I just don't understand where this goes. Do you want a query? If so can you provide sample data from the table that this query will be built on and the sample data you want this query to return?

Thanks for the reply, i have attached the sample data for your review to help me out, Yes i want the query or something like that i have no idea what to do because after this sort of calculation of Automatically Increase Rent after months i want to handle the accounting system like total rent amount dues by each tenant less amount paid by tenant and balance as well, please review the Rent Increase Query in attached database?

 

[Mohsin Malik]

This is something you want to save. Once a month, you need to run an update query. The query will search for leases that expire the following month and for which there is an automatic escallation agreement. The query will update the rent with the rent * the increase percent and change the expiration date.

I have a question for an update query that i also want to manage the amount receivable from tenants less amount received and balance. for example i have a tenant whose agreement start date is 01-07-2009, Rent Amount is $1000 per month, Rent Increase after 12 Months @ 10%, now as of today i want to the total of amount receivable from this tenant - amount received and balance, how can i do this?

 

[Galaxiom]

I would not use an update query to change the rent rate but rather calculate the current rate from values recorded in the original agreement each time the invoice is generated using the number of escalation periods since the original date of the agreement and the escalation rate.

It is generally much better to record the actual agreement than to automate such changes. It is safer and more easily traceable. Failing to run the update or accidentally running it more than once at the scheduled time will result in errors.

Updating the rent rate means the original information is destroyed and is actually a breach of normalization since the rate can be calculated from the record of the agreement.

 

[RainLover]

I would go even further to say that a new Table is required to store a history of Rates.

Things like Rent Increases change. Bad Market, Good Market, Improvements to Dwelling, Partial distruction of the dwelling and so on.

The Table would have

PrimaryKey Autonumber
PropertyID if required
Rate
RatePeriod. e.g. Weekly Monthly etc
StartDate
ProposedReviewDate.

Automation is not in my opinion a safe option.

 

[Mohsin Malik]

I would not use an update query to change the rent rate but rather calculate the current rate from values recorded in the original agreement each time the invoice is generated using the number of escalation periods since the original date of the agreement and the escalation rate.

It is generally much better to record the actual agreement than to automate such changes. It is safer and more easily traceable. Failing to run the update or accidentally running it more than once at the scheduled time will result in errors.

Updating the rent rate means the original information is destroyed and is actually a breach of normalization since the rate can be calculated from the record of the agreement.

I have a software in Visual Fox Pro which is doing the same calculation as of my requirement, i dont know what trick they used to automatically increase rent after specific period as well as showing complete record of tenant and agreement, i want to make ms-access to work like that? If i can change the system date to #1/1/2020# the foxpro software calculate the rent receivable upto year 2020 and also showing me the amount receive in year 2020 as of per month? Any idea how to do this in ms-access?

 

[Galaxiom]

The current rent for the period can be calculated with an exponential formula.

(OriginalRent * (1 + EscalationRate)) ^ WholeEscalationPeriods

WholeEscalationPeriods can be calculated by a function something similar to calculating a persons age.
http://msdn.microsoft.com/en-us/library/aa227466(v=vs.60).aspx

To answer the original question about calculating the total rent value for an extended time period it could definitely be done in loop. However it might be possible to use some algebra to re-express it as a single formula.

 

[Mohsin Malik]

The current rent for the period can be calculated with an exponential formula.

(OriginalRent * (1 + EscalationRate)) ^ WholeEscalationPeriods

WholeEscalationPeriods can be calculated by a function something similar to calculating a persons age.
http://msdn.microsoft.com/en-us/library/aa227466(v=vs.60).aspx

To answer the original question about calculating the total rent value for an extended time period it could definitely be done in loop. However it might be possible to use some algebra to re-express it as a single formula.

Okay thanks for the idea, would anyone help me in doing the total rent value for extended time period by loop or using some algebra to re-express it as a single formula, shall i have to do loops in Queries or on forms?

 

[Galaxiom]

The loop would have to be done in a custom function in VBA.

The algebra is not looking encouraging as I am very rusty on it but a maths whizz might be able to deal with it.

 

[Pat Hartman]

When you change the specifications, you get different advice.

If you need to track rent history, you can store the rents for a period in a history table or you can derive the rent by analyzing a tenent's payment history.

There is no reason to rely on memory to run an update query periodically. Create a log table to track each time it is run. Add code to the open event of the startup form to check the log and run the query if necessary. I believe I also said that the update query should also change the rent expiration date. That will keep the query from doubling up on increases if it were to run multiple times. It wouldn't select the records that had already been updated since they wouldn't be expiring next month.

Projecting rent increases for planning is a calculation. Glaxiom posted a formula for you. However, projecting rent increases is actualy a little more complicated. In some cases, you may have a long term lease with specificed periodic increases. In others, you will need to use a standard value based on what you think inflation will be.