Calculating Monthly returns based on Dates

[Brenden23]

I have two columns: Column A with dates, and Column B with daily returns. I
want to know if there's an efficient way to calculate a series of monthly
returns for each calendar month without having to manually select the
appropriate range for each month in the formula.

Essentially, is there a way I can say "Sum the totals in january 2009" and
then pull the formula down, and it will roll over to corresponding months in
each cell. Or some workaround that's as effective.

Any ideas?

 

[Paul C]

a sumproduct formula should work

=SUMPRODUCT(--(MONTH(A1:A16)=1),--(YEAR(A1:A16)=2009),B1:B16)

the --(MONTH(A1:A16)=1) evaluates to a string of True/False (1,0) values so
when you multiply you are left with the sum of column B only where the
month=1 and the year=2009

for copying you can change the 1 and 2009 to reference a heading instead of
a fixed value.

 

[JLatham]

You will have to futz with this just a tad depending on where you put it on
your worksheet.

As written, assumes your dates go from Row1 down to Row 365, also assumes
that the formula for January goes into a cell on row 1 somewhere:
=SUMPRODUCT(--(MONTH(A1:A365)=ROW()),(B1:B365))

Now, the catch here is the =ROW() part. Because I put that formula in a
cell on row 1, ROW() evaluates to 1. And as I drag it down to row 12, it
increments by one for each row/month.

But if you put the formula on some other row, you'll need to change that a
little, by subtracting a value that will result in the first formula coming
up with 1. For example, if I were to put the initial formula at row 44, then
I'd need to change the formula to:
=SUMPRODUCT(--(MONTH(A1:A365)=ROW()-43),(B1:B365))
so ROW() is 44 and 44-43 = 1 and 1 = month number for January.

Hope this helps.

 

[Fred Smith]

It depends what you mean by "returns", ie how your data is stored.

If your daily data is, say, 1.01 (meaning a 1% increase from the previous
day), then you can use the Sumproduct formulas provided. You will need to
subtract 1 from the result to get the monthly return

However, if a 1% increase from the previous day is stored as .01, then you
will have to add 1 to each number before using the Sumproduct formula.

Regards,
Fred

 

[isabelle]

why should the daily returns be summed? Shouldn't we compound it?