Brain teaser: (ratchet) indexation exercise for TU Delft

[peer_1988]

Dear excel masters,

Currently I’m struggling with a personal master thesis exercise, which I believe is for more experienced excel practitioners. Also my teacher cannot help me with this excel challenge. I can e-mail my simplified excel example sheet to keep the exercise organized and hopefully clear enough.

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.
[TABLE="width: 1129"]
<colgroup><col><col><col><col span="14"></colgroup><tbody>[TR]
[TD="colspan: 5"][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Year[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]%[/TD]
[TD]1,5%[/TD]
[TD]1,50%[/TD]
[TD]2,00%[/TD]
[TD]2,00%[/TD]
[TD]2,00%[/TD]
[TD]2,00%[/TD]
[TD]2,00%[/TD]
[TD]2,00%[/TD]
[TD]2,00%[/TD]
[TD]2,00%[/TD]
[TD]2,00%[/TD]
[TD]2,00%[/TD]
[TD]2,00%[/TD]
[TD]2,00%[/TD]
[TD]2,00%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tenant X[/TD]
[TD="colspan: 16"]Every time the total index has been increased by 3% or a multiple of 3% the rent can be increased fully by this index change up to a maximum of 10% in 10 years.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]3%[/TD]
[TD]3%[/TD]
[TD]0%[/TD]
[TD]3%[/TD]
[TD]1%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]3%[/TD]
[TD]0%[/TD]
[TD]3%[/TD]
[TD]3%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]in month[/TD]
[TD][/TD]
[TD][/TD]
[TD]1ste[/TD]
[TD]7th[/TD]
[TD][/TD]
[TD]1st[/TD]
[TD]7th[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7th[/TD]
[TD][/TD]
[TD]1st[/TD]
[TD]7th[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 996"]
<colgroup><col><col><col><col><col span="9"><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Rent[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]INDEX[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Increase [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NR[/TD]
[TD]Month[/TD]
[TD]annual %[/TD]
[TD]month %[/TD]
[TD]cum %[/TD]
[TD] [/TD]
[TD]%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] 1/12[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]0,13%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] 1/6[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]0,25%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] 1/4[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]0,38%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] 1/3[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]0,50%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] 5/12[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]0,63%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] 1/2[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]0,75%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] 7/12[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]0,88%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD] 2/3[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]1,00%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD] 3/4[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]1,13%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD] 5/6[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]1,25%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD] 11/12[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]1,38%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1 [/TD]
[TD]1,50%[/TD]
[TD]0,13%[/TD]
[TD]1,50%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1 1/12[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]1,63%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]1 1/6[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]1,75%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1 1/4[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]1,88%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]1 1/3[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]2,00%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]1 5/12[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]2,13%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]1 1/2[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]2,25%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]1 7/12[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]2,38%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]1 2/3[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]2,50%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]1 3/4[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]2,63%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]1 5/6[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]2,75%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]1 11/12[/TD]
[TD] [/TD]
[TD]0,13%[/TD]
[TD]2,88%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]2 [/TD]
[TD]1,50%[/TD]
[TD]0,13%[/TD]
[TD]3,00%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]2 1/12[/TD]
[TD] [/TD]
[TD]0,17%[/TD]
[TD]3,17%[/TD]
[TD][/TD]
[TD]3,00%[/TD]
[TD][/TD]
[TD="colspan: 6"]in month 25 for the first time 3% rent increase will be charged[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]2 1/6[/TD]
[TD] [/TD]
[TD]0,17%[/TD]
[TD]3,33%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]2 1/4[/TD]
[TD] [/TD]
[TD]0,17%[/TD]
[TD]3,50%[/TD]
[TD] [/TD]
[TD]0,00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[peer_1988]

Let me explain why this isn't homework or an assignment, but you can help me convincing my teachers it is possible to model this formula:

When you writing a thesis your teachers will only put you in the right direction. They don't know the answers as well. Like this excel formula. I though I could consult this forum because experts can contribute in science by sharing knowledge. Of course I understand the rules of conduct. But If nobody can help me I cannot prove that this formula I'm seeking for is even possible. My teachers say it isn't 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. It's just for personal interest, because 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."

May I rewrite the exercise so everyone knows for what purpose it is? So experts can contribute to prove my teachers it is possible?

cheers

Peter

 

[peer_1988]

Please understand this isn't homework because my teachers don't know the solution and say it isn't possible..

I just explained why I want to solve this formula. So why does this post get cross-posted and rated as homework or an assignment? Am I not allowed to prove my teachers it is possible?

 

[peer_1988]

I cross-posted this question (with explanation why this question isn't homework or an assignment) at; second chance for Indexation forumula problem (truly sorry for first post) and http://www.worksheet.nl/forumexcel/s...ferrerid=48775

cheers