Solved

How to calculate cumulative return in MS Excel?

Posted on 2011-02-27
15
12,116 Views
Last Modified: 2012-05-11
I want to calculate the cumulative return of a series of monthly fund returns over a monthly, quarterly and annual basis.  Example:

Jan: +3%
Feb: +4%
Mar: -2%
Apr: +6%
May: +8%
Jun: -3%
Jul: +1%
Aug: -4%
Sep: +2%
Oct: +1%
Nov: -3%
Dec: -2%

Could someone please put these values into an excel spreadsheet for me and enter the appropiate formula to calculate this?  Just to be clear, it is NOT simply the arithmetic sum of those numbers, it is the cumulative value I am seeking.

Thanks.
0
Comment
Question by:hedgeselect
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 2
15 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 34994701
See attached calculation which calculates cumulative return.

Dave
Cumulative-Return-r1.xlsx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34994703
The last post calculates the $ return on 1$ invested and compounds that every month.  So, the cumulative return at any point is the fraction (converted to percentage) after subtracting $1.

cheers,

Dave
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34994704
An easy way is to add 1 to each of these then use Product, the subract 1 for %

see attached for quarterly, sem-annual and annual return

Cheers

Dave
retruns.xlsx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 34994730
Crud - I missed the quarterly, semi annual, and annual....

lol,

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34994748
Ah well - here goes anyway, with perhaps simpler calculations.  

@hedgeselect Per your requrement - THIS result also includes MONTHLY - but cumulative for all these periods as well:  monthly, quarterly, semi-annual, and annual cumulative returns.

Dave


Cumulative-Return-r1.xlsx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34994749
@brettdj - you missed MONTHLY - and I missed the other three till I went back to the question...

lol - an assist at the least :)

Dave
0
 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 100 total points
ID: 34994759
well monthly returns were given as part of the input :)

happy to share points

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 400 total points
ID: 34994762
Apologies, I posted a bit too fast.

@hedgeselect - this is my final post with cumulative returns for ALL time periods requested.  I fixed a glitch in my calc... :)

Dave
Cumulative-Return-r1.xlsx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34994778
Actually, my monthly returns are the YTD cumulative return, as in a monthly YTD statement, which perhaps hedgeselect was not looking for mia culpa?

Anyway, my post is monthly YTD cumulative, then quarterly (for that quarter), semi-annual (for that period), and annual (for that period).

mia culpa, perhaps... no probably.

I'm hitting the hay.

Dave
0
 

Author Comment

by:hedgeselect
ID: 34996835
Thanks guys, I'll take a look at it later when I get a chance and yes, for the monthly data, I was for YTD monthly cumulative returns as you describe dlmille.

For a possible separate question, but is it possible, using the above scenarios, to create or amend the template such that existing charts etc can be dynamically updated when I use the template to dump several years worth of monthly returns in there without the need to manually change the referencing cells?

If so, let me know if you're up to it and I'll post a template later tonight or tomorrow incorporating the above but with some added tables and charts.  I'm just trying to avoid having to manually make changes to cell referencing etc.

Thanks.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34999510
That should not be a problem.  Below are some tips on dynamic ranges which can hold formulas for changing data pointed to charts...  
http://www.ozgrid.com/Excel/DynamicRanges.htm
http://www.ozgrid.com/Excel/advanced-dynamic-ranges.htm

So, basically, you create a named range with a formula in it that references the cells to be charted - and it is of a type that changes dynamically, with dynamically changing data (e.g., rows and/or column offsets can change).

Please feel free to post, as there are many E-E experts who are tuned into this method.

Cheers,

Dave
0
 

Author Comment

by:hedgeselect
ID: 35047891
Apologies for the delay guys.  Having just looked at the response again, dilmille appears to have the correct method in the spreadsheet.  The reason for this is that bretdj, the annual returns do not quite compound cumulatively properly.  For the end of one year, it should be 11% exactly and not 10.7%.

dlmille,
Thanks for your input.  Could you please advise whether your suggested formulas are that different in calculation procedure compared to that shown in the spreadsheet on a new question I have posted on
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26867086.html

I will close this question very shortly and split the points.  Please feel free to contribute on the new question I have just posted on dynamic ranges.

Thanks again.

0
 

Author Closing Comment

by:hedgeselect
ID: 35048957
Thanks guys.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35049257
@hedgeselect - I see no difference in our final submittals, and if you're happy with the answer that's GREAT.  If you liked the way I calculated mine (different but same answer as brettdj) that's GREAT.  If you liked the fact I did a monthly YTD - that's GREAT.

However, if you're observing different final ANSWERS, there aren't (from what I can see from both our submittals, our cumulative answers are the same on quarterly, semi-annually, and annually- suggest a 50/50 points split?

Dave
0
 

Author Comment

by:hedgeselect
ID: 35049392
Hi

I was thinking how to award this one, but as far I could see, the annual return provided by Brett showed 10.7% cumulative, but should have been 11% (without rounding) - correct me if I'm wrong.

I preferred you way of showing the data on the monthly, quarterly and annual, but happy to split it 50/50 if you are both in agreement.

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever thought of installing a power system that generates solar electricity to power your house? Some may say yes, while others may tell me no. But have you noticed that people around you are now considering installing such systems in their …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question