Search


Subscribe to AFM


Subscribe to AllFinancialMatters
by Email

All Financial Matters

Promote Your Page Too

The American's Creed

Site Sponsors

Books I Recommend


AFM in the Media


Money Magazine May 2008

Real Simple March 2008

Blogroll (Daily Reads)

« | Main | »

Fun Math: How to Calculate Returns Using Monthly Data

By JLP | October 1, 2007

Do you know how to calculate the year-to-date return on the S&P 500 Index using monthly returns?

No?

Well, it’s easy and fun and I’ll show you how. There may be another way to do this but this is the way I know how to do it.

First off, we need the monthly returns for the S&P 500 Index for 2007, which can be found here (download from the Standard and Poor’s website). To make things easy, I summarized the information in this graphic:

Next, we need to convert each month’s return to a decimal and add 1 to each one to get the factor (I believe that’s the word I’m looking for). So, it looks like this:

Finally, to get the year-to-date return of the S&P 500 Index, you simply find the product for the entire set of factors like this:

1.0151 × .9804 × 1.10112 × … = 1.0913

This step is very easy to do if you use Excel. Simply use the Product function. Or, if you are interested, you can download the simple spreadsheet I put together for this example.

Anyway, to get the YTD return subtract 1 from 1.0913 and multiply by 100 to get 9.13%, which is the exact number that S&P has on their website:

Pretty cool, isn’t it? Just make sure you have the date entered correctly. I used the returns through September.

On last thing: sometimes your numbers may not match up exactly. This is most likely due to rounding.

Now, isn’t math FUN?

Topics: Financial Math Basics, Investing | 23 Comments »


23 Responses to “Fun Math: How to Calculate Returns Using Monthly Data”

  1. Tyler Says:
    October 2nd, 2007 at 7:42 am

    This is why they put up these #’s for us. Saves me time so I don’t have to mess with functions in Excel.

  2. Brad Says:
    October 2nd, 2007 at 9:09 am

    Still, Tyler, this is a good skill to have if you’re active in your financial business. The desired number is not always posted and it would be good to be able to find it yourself.

  3. Analyst Says:
    October 2nd, 2007 at 10:41 am

    That’s what I did until I discovered =geomean(), which does the same thing.

  4. JLP Says:
    October 2nd, 2007 at 11:52 am

    Analyst,

    Although geomean works, I don’t see how it simplifies things.

  5. Customers Revenge Says:
    October 2nd, 2007 at 1:54 pm

    Or you can divide value of index on Sept 30 by value of index on Jan 1.

  6. JLP Says:
    October 2nd, 2007 at 1:58 pm

    Customers Revenge,

    True, but that won’t give you the total return because it won’t include dividends.

  7. Food For Thought - Articles From My Blogroll Says:
    October 4th, 2007 at 5:31 am

    [...] All Financial Matters has an interesting article about calculating the S&P 500 year-to-date return. [...]

  8. Free Money Finance Says:
    October 5th, 2007 at 5:20 am

    Star Money Articles for the Week of October 1

    Here are some recent interesting posts from the MoneyBlogNetwork and beyond: AllFinancialMatters has fun with math. MightyBargainHunter Five Cent Nickel says Zecco has modified its commission structure. Blueprint for Financial Prosperity discusses the …

  9. Weekly Dividend Investing Roundup - October 5, 2007 Says:
    October 5th, 2007 at 1:35 pm

    [...] 2. JLP provided us with a good post on how to calculate year-to-date returns using monthly data. You may ask yourself why this is important if you can just go to the site to get it – my response is that the key to not getting bamboozled in the investing marketing world is to understand where the numbers they tout come from! [...]

  10. GrowYourFunds Says:
    October 5th, 2007 at 6:34 pm

    Around the blogging world

    On this Friday evening I wanted to take some time to provide the readers here at GrowYourFunds with a nice roundup of some recent interesting posts from around the investing and personal finance blogging communities. Corey at Afraidtotrade talks a…

  11. Fun Math: How to Calculate Returns Using Monthly Data - Mathematics Lectures Says:
    November 24th, 2007 at 1:57 pm

    [...] admin: [...]

  12. chris Says:
    May 19th, 2008 at 10:09 am

    hey thanks!

  13. jatin Says:
    July 30th, 2008 at 3:49 am

    I use another way to calculate YTD. It makes more sense to me.. See if this helps others also.
    Let the index value ( amount) on 1 Jan be 100..
    Now, add 1.51% to 100 , so closing = 101.51…
    This way caculate for Feb till Sep as below:-

    Month Amt.(Op.) Amt(closing.)
    Jan 100 1.51% 101.51
    Feb 101.51 -1.96% 99.520404
    Mar 99.520404 1.12% 100.6350325
    Apr 100.6350325 4.43% 105.0931645
    May 105.0931645 3.49% 108.7609159
    Jun 108.7609159 -1.66% 106.9554847
    Jul 106.9554847 -3.10% 103.6398647
    Aug 103.6398647 1.50% 105.1944626
    Sep 105.1944626 3.74% 109.1287355

    YTD= (closing-opening )/ opening*100
    = (109.128-100)/100*100
    = 9.128

  14. jatin Says:
    July 30th, 2008 at 3:54 am

    Jatin’s clarification for
    July 30th, 2008 at 3:49 am

    god… I cant paste HTML format here…
    nobody can understand what i wrote on July 30th, 2008 at 3:49 am here.

    all i have done is
    Jan:- 100…..*1.51 = 101.51
    Feb:- 100.51..*-(1.96%) = 99.5204

    and so on upto Sep

    .Anyways.. if someone really wants it in excel, then mail me

  15. jatin Says:
    July 30th, 2008 at 3:58 am

    clarification for jatin Says:
    July 30th, 2008 at 3:49 am

    since HTML formatiting is not here , so my earlier calculation looks horrible and senseless.

    i will try to explain.. all i have done is this…

    for Jan , 100*1.51= 101.51
    for Feb ,101.51*-1.96= 99.5204

    and go on till sep

    Sep closing is 109.128

    YTD= (closing-opening )/ opening*100
    = (109.128-100)/100*100
    = 9.128

  16. dan russo Says:
    October 6th, 2008 at 11:03 am

    hi could you please calculated for me the s&p 500 for the las 15 years on your worksheet.say from 1/1/1993 to 9/30/2008. thanks very much great worksheet.

  17. Leland Holliday Says:
    November 10th, 2008 at 1:12 pm

    I used your method on each of the funds in my portfolio for YTD 2008 (thru 10/31/08). The results I got exactly matched the YTD values published in WSJ and on the Fidelity and Vanguard websites. That surprised me, since I had made major transfers during June (greatly reducing the amounts in stock-based funds). Thus, I expected my personal rate of return in those funds to be different from the published returns. Am I doing something wrong, or is the method wrong for my situation?

  18. Question From a Reader - Am I Calculating My Returns Correctly? | AllFinancialMatters Says:
    November 10th, 2008 at 2:44 pm

    [...] This comment was left by Leland Holliday on an old post, Fun Math: How to Calculate Returns Using Monthly Data: [...]

  19. Leland Holliday Says:
    November 11th, 2008 at 10:15 am

    I used your method to calculate YTD return (thru 10/31/08) for each of the mutual funds in my portfolio. I expected my personal return on each of the funds to be different from published numbers, since I had done major transfers between funds in June. Instead, to my surprise, the numbers I calculated were identically the same as the published numbers. Why? Should I use a different method to compute my personal returns?

    BTW, for those who do not have access to the =Product() function in Excel, an alternate method is to take the natural log of each of the monthly factors, sum the natural log values, and then take the exponent of the sum. In MS Works, the formula would be =exp(sum(B2:B13))-1, where B2:B13 contains the natural log values of the monthly factors.

  20. Natasha26 Says:
    November 19th, 2008 at 1:16 pm

    I’ve used the method for a while but I can’t justify how the growth figures(%) of a couple of months can be used to represent the growth for an entire year (YTD)?

    So if ur fund started in March, then at the end of month, the growth for March and YTD are the same!? Then you carry on doing so for each coming months as in, YTD = (1+r_Mar)*(1+r_Apr)*…-1

    Any comments on that? thanks.

  21. jhill Says:
    August 25th, 2009 at 11:22 am

    What is the reasoning behind this math. Why can’t you just add up the returns for the month to get ytd?

  22. Cindy McKinney Says:
    December 7th, 2010 at 2:16 pm

    Does anyone have a simple method for a monthly performance calculations which includes dividends?

  23. fateme Says:
    December 22nd, 2010 at 6:09 am

    hi
    I want calculate calender in java with get year of user
    you know how calculate calender in any year?

Comments