Rolling 12 month return by portfolio using product(if()) array

Hi all,

This has been giving me fits all afternoon, and would love to get a fresh perspective. I am trying to create a rolling 12 month return by portfolio, but can't get the formula to grab 12 exactly months of returns. Any help is greatly appreciated. Sample spreadsheet attached....

Thanks, Brad

Hi Marvin,

Thanks for the reply. Unfortunately you cannot sum returns. To get a return across more than 1 period you have to geometrically link them (1+Jan Return)*(1+Feb return)-1=jan thru feb return. I may approach the pivot table route later on, however, I'd have to get much more acquainted with them.

It won't be elegant but the formula is going to look something like this in cell d34 (i hope)...now if i can only figure out the parentheses

{=PRODUCT(IF((((YEAR(B34)-YEAR(B$21:B$99))*12)+(MONTH(B34)-MONTH(B$21:B$99))<12)*(((YEAR(B34)-YEAR(B$21:B$99))*12)+(MONTH(B34)-MONTH(B$21:B$99))>=0)*(A$21:A$98=A34)*(B$21:B$99<=B34)=1,(1+C$21:C$98),FALSE))-1}

Brad

Think this is it...

=PRODUCT(IF((((YEAR(B33)-YEAR(B$21:B$99))*12)+(MONTH(B33)-MONTH(B$21:B$99))<12)*(((YEAR(B33)-YEAR(B$21:B$99))*12)+(MONTH(B33)-MONTH(B$21:B$99))>=0)*(A$21:A$99=A33)*(B$21:B$99<=B33)=1,(1+C$21:C$99),FALSE))-1

ctrl-shft-enter