second chance for Indexation formula problem (truly sorry for first post)

**peer_1988** · 08-22-2014, 05:27 AM

Dear excel guru's,

First of all I'm Truly sorry for my last post. I did not read the forum rules well and therefore wasted your time! I'm very very sorry hopefully I can get a second chance.

Note that my question is cross-posted: http://www.mrexcel.com/forum/excel-q...-tu-delft.html and http://www.worksheet.nl/forumexcel/s...ferrerid=48775

Secondly let me explain why this question isn't homework or an assignment, because my teachers don't know the solution as well and say it isn't possible to model a formula like this. So for science I posted this question. I'm not capable to model this formula and I was hoping you could consult me. If nobody can help me I cannot prove that this formula I'm seeking for is even possible.

So to prove it, I'm looking for some help in the name of science not to help me finding the right research results, because I always can choose simple inflation numbers and arrange them manually in my worksheet. My teachers already said: "just fill in 1, 1.5, 2, 2.5 or 3% inflation for your different scenario's. These percentages are realistic enough for your research results. So don't bother." Still, I do therefore my second post, because I want to convince my architecture department it is possible, just or lack of knowledge makes us believe it isn't. hopefully you can share your knowledge to improve teaching methods.

If you want to help me this was my previous post:

"Currently I’m struggling with a personal master thesis exercise. Which I believe is for more experienced excel practitioners, because many Dutch Excel fora did not respond yet but many have seen the exercise. Also my teacher cannot help me with this excel challenge (because he doesn't know the answer as well). Please see attached the simplified excel worksheet to for the way I think the data should be organized. Hopefully clear enough. Examplesheet automatic indexation alternations.xlsx

I’m trying to calculate a monthly rent increase which depends on a certain (contractual) predetermined indexation rate. Let me further explain.

Tenant X has a lease agreement with a lease length of 15 years. The rent can be increased every time when the total index changed by 3% (3%, 6%, 9%). Each month the index will change with a certain percentage depending on the average annual index change (equal to average annual inflation rate). So the rent may only be increased if the index changed 3% or exceeds 3% in a certain month. The index change can be fully discounted to the rent, only once and rent increase will stay the same after the increase until the next increase, and to a maximum of 10% in 10 years. So after 9% index change only 1% can be discounted to the rent increase if it takes place within those 10 years.

For example for my scenario-analysis I have 2 variable cells, which are:
CEL1: average annual index change (inflation) in year 1 and year 2 (e.g. 1,5%)
CEL2: average annual index change (inflation) in year 3, year 4, etc. up to year 15 (e.g. 2,0%)
Please see attached file for a convenient table example.
brief example inflation scenario.jpg

Suppose I would like to change CEL1 from 1% to 1,5% (like above) and CEL2 from 1,1% to 2,0%.
This would mean that the rent will be increased by 3% in month 25.
Further on, and again, the rent will be increased by 3% in month 43.
For this scenario the rent will be increased for the 3th time by 3% in month 61.
After month 78, in month 79 the last 1% rent increase will be discounted according to the 10% in 10 years contractual agreement
After 10 years it will starts all over again, resulting in a new rent increase for month 139 (halfway through year 12), etc. etc.

Probably you could imagine how much time this will cost me if I have to rearrange all these calculations for more than 10 scenarios with different inflation forecasts, and with peculiar numbers (for example CEL1:1,772% and CEL2:2,337%).

Hopefully your expertise can help me to solve this problem (finding out which function/formula I have to use to generate a model calculating automatically the column rent increase according to CEL1 & 2)

Looking forward to your responses,

Cheers Peter

extended example:
Example sheet.jpg