Increment for specific tenant for specific month and go on

[haroon.mansha]

Hi,
I am thinking that let say i have 4 tenants who joined during different months on 12 months contract, what if we continue and we agreed on 10% increment after 1 year .
How to control for each customer .

For Example Mr.A joined in month January , his rent will increased automatically in next year january 10% (So basic + 10% for the rest of year)
Mr.B joined March his rent will increased automatically in next year March (so basic + 10% increment for rest of year) and so on .

i tried but seems no body worked on this .

Can you give me idea on this.

Thanks in Advance.

 

[mike60smart]

If you have a Clients Table and Payments Table with a Junction Table to deal with Many Clients make Many Payments.

When you make a New Payment you can automatically add 10% to the Basic Rent

 

[arnelgp]

you may also add a Table where you can put the increase percent and the effectivity date.
you will also need a vba code to check this table if some of your clients are due for the increase.

 

[plog]

First, nothing is "automatic". Second, you need a contracts table:

tblContracts
con_ID, autonumber, primary key
ID_Tenant, number, foreign key to tenants table
con_StartDate, date, data contract starts
con_EndDate, date, date contract ends
con_MonthAmount, number, amount of montly rent for duration of contract
(other fields: e.g. contract type, unit number, notes, etc.)

Then, every contract goes in there as a record. You get a new tenant, you put their initial contract in there, then in a year you add a new record to it if they are going to stay.

Every month you run a query on that data and see who's going to or has expired. If they are continuing you add a new record in tblContracts for their new agreement and include the 10% increase. With some coding you can turn that query into a form and have a button besides each contract that performs some VBA and "automatically" adds a new record to tblContracts with every piece of data it should have--it can copy the TenantID, calculate the new STart/end dates and make the amount 110% of the old amount.

Again though, this is all predicated on having the correct table structure to handle this.

 

[Solo712]

Hi,
I am thinking that let say i have 4 tenants who joined during different months on 12 months contract, what if we continue and we agreed on 10% increment after 1 year .
How to control for each customer .

For Example Mr.A joined in month January , his rent will increased automatically in next year january 10% (So basic + 10% for the rest of year)
Mr.B joined March his rent will increased automatically in next year March (so basic + 10% increment for rest of year) and so on .

i tried but seems no body worked on this .

Can you give me idea on this.

Thanks in Advance.

The solution depends on your business rules. My rental application has each apartment rent schedule (not a tenant but an apartment table!). Scheduled rent increases are automatically implemented in a receivables run at the beginning of each month. When the month of rent increase becomes current the rent is recalculated based on the defined percentage and the date of next increase is pushed to next year. (The system has flexibility so the date of the next increase could be manually overridden). The system also issues notice to each lease holder two months before the rent increase becomes due.

@Pat - The "contract" table would be an overkill in most situations. I can't think of any use of it except where the landlord wants to keep a history of contracts. All the fields to implement the terms of a lease logically relate to the tenancy section of the apartment/unit, and the normalization rules pretty much require to place them there.

Best,
Jiri

 

[Pat Hartman]

I think Plog made the contract suggestion. It is actually the lease in this case and at each renewal, a new lease would be added. Doing a lease, you could do something like - the last three months are discounted but the first nine months are full price or the first month is free. Whatever. Both versions of the lease would be added at the time you make the agreement and billing automatically switches to the last version at the right time.

 

[Solo712]

I think Plog made the contract suggestion. It is actually the lease in this case and at each renewal, a new lease would be added. Doing a lease, you could do something like - the last three months are discounted but the first nine months are full price or the first month is free. Whatever. Both versions of the lease would be added at the time you make the agreement and billing automatically switches to the last version at the right time.

Few things, Pat. I wrote my app for the Canadian market where the lease regime is standardized and must conform to the Landlord-Tenant legislation. Not much opportunity to force tenant into a new term of lease, for example. The lease extend automatically after a year and becomes a monthly occupation with a 60 days notice. This regime exists in all Canadian provinces that I am familiar with. As for discounts, free months, etc. these optimally are not attributes of contracts but again of the units to be rented. In my program, they have a field called "discount model" which has a discount formula. So eg. all two bedrooms have such and such discount and studios such and such, in a given time period Binding to units this way has the advantage for the landlord in that he/she can market the units wholesale depending on varying vacancy rates for different types of units.

There may be situations where the contract/leases do make sense as a separate table, for example where you have non-standard lease clauses, and you need to keep track of aggregates of restrictions, special privileges (eg. club membership) etc. But for the simple accounting needs I would stick with the unit-tenant(account) relationship. The OP wants to automate rent increases. So, a unit has a date of rent increase and a rate. On opening, the program checks the current date for all units, and if it falls beyond the date of the increase, it calculates the new rate, and resets the date of future rent hike. It should not matter whether Mr A. or Mr B. is in the unit, although you can customize the price for the unit for your special customers.

Best,
Jiri

 

[Pat Hartman]

We don't know the country of the poster so we don't know which model might work best

 

[haroon.mansha]

Hi ,
I am from Pakistan . Here each contract is made on 12 month basis , after that rent is increased 10% .
But tenants move during the dear the year and move in date is different for each .
What my idea was to run a query every month , which will counts the months and then add the increment to the ongoing rent.

Thanks
Haroon

 

[ebs17]

Initial situation:
tblRent (PersonID Increment, StartRental Date, StartRent Double/Currency)
As a universally applicable auxiliary table: T999 (I Long PrimaryKey) with sequential numbering from 0 to 999

SELECT
   R.PersonID,
   R.StartRental,
   R.StartRent,
   DateAdd("m", T.I, R.StartRental) AS TerminRent,
   R.StartRent *(1.1 ^(T.I \ 12)) AS ActRent
FROM
   tblRent AS R,
   T999 AS T
WHERE
   DateAdd("m", T.I, R.StartRental) BETWEEN Date()
      AND
   Date() + 1000
ORDER BY
   DateAdd("m", T.I, R.StartRental),
   R.PersonID

The date filter is set to a period between today and today + 1000 days. The calculated rent would have to be rounded if necessary.

 

[arnelgp]

here is a demo.