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

I am not sure to understand, can you please give me an example of what I should input in E1 to get the monthly return for Dec-07... The COUNTIF() returns that there is too many arguments

I have included a screenshot.

Many thanks,

Type in cell E1 =COUNTIF(C:C,C1,B:B)

Sorry but it returns the "You've entered too many arguments for this function" error message

Same thing with COUNTIFS()

In what country do you live, American or European ? When you are an European citizen you need to replace , by ; in the functions.

I live in the UK, and I always use commas "," in functions. I tried with ";" as well and it is actually worse

I use Excel 2007, do you think this could be the reason?

Do you think you could zip me your excel sheet and upload it on the forums? that would be very nice!

I worked out a little example.

MAGIC!

Thank you sooo much! This is beautiful.

You're welcome.

Now I am having troubles with calculating daily returns

I use the same method, and i add a daily value using the day (DAY) function.

However, a few days in the year return the same value, example:

DATE DATE_ID
21/02/2007 2122007
02/12/2007 2122007

Do you have any idea how to solve the issue?

i see!

thank you!

I have been battling with this for almost a while.Followed the thread,it was helpful in giving me the understanding but did not really help..I was working with daily data for 10 years(2000 to 2010)for my thesis.and I had to convert it to monthly average returns for the FTSE 350.
I finally found the miracle..PIVOT tables.Wow, if you can understand this aspect of excel you are a winner.