Hi,
I'm trying to calculate monthly returns from daily returns using geometric mean. I would like to be able to check the date array in column A of the attached document and locate the corresponding return array if the month and the year of the dates in question are equal to the month's returns I'm trying to calculate. My guess is I would need to use an offset function where the reference point changes every month to the first of the month in question and then calculate the height of the offset range based on how many return data points I have for that month.
Not exactly sure how to build this formula out though.
This is the formula I have to replace:
B4:B23 would need to be replaced by the moving offset function for every month in question.
=PRODUCT((1+B4:B23)^(1/n))-1
I have uploaded a sample file here if you want to play around with it: https://file.io/jTEaYH
Essentially, the formulas in E3 and E4 deal with the date filtering fine but they aren't calculating geometric means, it's just a normal sum. I want to incorporate the date filtering within the formula in G3 (=PRODUCT((1+B4:B23)^(1/n))-1).
Also, the hard part is to make it so that the offset function recognizes what is the first day of every month, makes that cell the reference cell, then sees what is the last date of the month in question (even if the current month isn't finished) and then creates the appropriate range of returns between the first and last day of the month in question.
THanks a ton for any help! Or if you can think of any other way of doing this, it would be super appreciated.
I'm trying to calculate monthly returns from daily returns using geometric mean. I would like to be able to check the date array in column A of the attached document and locate the corresponding return array if the month and the year of the dates in question are equal to the month's returns I'm trying to calculate. My guess is I would need to use an offset function where the reference point changes every month to the first of the month in question and then calculate the height of the offset range based on how many return data points I have for that month.
Not exactly sure how to build this formula out though.
This is the formula I have to replace:
B4:B23 would need to be replaced by the moving offset function for every month in question.
=PRODUCT((1+B4:B23)^(1/n))-1
I have uploaded a sample file here if you want to play around with it: https://file.io/jTEaYH
Essentially, the formulas in E3 and E4 deal with the date filtering fine but they aren't calculating geometric means, it's just a normal sum. I want to incorporate the date filtering within the formula in G3 (=PRODUCT((1+B4:B23)^(1/n))-1).
Also, the hard part is to make it so that the offset function recognizes what is the first day of every month, makes that cell the reference cell, then sees what is the last date of the month in question (even if the current month isn't finished) and then creates the appropriate range of returns between the first and last day of the month in question.
THanks a ton for any help! Or if you can think of any other way of doing this, it would be super appreciated.
