If statements and CPI increase

  • G’Day Everyone:


    I am working on a small model that calculates cash flows, within the model the cash flows are broken up by month and after 12 months the cash flows automatically increased by CPI. The cash flows do not always start at the same month. I would like to us an if statement to instruct the cash flow to start in the appropriate month however my problem is getting the CPI calculation to automatically start 12 months after the cash flows starts.


    (ie cash flow 1 starts in month 6 so on month 18 I would like the cash flow to increase by CPI. And cash flow 2 starts month 3 I would like to start the CPI increase on month 15.) These months will change with every example I put into my model.


    Thank you in advance for everyone’s help. Let me know if I need to explain the problem in greater detail.


    Jason

  • The way to do this will depend on how your data are arranged. Can you attach a representative example of what you have? Is the CPI increase a constant percenage independent of which month or year ends the 12 month period?

  • Derk,


    Thanks for the response. Correct, the CPI amount will be independent on when the the 12 months ends and for simplification we can assume CPI will always be the same.


    I have attached a copy of the format. If you have a better format I am still in the early stages of creation.


    There is another details tab that populates the cash flow page. What I want is that this change allowing cash flow 2 to start in month 14 or whatever depending on the if statement and then I don't know if I need another if statement that says CPI starts 12 months after first number etc. Does this help?


    Also with Cash Flow One - this will always be four months but the four months will vary I was wondering if the offset funtion could be used here.


    Thanks again,
    Jason

    Files

    • CashFlows.xls

      (24.58 kB, downloaded 66 times, last: )
  • See the attached. I created a user defined function (UDF) to return the position of the first non-zero value in a range. I then used that function and added enough to it so that a simple If statement determines when the CPI increase kicks in. The shaded area in Column B has these values and they are then referenced in the If statements of the bottom table. CPI is a defined name referring to the amount of the increase. Does this help?

    Files

    • CashFlows.xls

      (76.29 kB, downloaded 58 times, last: )
  • Another question? How do I use this formula if I want the Cash Flow to increase ever 12 months not just after the first 12 month period? Sorry I thought I could figure this aspect out.