Help with formulas to perform indexation to rental income on annual, semi-annual etc

All,

Hope you can help me with creating a dynamic formula to calculate indexation on rental income.

I would like to have the possibility to calculate precise rental income depending on different indexation method. Some tenants only have their rent indexed annual, some have it every second year and some third year etc. If indexation everythird year the rental income will be the same year 1 and 2, while rent in year 3 will then be indexed by the inflation the last three years.

I have enclosed a dummy file where I have tried to explain the issue - Ideally I would like to be able to change cell G11 in the attached file and the indexation automatically changes to annual, every 2nd year or third year or even 10 year if the cash flow is long enough.

Appreciate all the help I can get.

Thanks,
Stian

I'm sorry,
Was a punching error. Should be 1000 there as well. Uploading the correct one.

Thanks a lot for the help. I just realized that my model is a bit more complex, I have different tabs as well as the model is quarterly and not annually. I have uploaded a revised file where I have tried to describe what I want to achieve.

Appreciate all the help I can get!

Thanks,
Stian

Thanks a lot, this works perfect!

Another tricky question - some of the tenants has partially index, for instance 50% instead of 100%. Do you have any advise on how to capture this? I have added a column under the tab "Rent Roll" with suggested indexation. See attached excel file.

Thanks a lot!

RKP

Oh, sorry.

See attached, I have tried to describe the issue in more detail.

Hi Thanks,

I encountered some minor problems with the formulas:
- In my model the rent roll might consist of hundreds of tenants and many tenants will have different space so their name will appear on more than one row. This creates some issues as the formulas automatically calculates when going to the first name.

Also, when using the formulas I get N/A on some of the tenants due to their name, but don't understand exactly why.

Any advise on how to resolve this?

Thanks,

Your help will be appreciated when you have the time.

Seems like teh formula works on some of the names, but get errors on other randomly. For instance tenant name "KM" works, but tenant name "AT" does not work. Are you familiar with this issue when using the formulas?

Seems like it has something to do with the VLOOOKUP formula.

Resolved, added a help column where I numbered tenants from 1-xx so the match-formula worked.

Thanks a lot for the help Kvsrinivasamurthy!

Hi Kvsrinivasamurthy,

A quick follow up to this formula - is there a way to sort out if the analysis start date is different from 31.12.xx and to always do the indexation 31.03.xx? I.e if the analysis start date is 30.6.xx the indexation will first take place 31.03.xx the following year.

Ok, I have only updated row 12 under tab" income calc.). As you can see I have the analysis start date 31.03.2018 and thus want the indexation to not take place before 31.03.2019 (cell H12). The formula in row 3 which you created works perfectly well when the analysis start date is 31.12.xx, but not when the start date is something else.

Hi,

Thanks. However, it is not exactly what I needed as I want the indexation to take place only 31.03.xx each year. So If the analysis date is 31.03.xx - that's why I had 1.00 in cell: "E12:G12".
Can you amend so this is taken into account?

Thanks a lot - this works perfectly well!

Quick and good help - I am not allowed to add more reputation unfortunately so I encourage others to add some.

Again, thanks a lot!