Indexation Query

broncos347

Active Member
Joined
Feb 16, 2005
Messages
282
I have a question surrounding indexation which someone can hopefully help me with because I can't get my head around what i need to do.

I have a table which holds an annual index figure, the table has two columns one for the year and one for the indexation rate.

I have another table with a base amount, says £100,000, and this amount has a base year of 2016, the sheet also has another column which shows year of construction, e.g. 2020 (this year could change), what i am trying to do is * the £100,000 to bring it up to a figure in 2020, i can do this by simply *ing the base figure by the indexation rates between 2016 and 2020, for example, £100,000*1*1.023*1.026*1.04*1.025 and this would give me the figure i need, what i can't get my head around is how i can automate this so that if the year of construction changes the £100,000 will be adjusted accordingly.

Any help would be appreciated.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,873
Hi

Try in B5:

=B2*PRODUCT(IF(D2:D16>=B3,IF(D2:D16<=B4,E2:E16)))
This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER

In this example the result is 100,000*1.04*1.052*1.059*1.046*1.036, the factors from 2016 to 2020


Book1
ABCDE
1
2Amount10000020101.028
3Start201620111.048
4End202020121.034
520131.027
6Result125555.713220141.047
720151.049
820161.040
920171.052
1020181.059
1120191.046
1220201.036
1320211.022
1420221.024
1520231.031
1620241.030
Sheet1
 

broncos347

Active Member
Joined
Feb 16, 2005
Messages
282
brilliant works like a dream, i would never have worked that one out, many thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,294
Messages
5,571,379
Members
412,385
Latest member
OChambo94
Top