Calculating Trailing Returns as New Data Populates

[James02]

Hi,

I have no idea if I linked this correctly or not. But I'm trying!

C:\Users\jkaelin\Dropbox\2015-04-07 Trailing Cumulative Returns.xlsx


QUESTION:
I'm trying to automate the trailing returns. As new data gets populated, I'm trying find a formula that will provide me the most recently 1-mo, 6-mo, 1-yr, 2-yr trailing returns. Note: The highlighted yellow cells starting in column E125 is what I'm referring to.

Right now, i adjust the formula each month. Any ideas?

Thank you!
James

 

[gaz_chops]

As i've done them here are the others........

[TABLE="width: 630"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Model Cap. Weighted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Since Inception:[/TD]
[TD][/TD]
[TD="align: right"]200.1%[/TD]
[TD]cum[/TD]
[TD][/TD]
[TD="colspan: 4"]=PRODUCT(OFFSET(D1,8,,114,))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.75[/TD]
[TD]yrs[/TD]
[TD][/TD]
[TD="colspan: 4"]=PRODUCT(OFFSET(A103,-2,,1))/12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.13[/TD]
[TD="colspan: 2"]inverse of yrs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9.4%[/TD]
[TD="colspan: 2"]annualized return[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2 year trailing[/TD]
[TD][/TD]
[TD="align: right"]149.0%[/TD]
[TD]cum[/TD]
[TD][/TD]
[TD="colspan: 4"]=PRODUCT(OFFSET(D103,-25,,24))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.00[/TD]
[TD]yrs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.50[/TD]
[TD="colspan: 2"]inverse of yrs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22.1%[/TD]
[TD="colspan: 2"]annualized return[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1 year trailing[/TD]
[TD][/TD]
[TD="align: right"]120.8%[/TD]
[TD]cum[/TD]
[TD][/TD]
[TD="colspan: 4"]=PRODUCT(OFFSET(D103,-13,,12))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.00[/TD]
[TD]yrs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.00[/TD]
[TD="colspan: 2"]inverse of yrs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20.8%[/TD]
[TD="colspan: 2"]annualized return[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6 month trailing[/TD]
[TD][/TD]
[TD="align: right"]115.6%[/TD]
[TD]cum[/TD]
[TD][/TD]
[TD="colspan: 4"]=PRODUCT(OFFSET(D103,-7,,6))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15.6%[/TD]
[TD="colspan: 2"]6 month return[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1 month trailing[/TD]
[TD][/TD]
[TD="align: right"]-1.22[/TD]
[TD="colspan: 2"]1 month return[/TD]
[TD="colspan: 4"]=PRODUCT(OFFSET(C103,-2,,1))[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James02]

Gaz - Sorry for the delayed response. I got swamped at work. This works great. Thank you! I appreciate the time and effort you put into helping me out here.

 

[gaz_chops]

You're welcome.