Complicated Rent Formula

baleshst

baleshst

Board Regular
Joined
Jan 24, 2010
Messages
131
Hi,

Please suggest a formula for the table below to calculate the rent.

Please note the following, while formulating.
1) Escalation of rent happens after every 2 months. ie 3rd month rent is 15% higher than the previous month.
2) Rent has to be date specific, in other words, if the rent start date is 15th March, then the rent to be charged in the month of March is for 17 days.
3) Rent escalation is date specific, in other words if the rent start date is 19th June, the escalation of rent shall happen after 2 months i.e. 19th Aug, for the month of Aug the rent shall be calculated in the following manner, 1st Aug to 18th Aug as per previous months rent from 19th Aug to 31st Aug with 15% escalation.

Hope this makes sense. I am using excel 2007.

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEFGHIJK
2TenantRent Start DateRent per monthEscalation after everyEscalation %
3ABC01-Jan-2011 5,000.002 months15%
4XYZ19-Mar-2011 5,000.002 months15%
5
6Rent Every Month
7Tenant31-Jan-201128-Feb-201131-Mar-201130-Apr-201131-May-201130-Jun-201131-Jul-201131-Aug-201130-Sep-201131-Oct-2011
8ABC
9XYZ
Sheet1


</body></html>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
baleshst said:
3) Rent escalation is date specific, in other words if the rent start date is 19th June, the escalation of rent shall happen after 2 months i.e. 19th Aug, for the month of Aug the rent shall be calculated in the following manner, 1st Aug to 18th Aug as per previous months rent from 19th Aug to 31st Aug with 15% escalation.

Hope this makes sense.
Click to expand...
I'm not sure if I can answer it anyway but it needs some clarification. On what date would the escalation happen if the rent start date is ..

a) 28 December?
b) 29 December?
c) 30 December?
d) 31 December?

Also, do the answers to any of the above 4 questions change depending on whether the new year is a leap year or not?
 
Upvote 0
Hi,

This is my first post so please bear with me.

I don't quite get the answer. I have a similar problem. Can someone help?

Thanks
 
Upvote 0
Hi,

This is my first post so please bear with me.

I don't quite get the answer. I have a similar problem. Can someone help?

Thanks
 
Upvote 0
A couple of thoughts:

1. Are you prepared to use Visual Basic to create a custom formula - if not then you'll need to break the calculation down into a number of steps and I suspect that (if its possible even) it will result in a horribly complex equation.
2. You need to decide whether you're using calendar months or simply want to divide the year into twelve equal parts - I suspect the first optionr since you want the rent to be date specific. The devil is really in the detail here.
3. Does the frequency remain fixed or could different tenants have different escalation frequencies?

4. Can I work for you? since a rental of 5,000 per month at the outset will be worth 26,751 after only 2 years you should do very well!
 
Upvote 0
Thanks for the quick response.

1. In relation to your post. I haven't worked in VB before but could try.
2. I am not even using calendar months but rather in financial year. Example, the financial year runs from 1 july to 30 june. A lease is signed 10/11/2011. Escalation is on a yearly basis after 12 months from the start of the lease. So I am trying to find a formula to calculate the yearly income by financial year.
3. Different tenants have different escalation frequencies. It could be yearly as quoted in 2 or every year or every 2 years
4. Work for me? Unfortunately we are talking about Indian rupees so doubt that comes to a lot ;)
 
Upvote 0
Not sure this helps, but I don't think you'll find a simple formula to do that calculation. It will either need the creation of a UserDefined Function in VBA which is the best solution if you have many tenants to do this for, or you could try breaking the problem down into its component parts. Neither is straightforward.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Running total Last 12 months per category (excluding Zeros)
Replies
5
Views
335
Fluff
Fluff
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
401
AndyEd
A
K
  • Solved
Formula to find average number of occurrences each day in a given month
Replies
7
Views
610
Eric W
Eric W
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
Chris_010101
  • Solved
Complicated SUMIFS/COUNTIFS Formulas Required
Replies
5
Views
512
etaf
etaf

Forum statistics

Threads
1,215,059
Messages
6,122,913
Members
449,093
Latest member
dbomb1414

Share this page

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