Indexation formula for experts: I'm completely confused?!

Dear excel masters,

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.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

Welcome to the forum. Unfortunately we can not help you on this.

http://www.excelforum.com/faq.php?fa...ework_faq_item

Copied from the link above is the explanation.

."
Homework or Assignments!
In this forum we cannot allow direct help with homework or assignment questions, if we tell you how to do it you will never learn how to do it! We can answer specific questions and point you in the right direction, if you really don't understand your work/assignment then don't be afraid to contact your tutor as he/she will be pleased you came forward for help rather than struggle. One other thing, your tutor(s) may also frequent this and many other forums like it, either to help the community or sourcing training aids for their pupils so be mindful of this when seeking help!"

Peter,

I agree (of coure) with the statement of Fotis1991 in #2.

With your explaination I think it is fair enough to help you.

you get better help if you add the excel file, without confidential information on the forum.

Please also add the desired (expected) result.

Since I no expert in science, (except of course my own science ) so probably I can't help you on that.


P.s.

in a JPG forummembers can't work.

so they have to rebuilt your file, to help you.

Cross-posted at: http://www.mrexcel.com/forum/excel-q...-tu-delft.html

Please Login or Register  to view this content.

**** what a mess.

Peter, read the forumrules, before posting, since this will be not in your advantage.

I advice you(again) to read forum(eatch forum that you post) rules. Sorry but

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).