Forum Discussion
Calculate performance based on proportional holdings
Hi,
I am trying to calculate monthly performance for a group of clients where each client holds a different proportion of a selection of companies.
I have the following datasets:
Client %age weighting in each company
Company %age return at each month-end date
I am trying to come up with an array formula that will allow me to calculate, for each client at each month-end date, their performance for that month, and ultimately end up with a table of data for each client's monthly performance that can be added to as new monthly data becomes available.
The long-winded way would be to calculate, for each Client (A, B, C etc.), the sum of their proportion of each company (X, Y, Z etc.) times by the monthly return of that company, but given that dates are involved it would mean manipulating the formula too much for it to be practical long term.
I am sure that this can be achieved with some sort of SUMPRODUCT and INDEX/MATCH function but I am struggling to work out the combination and would welcome any help if it is available.
Thanks,
Thanks. In B2:
=SUMPRODUCT(INDEX('Company Performance'!$B$2:$Z$100,0,MATCH(B$1,'Company Performance'!$B$1:$Z$1,0)),INDEX('Client Holding Size'!$B$2:$Y$100,0,MATCH($A2,'Client Holding Size'!$B$1:$Y$1,0)))
You can modify the ranges, but the last row (100 in the above formula) should be the same in 'Company Performance'!$B$2:$Z$100 and 'Client Holding Size'!$B$2:$Y$100.
Fill down.
6 Replies
Please provide a sample workbook.
Hi Hans,
https://1drv.ms/x/s!AtMRQbHIJJWxuB6bSmUcK0Hc5G9d
Can you try that and see if it works? I've left in the long-winded formulae on the first tab, but I'm sure there's a better way of doing it.
Also there are likely to be hundreds more clients and many more companies than just the 3 I have used in the sample spreadsheet. I need to it be able to be added to and grown without making it more difficult to manage.
Thanks,
Thanks. I have downloaded the workbook, but I don't have the slightest idea what it has to do with your question. The worksheet doesn't contain any formulas.
