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 [email protected] if you have any software recommendations or can help me.