Inflation Step

[Gregg Wolin]

I have a 15-year monthly cash flow model that utilizes an inflation factor based on a single assumption (I.e. annual inflation = 3%). I want to give the user the option to input multiple inflation “steps” as follows:

Step 1: month 0 thru month 18: rate =2%
Step 2: month 19 thru month 36: rate =3%
Step 3: month 37 thru month 60: rate= 4%
Step 4: Month 60 thru month "End" = 3%

By entering "End" in the "thru month" field, the rate table populates the remaining months with the indicated rate.

 

[chirayu]

like this?

 

[Gregg Wolin]

like this?

This is perfect, thanks!!!!
Questions: What does the "+9" do in this named formula and how does this deal with the "P" case?


=MATCH(MAX(Sheet1!$E$10:$E$14),Sheet1!$E$10:$E$14,0)+9

 

[chirayu]

match is matching between those 5 rows. so match would only give 1 to 5. Since we are using it as Max Row, we need to do +9 to do row 10,11,12,13,14 etc

 

[chirayu]

the max row doesn't have anything to do with P

it is just used to find the last row - because in your data you had row 1 to 5 but only first 4 populated with percentages

then maxrow is used to create range LookupInput and LookupOutput, which used in the Lookup formula as lookup_vector and result_vector for the percentages