#### jgedwardsv

##### New Member

- Joined
- Oct 27, 2016

- Messages
- 31

I am trying to create a model to evaluate participant data and then create a series of charts and tables to correlate that data output to a specific target date mutual fund series.

I have the logic thought out, but am having trouble executing this with powerpivot.

I have 3 tables and a helper age table built. One table contains all the raw data on each target date series with values of equity exposure as the columns (i.e. 50 years to date x%, 45 years to date y% etc etc). A second table contains the participant data calculated in excel (I calculate future account balance and estimate social security payments to derive a total benefit at retirement and then calculate a replacement ratio based on current income and then based on inflated income in the future). My final table contains an array of replacement ratios, grades for each, and corresponding equity allocation.

I am having trouble calculating a participant's age group (in columns in the equity allocation) and corresponding that to a participant's income replacement ratio (the rows in the same table). How can I pull the age group and replacement ratio to find the equity allocation associated with it? I believe this would be an index/match function in excel, but given that this analysis is intended to be run repeatedly with varying sets of participant data (i have linked the table from an external excel workbook to prevent someone from messing with the data model).

Thanks for your help, I'm really stuck here and believe I am close to figuring out how to model this correctly.

John