Calculating Returns off Data Table

[bballman178]

Hello,
I have a data table of gross and net time-weighted returns for each quarter from 6/30/2014 to 3/31/2008. I am trying to set up a matrix that would pull the return figures from the table and calculate time-weighted returns over specific periods based on the starting quarter. I want to have the following return periods: Quarter, YTD, 1-Year (annualized), 2-Year (annualized), 3-Year (annualized), 4-Year (annualized), 5-Year, and Since Inception (annualized). For example, in the matrix, one date would be 9/30/2013, and would further be split into gross and net; what would be the formula to use the data from the data table to calculate the returns for the aforementioned periods based on that 9/30/2013 specific start date? I've attached what I'm working with. Thanks

 

[Hui]

Bballman178

I have used a few Averageifs() functions and some careful use of Eomonth() and Named Formula to simplify this

See attached:

 

[bballman178]

Thanks Hui, this is great. The only problem is that I'm trying to find the compounded annualized return figures. So for example, if I'm looking at cell J40, the formula would be:
=PRODUCT(1+B11:B18)^(4/COUNT(B11:B18))-1

I just can't figure out how to automatize the spreadsheet

 

[Hui]

Try this
I have put the formulas for J40 at the top for your review
Please check these to ensure your happy
especially the YTD Numbers

 

[bballman178]

Thanks Hui. Is this the correct spreadsheet?

 

[Hui]

Whoops, Here it is:

Also changed in the previous post

 

[bballman178]

Thank you greatly for your help Hui. Much appreciated!