Calculate current rent

[[email protected]]

So I posted about 3 months ago, and didn't get even a single idea from anybody. Maybe someone will help. I have attached a sample of the Excel I am working with. The sensitive information has been changed but the information is the exact stuff that I am working with. Please take a look and see if you can help.

My ultimate question is:

Is it possible to have my lease rents be automatically adjusted on the anniversary of their commencement date using the current system date? Maybe, this would work using the excel TODAY function. Excel would need to compare the current date with the commencement date and calculate how many years have since passed. It would need to use a compound interest formula to calculate what the present rate is.

In the document attached, you will find (lease commenced, original rent, escalation rate and present rent. There are also ammendments which follow the present rent which show how rent has increased due to new lease agreements or amendments. The most recent rent to use is present rent, but it would be great if excel could show how the rent increased or changed since the lease start.

The document is the following: Basically, these are multi-year property rentals which on the anniversary of the contract signing increase by 4% or 5% annually (or whatever the escalation is). That means, if I rent it out on March 1st 2007 for 1000$, it will be $1050 from March 1st 2008 and then 5% increase on every anniversary. It works just like compound interest over given years.



What I want to know is it possible to have Excel update my spreadsheet depending on the date automatically. Is there any kind of Macro program? I have over 600 rents, that is why I want the process automated. I would want the rents to increase on the anniversary of the contract signing every year and so basically the Excel would update itself, or it would update once I turn it on and then refresh it.

Any ideas?

Next question I have is, say I scroll my mouse over the rent column. I look at building A and look at the rent for this year. Is it possible that once I pop my mouse over it a small chart can pop up and describe the rents for the previous years. Meaning a chart that would quickly pop up and show the 5% decrease every year for the past say 5 years.

ALL ADVICE APPRECIATED!

E-mail me directly at [email protected] if you have any software recommendations or can help me.

[NBVC]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[shg]

You're more likely to get help if you explain the structure of your workbook (what's an amendment? what's its relevance?), and show an example of what you're looking for. People are willing to help, but asking 20 questions is not part of the deal.

[[email protected]]

Sorry if I made it unclear. The amendments only relevance is to explain what changed in the lease. As these are multi-year leases, sometimes things change and tenants require more space or other services. These changes cause increases or legal document changes, and are made as part of amendments. They are not very relevant to this problem.

The only thing I really need is to have Excel calculate the present rent automatically. Meaning, that a refresh would allow it to use today's date and figure out if the rent escalated since the lease started. I don't really have ideas on how to present this, and that is why it is explained so vaguely.

[shg]

In U2 and copy down, =(1 + H2) ^ DATEDIF(F2,TODAY(), "Y") * G2

[[email protected]]

Hey,

thanks for the idea. I am trying to understand what you are doing. In the first example, the rent is calculated correctly. Unfortunately, this won't work for everything. I can't use the original rent for the calculations, because sometimes the amendments change the rent. So I would need all calculations to be based off of the "Present rent" and any escalation would go from this number. I noticed your formula uses the Original rent to base itself off of.

So in the first example, this is ok because there are no amendments so the escalation is a basic compound interest. However, in the second example, I would need excel to calculate the Date function from the present rent and force it to escalate every 365 days. Does this make sense?

[shg]

Then you need a column to contain the most recent rent baseline and as-of date, and then adapt the same formula.

[deadlyduck]

Does this formula in U2 copied down work:

Please Login or Register  to view this content.

It requires that there are always 3 columns prior to the cell where the calculation is performed for Terms/renewal/Expiration. It also assumes that if ANY amendment has been entered in a row that you use the present rent for the purpose of escalating to the new rent.

[EDIT- I MODIFIED THE ABOVE FORMULA SINCE I FIRST POSTED IT. THE ABOVE IS THE EDITED EFFORT.]

[[email protected]]

Ok so this a big step. This formula is a great start so thank you for your time. I REALLY appreciate it.

However, just using it on some other parts of the Excel, why does it give me a date as an answer instead of a number? Can you possibly explain a litlte how this formula works. I cannot seem to understand it.

I can send you a larger chunk of the data I am working with if you would like to make it easier.

[deadlyduck]

Here's the explanation of what the formula after (1+H2) is doing:

IF(ISERROR(LOOKUP(2,1/(J2:OFFSET(U2,0,-4)<>""))),DATEDIF(F2,TODAY(), "Y"),1)

This formula is used to determine whether you need to escalate from date of the original creation of the lease or just 1 year- note that I'm working on the basis that there are ANNUAL rent escalations so if you're not computing from the original date, you're simply increasing the most recent rate up by the annual escalation amount eg add 4% or whatever to the current rent amount.
The formula checks to see if there is any text in the amendment or details cells ("Amendment 1" "Details" etc). It does this by using the LOOKUP function- this is checking in a range which starts in cell J2 and goes to a cell 4 to the left [the OFFSET(U2,0,-4) part] from where the actual formula is entered eg if the formula is in cell U2 it looks in cells J2 to Q2 for some text. That's why it's important that there are always 3 columns prior to the cell where the calculation is performed to hold data for "Terms/renewal/Expiration".

If it doesn't find any text, it then knows to calculate the length of time from the original date of the lease to today [the DATEDIF(F2,TODAY() part]. Otherwise, it assumes that the time period is 1 year - that's what the ",1)" part of the formula is doing.

Here's what the next part is doing (the part after the foregoing formula):

* IF(ISERROR(LOOKUP(2,1/(J2:OFFSET(U2,0,-4)<>""))),G2,I2)

This is kind of similar to the previous part. This formula is figuring out whether to use the original rent amount or the most recent rent amount. It performs a test to see of there is any text in the cells for "Amendment" or "Details". If it finds nothing, it knows to use the original rent amount [G2 value] , otherwise it uses the most recent rent amount [I2 value].


If the formula is returning a date in a particular cell, make sure that the Formatting of that cell is set to either 'General' or 'Currency' rather than 'Date'. Excel stores dates as numbers so if you enter say 2500 in a cell formatted as 'Date' it'll be displayed as Nov 5 1910, which is 2500 days since Jan 1 1900.

[rahulbhanwar]

Hi All,

I am new for this forum & i need the same but with day by day escalation. as in attached format. I just want to map the details and total rent will come out in column. "B11".. please help me..

[NBVC]

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[rahulbhanwar]

Sorry.... i am new for this forum........