Closed Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    08-22-2014
    Location
    Amsterdam
    MS-Off Ver
    excel 2010
    Posts
    6

    Question 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
    Last edited by peer_1988; 08-22-2014 at 04:10 AM. Reason: example table unclear

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

    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!"
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    08-22-2014
    Location
    Amsterdam
    MS-Off Ver
    excel 2010
    Posts
    6

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

    Thank you for your response, only I believe that this exercise is not homework or an assignment. Let me explain.

    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

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

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

    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.
    Last edited by oeldere; 08-22-2014 at 04:45 AM. Reason: P.s. added
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

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

    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

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

    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.

  7. #7
    Registered User
    Join Date
    08-22-2014
    Location
    Amsterdam
    MS-Off Ver
    excel 2010
    Posts
    6

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

    Great that could be very helpful!!

    I attached the file. First I though that would discourage respondents even more, my apologies.

    In the file I described the expected formula I can't figure out.

    Examplesheet automatic indexation alternations.xlsx

    cheers guys!

    Peter

  8. #8
    Registered User
    Join Date
    08-22-2014
    Location
    Amsterdam
    MS-Off Ver
    excel 2010
    Posts
    6

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

    Huh I do not understand?!

    I just explained why I want to solve this formula. Its not homework or an assignment.. Even my teachers do not understand the exercise, so why do you rate the question as homework or an assignment? Am I not allowed to prove my teachers it is possible?

    bye

  9. #9
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

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

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. indexation
    By Marcin4111 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-16-2014, 07:05 PM
  2. Replies: 1
    Last Post: 10-03-2012, 10:32 PM
  3. [SOLVED] Macro executes but not giving results :confused::confused::confused:
    By Ganesh7299 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2012, 10:49 AM
  4. Formula experts here?
    By ZorroZ in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-30-2005, 04:48 AM
  5. Indexation help needed
    By zoomer in forum Excel General
    Replies: 1
    Last Post: 02-25-2005, 02:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1