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.
<colgroup><col><col><col><col span="14"></colgroup><tbody>
</tbody>
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:
<colgroup><col><col><col><col><col span="9"><col></colgroup><tbody>
</tbody>
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.
| Year | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | |
| % | 1,5% | 1,50% | 2,00% | 2,00% | 2,00% | 2,00% | 2,00% | 2,00% | 2,00% | 2,00% | 2,00% | 2,00% | 2,00% | 2,00% | 2,00% | |
| Tenant X | 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. | |||||||||||||||
| 0% | 0% | 3% | 3% | 0% | 3% | 1% | 0% | 0% | 0% | 0% | 3% | 0% | 3% | 3% | ||
| in month | 1ste | 7th | 1st | 7th | 7th | 1st | 7th |
<colgroup><col><col><col><col span="14"></colgroup><tbody>
</tbody>
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:
| Rent | |||||||||||||
| INDEX | Increase | ||||||||||||
| NR | Month | annual % | month % | cum % | % | ||||||||
| 1 | 1/12 | 0,13% | 0,13% | 0,00% | |||||||||
| 2 | 1/6 | 0,13% | 0,25% | 0,00% | |||||||||
| 3 | 1/4 | 0,13% | 0,38% | 0,00% | |||||||||
| 4 | 1/3 | 0,13% | 0,50% | 0,00% | |||||||||
| 5 | 5/12 | 0,13% | 0,63% | 0,00% | |||||||||
| 6 | 1/2 | 0,13% | 0,75% | 0,00% | |||||||||
| 7 | 7/12 | 0,13% | 0,88% | 0,00% | |||||||||
| 8 | 2/3 | 0,13% | 1,00% | 0,00% | |||||||||
| 9 | 3/4 | 0,13% | 1,13% | 0,00% | |||||||||
| 10 | 5/6 | 0,13% | 1,25% | 0,00% | |||||||||
| 11 | 11/12 | 0,13% | 1,38% | 0,00% | |||||||||
| 12 | 1 | 1,50% | 0,13% | 1,50% | 0,00% | ||||||||
| 13 | 1 1/12 | 0,13% | 1,63% | 0,00% | |||||||||
| 14 | 1 1/6 | 0,13% | 1,75% | 0,00% | |||||||||
| 15 | 1 1/4 | 0,13% | 1,88% | 0,00% | |||||||||
| 16 | 1 1/3 | 0,13% | 2,00% | 0,00% | |||||||||
| 17 | 1 5/12 | 0,13% | 2,13% | 0,00% | |||||||||
| 18 | 1 1/2 | 0,13% | 2,25% | 0,00% | |||||||||
| 19 | 1 7/12 | 0,13% | 2,38% | 0,00% | |||||||||
| 20 | 1 2/3 | 0,13% | 2,50% | 0,00% | |||||||||
| 21 | 1 3/4 | 0,13% | 2,63% | 0,00% | |||||||||
| 22 | 1 5/6 | 0,13% | 2,75% | 0,00% | |||||||||
| 23 | 1 11/12 | 0,13% | 2,88% | 0,00% | |||||||||
| 24 | 2 | 1,50% | 0,13% | 3,00% | 0,00% | ||||||||
| 25 | 2 1/12 | 0,17% | 3,17% | 3,00% | in month 25 for the first time 3% rent increase will be charged | ||||||||
| 26 | 2 1/6 | 0,17% | 3,33% | 0,00% | |||||||||
| 27 | 2 1/4 | 0,17% | 3,50% | 0,00% |
<colgroup><col><col><col><col><col span="9"><col></colgroup><tbody>
</tbody>
