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

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.