I'm building an investment appraisal model and have lines that look up benefits and costs
IF($G12="",0,IF(AND($H12="ongoing benefit",AA$8>=$G12,($AA$8,$I12),AA$8<$FE12),$FF12,0)+IF(AND($H12<>"ongoing benefit",AA$8>=$G12,AA$8<=$FE12),$FF12,0))
G12= start month
H12= ongoing benefit/costs or length in months
AA8= month on the top of the column
I12= any delay in months
FE= formula that calculates the end month
FF= formula that calculates the monthly amount
Now I would like to introduce inflation to this formula. Normally I would just add from year 1 [above formula]*(1+[rate] and ^2 on year 2 etc. but in this instance I do not have a single inflation rate but a table with 6 different countries and a projected different rate for each year. I can set up a column to choose a country but I'm not sure how to build a look up from the table to the formula, or if I should build a separate line after each cost/benefit line just look up the inflation rate. I want to keep it simple but I'm lacking ideas.. any help would be appreciated :confused2:
IF($G12="",0,IF(AND($H12="ongoing benefit",AA$8>=$G12,($AA$8,$I12),AA$8<$FE12),$FF12,0)+IF(AND($H12<>"ongoing benefit",AA$8>=$G12,AA$8<=$FE12),$FF12,0))
G12= start month
H12= ongoing benefit/costs or length in months
AA8= month on the top of the column
I12= any delay in months
FE= formula that calculates the end month
FF= formula that calculates the monthly amount
Now I would like to introduce inflation to this formula. Normally I would just add from year 1 [above formula]*(1+[rate] and ^2 on year 2 etc. but in this instance I do not have a single inflation rate but a table with 6 different countries and a projected different rate for each year. I can set up a column to choose a country but I'm not sure how to build a look up from the table to the formula, or if I should build a separate line after each cost/benefit line just look up the inflation rate. I want to keep it simple but I'm lacking ideas.. any help would be appreciated :confused2: