Calculating returns of a range using Product + Offset functions

[gcefaloni]

Hi,

I'm trying to calculate monthly returns of a stream of returns using an ongoing set of daily returns. So what I was trying to do was something of the sort:

A4:A10000 column = WORKDAY(daily dates)
B4:B10000 column = daily returns %
S2:S61 column = end of month dates (Jan 2019 to Dec 2023)
T2:T61 column = where I would like monthly returns %to populate next to each month in column S

Returns start at row 4; lets say we are trying to calculate Feb 2019 returns
{=PRODUCT(1+OFFSET(return of first day of the month of cell showing '02/28/2019',COUNTIFS('daily_dates',"=<"&'02/28/2019','daily_dates',">"&('02/28/2019'-DAY('02/28/2019')+1)),0,-DAYS('02/28/2019','01/31/2019'))-1)}

Which currently translates into
{=PRODUCT(1+OFFSET(INDEX(A4:B10000,MATCH((S3-DAY(S3)+1),A4:A10000,0),2),COUNTIFS($A4:$A10000,"=<"&S3,$A4:$A10000,">"&(S3-DAY(S3)+1)),0,-NETWORKDAYS(S3-DAY(S3)+1,S3))-1)}

It returns me a "#Ref" output.

So there are a few things I'm not sure about:
1- Using INDEX(A4:B10000,MATCH((S3-DAY(S3)+1),A4:A10000,0),2) give me my initial reference cell that corresponds to the first day of the month that I'm trying to calculate returns for, does that only give me the value instead of the actual cell that the Offset function could use (e.g. return -0.00027 instead of B4.). Currently returns B$4$ in evaluate formula check.
2- The countifs syntax to calculate the height of the range within the offset function using lesser than EOMonth date in S3 and first day of the month showing in S3. When evaluation the formula, it returns 0 instead of whatever the range height should be.
3- Calculating the offset range height using Networkdays. It currently seems to return the correct 20 for Feb 2019.

You guys seeing any solution/modification to what I'm doing in order to achieve the objective of simply calculating geometric monthly returns using daily returns?

Here's my current table:

[TABLE="width: 322"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]A[/TD]
[TD] B[/TD]
[TD="colspan: 2"] S T[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Daily Change %[/TD]
[TD="align: right"]31-Jan-19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31-Jan-19[/TD]
[TD][/TD]
[TD="align: right"]28-Feb-19[/TD]
[TD="align: right"]#Ref![/TD]
[/TR]
[TR]
[TD="align: right"]01-Feb-19[/TD]
[TD="align: right"]-0.027%[/TD]
[TD="align: right"]31-Mar-19[/TD]
[TD="align: right"]#Ref![/TD]
[/TR]
[TR]
[TD="align: right"]04-Feb-19[/TD]
[TD="align: right"]-0.252%[/TD]
[TD="align: right"]Apr-19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Feb-19[/TD]
[TD="align: right"]0.193%[/TD]
[TD="align: right"]May-19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06-Feb-19[/TD]
[TD="align: right"]0.010%[/TD]
[TD="align: right"]Jun-19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07-Feb-19[/TD]
[TD="align: right"]0.261%[/TD]
[TD="align: right"]Jul-19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08-Feb-19[/TD]
[TD="align: right"]-0.020%[/TD]
[TD="align: right"]Aug-19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11-Feb-19[/TD]
[TD="align: right"]-0.079%[/TD]
[TD="align: right"]Sep-19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12-Feb-19[/TD]
[TD="align: right"]-0.061%[/TD]
[TD="align: right"]Oct-19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13-Feb-19[/TD]
[TD="align: right"]-0.012%[/TD]
[TD="align: right"]Nov-19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14-Feb-19[/TD]
[TD="align: right"]0.365%[/TD]
[TD="align: right"]Dec-19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15-Feb-19[/TD]
[TD="align: right"]-0.056%[/TD]
[TD="align: right"]Jan-20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18-Feb-19[/TD]
[TD="align: right"]0.027%[/TD]
[TD="align: right"]Feb-20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19-Feb-19[/TD]
[TD="align: right"]0.059%[/TD]
[TD="align: right"]Mar-20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20-Feb-19[/TD]
[TD="align: right"]-0.033%[/TD]
[TD="align: right"]Apr-20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21-Feb-19[/TD]
[TD="align: right"]-0.170%[/TD]
[TD="align: right"]May-20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22-Feb-19[/TD]
[TD="align: right"]0.224%[/TD]
[TD="align: right"]Jun-20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25-Feb-19[/TD]
[TD="align: right"]0.077%[/TD]
[TD="align: right"]Jul-20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26-Feb-19[/TD]
[TD="align: right"]0.194%[/TD]
[TD="align: right"]Aug-20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27-Feb-19[/TD]
[TD="align: right"]-0.330%[/TD]
[TD="align: right"]Sep-20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28-Feb-19[/TD]
[TD="align: right"]-0.180%[/TD]
[TD="align: right"]Oct-20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Mar-19[/TD]
[TD="align: right"]0.161%[/TD]
[TD="align: right"]Nov-20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]Dec-20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]Jan-21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]Feb-21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]Mar-21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]Apr-21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]May-21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]Jun-21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]Jul-21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]Aug-21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]Sep-21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]Oct-21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]Nov-21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]Dec-21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21-Mar-19[/TD]
[TD][/TD]
[TD="align: right"]Jan-22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Feb-19[/TD]
[TD][/TD]
[TD="align: right"]Feb-22[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[gcefaloni]

Good, no date without value in colB. The Match in the formula should work to find the row for the last day of the month or less...so if the date stops on 10th, it returns that row. Test the Match by itself to see how it works.

I have not downloaded the file so I have idea what is in colE or colG. I did just realize that when you get the match and offset working with your data you can use the GEOMEAN function instead of product and nth root.

When I go from February to March to April, shouldn't I move the reference cell F3 to another cell that represents the beginning of the range of the new month? Also, when you determine the Nth root and the height of the offset array, by doing Match S4 - Match S3 (for Month of March), don't you use a full month's days as N even if there's only 2 days return worth of data in March ?

 

[duggie33]

The Match function is returning the row containing the last day of the month or the nearest value less than that. If your dates end at January 15th, it will show that as the row indicated by matching January 31st, the 1 in the Match function indicates “less than”. The Offset returns a range using a reference cell and the rows found by the Match functions. I have not downloaded your file and cannot at work so I only know what is in columns A, B, S and T.


I suggest that you test the Match functions on your date ranges and see how it works. Then see how to incorporate the Match into and Offset function to get a range of values in column B. Then it is easy…GEOMEAN(OFFSET(reference, rowoffset, column offset, height, width)).


Good luck.

 

[gcefaloni]

The Match function is returning the row containing the last day of the month or the nearest value less than that. If your dates end at January 15th, it will show that as the row indicated by matching January 31st, the 1 in the Match function indicates “less than”. The Offset returns a range using a reference cell and the rows found by the Match functions. I have not downloaded your file and cannot at work so I only know what is in columns A, B, S and T.


I suggest that you test the Match functions on your date ranges and see how it works. Then see how to incorporate the Match into and Offset function to get a range of values in column B. Then it is easy…GEOMEAN(OFFSET(reference, rowoffset, column offset, height, width)).


Good luck.[FONT=&quot][FONT=&quot]
[/FONT][/FONT]

Thanks a lot. I realized way too late what was happening. Your formula worked. It is just that instead of putting a 1 at the column parameter, it needed to be a 0 in order for it to stay in the same column. I think now what it was doing is offset to the column to the right of it which had somewhat similar return which is why I didn't realize earlier why it was outputting wrong results since the output was always so close to the intended answer.

This is the final formula I used:

=SUMPRODUCT(GEOMEAN(1+OFFSET($F$3,MATCH($S2,$A$3:$A$159,1),0,MATCH($S3,$A$3:$A$159,1)-MATCH($S2,$A$3:$A$159,1),1)))^((MATCH($S3,$A$3:$A$159,1)-MATCH($S2,$A$3:$A$159,1)))-1

Thanks a ton Doug.

 

[duggie33]

Great, no problem. I am glad you got it sorted out. I am not sure if the SUMPRODUCT is needed, but it works!

Doug