Inflation formula with varying rates per year!

[ktsmith21]

Hello, I am looking for a formula that properly pulls a Baseline Expense depending on location (this part is done) and then multiplies it by looking up the inflation rate for a given year. Second half of the formula is below.

Inflation portion:
+((1+INDEX(INFLATION!$H$19:$ZZ$41,MATCH(FORECAST!$B6,INFLATION!$B$19:$B$41,0),MATCH(YEAR(FORECAST!AZ$3),INFLATION!$H$16:$ZZ$16,0))/12)^DATEDIF(LOCATIONS!$C$2,AZ$3,"M"))

The formula is incorrect as it is just looking up the current year inflation rate, dividing it by 12 to get a monthly amount, and then raising it to the power equal to the number of months from baseline.

What the formula really needs to do is multiply the Baseline Expense * (1+(period 1 inflation rate/12))*(1+(period 2 inflation rate/12))*(1+(period 3 inflation rate/12))*.... so and so forth until it gets to the current month. My problem is that there are over 500 months in this analysis. Is there a way to write a formula that will add a (1+(period x inflation rate/12)* for each column that the formula is dragged across? Product formula of some sort?

Any help is greatly appreciated!

 

[Dave Patton]

Hopefully the following will help.

A few examples would be useful.

Excel 2010
	B	C	D	E
1	Inflation
2	3%	1-Jan-19	22-May-19	1.010038
3				1.010038
4
3c

Cell Formulas
Range	Formula
E2	=(1+B2/12)^DATEDIF(C2,D2,"m")
E3	=FV(B2/12,DATEDIF(C2,D2,"m"),0,-1)

 

[ktsmith21]

Dave Patton, thank you for looking at this. The issue with the datedif formula is that it is using the same inflation rate for each month. I am trying to create a formula that looks up inflation rates per month from an existing table.

 

[Dave Patton]

A few examples and/or more information is required

FVSchedule may be useful

See Excel's help and

https://www.mrexcel.com/forum/excel...pply-multiple-rates-compound-core-number.html


If you have a table with the relevant factor, a Lookup formula could access that information.

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Period​

[/td][td]

Inflation​

[/td][td][/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

4%​

[/td][td][/td][td]

1,012558​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2​

[/td][td]

3%​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

3​

[/td][td]

3,50%​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

4​

[/td][td]

4,50%​

[/td][td][/td][td][/td][/tr]
[/table]

Formula in D2
=SUMPRODUCT(PRODUCT(1+B2:B5/12))

M.

 

[ktsmith21]

Dave Patton, genius! I never even knew about this function. It worked perfectly. Thank you very much for your help!