Calculating monthly returns

gcefaloni

Board Regular
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.

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
 1000000 Monthly Returns Date Daily Change % Jan-19 31-Jan-19 Feb-19 0.19118% 0.188429% 01-Feb-19 -0.027% Mar-19 0.39181% 04-Feb-19 -0.252% Apr-19 05-Feb-19 0.193% May-19 0.00000% 06-Feb-19 0.010% Jun-19 07-Feb-19 0.261% Jul-19 08-Feb-19 -0.020% Aug-19 11-Feb-19 -0.079% Sep-19 12-Feb-19 -0.061% Oct-19 13-Feb-19 -0.012% Nov-19 14-Feb-19 0.365% Dec-19 15-Feb-19 -0.056% Jan-20 18-Feb-19 0.027% Feb-20 19-Feb-19 0.059% Mar-20 20-Feb-19 -0.033% Apr-20 21-Feb-19 -0.170% May-20 22-Feb-19 0.224% Jun-20 25-Feb-19 0.077% Jul-20 26-Feb-19 0.194% Aug-20 27-Feb-19 -0.330% Sep-20 28-Feb-19 -0.180% Oct-20 01-Mar-19 0.161% 04-Mar-19 0.231%

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

This is what the table looks like in the file in case you don't want to download it.

Replies
6
Views
184
Replies
7
Views
133
Replies
12
Views
310
Replies
1
Views
75
Replies
1
Views
335

Forum statistics

1,203,728
Messages
6,056,997
Members
444,902
Latest member
ExerciseInFutility

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back