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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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,119,079
Messages
5,575,996
Members
412,694
Latest member
Deaf1Too
Top