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]

 

[duggie33]

Are you looking for the average of column B values for a specific month based on the number of workdays in that month?


If yes, you could try something like this… =SUMPRODUCT(--($A$3:$A$349>$S3),--($A$3:$A$349<=$S4),($B$3:$B$349))/NETWORKDAYS($S3+1,$S4)


In my example that I tested column A has the dates, column B has numbers, S3 is 1/31/2019, and S4 is 2/28/2019. You will have to adjust ranges for your data and you should be able to copy the formula down for subsequent months.

Hope that helps,

Doug

 

[gcefaloni]

Are you looking for the average of column B values for a specific month based on the number of workdays in that month?


If yes, you could try something like this…=SUMPRODUCT(--($A$3:$A$349>$S3),--($A$3:$A$349<=$S4),($B$3:$B$349))/NETWORKDAYS($S3+1,$S4)


In my example that I tested column A has the dates, column B has numbers, S3 is 1/31/2019, and S4 is 2/28/2019. You will have to adjust ranges for your data and you should be able to copy the formula down for subsequent months.

Hope that helps,

Doug

Hi Doug,

It needs to be geometric returns so using Product(1+blablabla)-1 is necessary. As for the result of the formula you gave, it seems to be a bit off. The result your formula is outputting is around 0.0096% whereas we should be getting something around 0.19% for the month of February 2019 based on the numbers in the table above. Not sure what is the difference.

 

[duggie33]

I don't know what geometric returns are. Please elaborate...do you have a formula to return a geometric return? I supplied an average for the month. Can you modify the formula to give you the result you want?

 

[gcefaloni]

I don't know what geometric returns are. Please elaborate...do you have a formula to return a geometric return? I supplied an average for the month. Can you modify the formula to give you the result you want?

https://exceljet.net/excel-functions/excel-geomean-function

The most versatile way to do it is an array formula where we compute PRODUCT(1+(daily returns array))-1. This will give you the geometric mean of the returns. In our exemple, I am trying to get the daily returns array through the offset function while filtering for only the month we need.

 

[duggie33]

So the geometric mean (not geometric returns) is determined by calculating the nth root of the product of n numbers. Pretty straightforward.

The equation including OFFSET as you started is something like this... =PRODUCT(OFFSET($A$2,MATCH($S2,$A$2:$A$158,1),1,MATCH($S3,$A$2:$A$158,1)-MATCH($S2,$A$2:$A$158,1),1))^(1/(MATCH($S3,$A$2:$A$158,1)-MATCH($S2,$A$2:$A$158,1)))

It worked perfectly on some test data that I setup. You may have to make adjustment to fit your data.

Doug

 

[gcefaloni]

So the geometric mean (not geometric returns) is determined by calculating the nth root of the product of n numbers. Pretty straightforward.

The equation including OFFSET as you started is something like this... =PRODUCT(OFFSET($A$2,MATCH($S2,$A$2:$A$158,1),1,MATCH($S3,$A$2:$A$158,1)-MATCH($S2,$A$2:$A$158,1),1))^(1/(MATCH($S3,$A$2:$A$158,1)-MATCH($S2,$A$2:$A$158,1)))

It worked perfectly on some test data that I setup. You may have to make adjustment to fit your data.

Doug

Sorry for late response. I was tyring to play around to figure out how your formula worked. I think it works if you have a full month's data but then I realized that using the S3-S2 methodology won't work if the current month isn't completed and I want to know what is the return for the current month if it's not over, which is something that will happen every day of every month except the first day of the month.

 

[duggie33]

Does that mean you have the future dates in colA, but no data in colB?

 

[gcefaloni]

Does that mean you have the future dates in colA, but no data in colB?

Yes, I essentially use a macro that prints the day's date and the corresponding daily return at the end of every day. So the table just keeps growing every day, I would like to keep track of the past month's returns in their own cell but then move on to the next month, check what the first day of that month is (used in reference point of the offset function) and then check what is the last available date for the current month to see what the return is.

I have uploaded a sample file here: https://file.io/jTEaYH

The current formulas in E3 and E4 are incorrect because they don't take geometric mean in effect but they deal with the months properly. I would essentially like to do the same thing using the PRODUCT(1+range)^(1/n)-1 formula in G3. I'm guessing we also need the reference point of the offset function to move to the first day of every month.

 

[duggie33]

Yes, I essentially use a macro that prints the day's date and the corresponding daily return at the end of every day.

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.