Formula help

Nesfin1

New member
Joined
Nov 7, 2012
Messages
4
Reaction score
0
Points
0
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:
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Post an example workbook so we can see what the inflation rate table looks like and the core data.
 

Nesfin1

New member
Joined
Nov 7, 2012
Messages
4
Reaction score
0
Points
0
Thanks, attached (only two years of data due to the attachment size restrictions). 5b is meant to be for all costs and benefits, hence I thought that I could choose the country in column K and use that as a point of reference for the inflation rate (if the country in question is not one of the 6 countries, default is US rate). So I'm looking for something along the lines "if country in column K is US then give me US rate, if country in column K is Australia then give me Australia rate etc.". The challenge is that building it into the formulas starting from column AA will make the formula really long, but building it into a separate column will create problems because the rate is different every year. Is my best bet to create a different lookup column for each year with a nested lookup formula for multiple countries or would there be a more integrated approach?
 

Attachments

  • Financial model ExcelForum.xls
    343.5 KB · Views: 13
Top