Calculating Monthly Returns

Hello Everyone!

Basically, I have a list of dates with respective balances. I am wondering how to get Excel to determine if the end of the month has passed after a certain date and then calculate the return for that month. What is the most efficient way in your opinion?

Here is a small data sample:

27/12/2005 06:23 0.3%
27/12/2005 05:47 -0.6%
29/12/2005 06:53 1.3%
04/01/2006 17:55 -0.1%
09/01/2006 15:35 3.99%
09/01/2006 15:46 2.54%
09/01/2006 17:07 1.8%
12/01/2006 07:12 -2%
12/01/2006 13:37 1.5%
12/01/2006 13:39 0.8%
12/01/2006 13:58 0.01%


Many thanks,

What do you mean with ".. calculate the return for that month." ?

Is this about opening and closing balances ?

Hello WinteE,

Thank you for your query. My apologies if it was unclear.

What I mean by "monthly return" is the sum of (financial) figures for a month.

In the data sample, the monthly return for December 2005 would be 1% and for January 2006: 5.1418% .

What I am trying to find is an excel formula that would consider the dates in the first column, and sum the figures in the second column if they are within the month.

Many thanks,

You can extract the month and year from the date by using MONTH() and YEAR(). Join these two together as MONTH()&YEAR() in a separate column next to the date and value.
Based on the number of the month (1) and year (2006) combined to 12006 you now can use the COUNTIF() function to get the totals by month.

Erik