Thread: Calculate current rent

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 dmitri.ivashchenko@gmail.com if you have any software recommendations or can help me.

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.

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.

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

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

Does this formula in U2 copied down work:

=(1 + H2) ^ IF(ISERROR(LOOKUP(2,1/(J2:OFFSET(U2,0,-4)<>""))),DATEDIF(F2,TODAY(), "Y"),1) * IF(ISERROR(LOOKUP(2,1/(J2:OFFSET(U2,0,-4)<>""))),G2,I2)

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.]

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&#37; 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.

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.