Comment from the author: A lot of people visit this page after googling for cagr calculations in excel. Is what’s here what you’re looking for? If not, leave a comment on what you were wanting instead and it’ll help me put more useful content. Thanks!

please send me the file on CAGR calculation

Hi Maanav,

You can get the file by right clicking on the bold orange link at the top of the post, or directly by going here:

http://www.experiglot.com/wp-content/tools/CAGR%20calculator.xls

Let me know if you still can’t get it, and I’ll try to send it to you.

[...] Ricemutt over at Experiments in Finance has written some wonderful posts (here, here, and here) on financial math. It was through her (I think Ricemutt is a “her,” but I’m not positive on that) posts that I discovered the meaning of Compound Annual Growth Rate or CAGR as it is commonly known. Yes, I was aware of CAGR but I never really thought about it much. I didn’t know it at the time that I put my Average vs. Geometric Average post together using an Excel spreadsheet, but CAGR and Geometric Average are the same thing. And, to top it off, I found a formula for calculating them that is MUCH easier than I previously understood. That’s good for all of us! [...]

[...] My little earnings experiment for this site continues. September earnings and traffic were down 10% and 25% respectively, largely due to my absense in blogging earlier this month. But, this was also the first month in which I had no days with 0 Adsense clicks, which means I’m getting a critical mass of traffic, I believe. My most popular post remains the one about using CAGR in Excel, written waaay back in January when I first started this site. [...]

[...] Personal finance, Corporate finance I’ve previously created a spreadsheet for calculating compound annual growth rates (CAGR) as well as an online CAGR calculator, but these aren’t always the tools you need when trying to calculate your returns. [...]

[...] If you wanted to put this into an Excel spreadsheet, it might look like what’s below (taken from the first part of my CAGR excel spreadsheet) (click to enlarge): [...]

I’m a rookie..I’m building an excell spread sheet for a small investment club that would reflect the actual annualized rate of return for individual stocks. I’m using your CAGR for a single lump sum investment over many years. My question is do you change the factor 1 to reflect the number of years invested? Also, I’m assuming you leave the -1 alone.
=($B$8/$B$7)^(1/$B$9)-1

By the way, I’m wanting to calculate the annualized return for each year I’ve held the investment. As part of the bigger picture I’m going to calculate a weighted return for the whole portfolio, year by year….(just a set-up for more questions!).

I’m working with a particular stock that has lost value and I noticed when using this formula the negative return is getting smaller rather than larger every year? Is there a change to the formula for negative returns?

Paul: Happy to help. I’ll answer and respond to you via email.

[...] Toward the end of December, I received some questions from readers asking about how to calculate average returns on investment portfolios using Excel. In previous posts on this site, you can read about using CAGR, Excel’s XIRR function, and simple averages, but they all come with one big warning, which is that your previous average performance may not necessarily be a good way to set your expectations about your future performance. [...]

Just a note to thank you for sharing your expertise and taking the time to put all this together.

kindly send me the excel file for CAGR calculation

Ricemutt:
Is there a way to calculate an average return on investment or rate of return on mutual fund investments over a (6) year period when additions have been made once a year for every one of the (6) years? I have the beginning amount, the ending amount, the additional investment amounts and the date on which they were made.

What is the formula for CAGR if initial and ending value are both negative?

As long as your beginning and ending numbers have the same sign, it shouldn’t matter, so you can just plug in the two negatives into the calculator and get the CAGR.

The math isn’t defined it if your initial or ending amount is positive and the other one is negative, though.

Thanks – this is a life saver!

Hallo, I need a formula for initial negative and end positive value. Who could help me?

Dear Ricemutt

Your CAGR calculator is very useful. i would still like to understand the underlying logic if you could mail it to me

Regards
Aditya

Thanks so much for your CAGR excel calculator! It really helped me with my strategic management financial ratio analysis so late at night (I was not thinking clearly). :) God bless! P.S. Your comment box deleted my first message because I didn’t fill in one of the blanks; that was sad.

I need to calculate the monthly annualised CAGR. Eg, the increase of retail sales between January 2005 and September 2006. For the period, instead of putting 1 for one year, I would imagine I could put 12/12 for the year. If it’s over 21 months, I guess I could put 21/12 for the period? Is this mathematically sound? Or does the compounding affect it?

how do you calculate a CAGR where the starting value is negative
or
both starting and ending values negative

like negative profit $ 500 million 2001
and pisitive profit $ 1.5 billion 2006

How could we calculate CAGR for SIP.

How do we calculate CAGR in case of a positive result at the end of the period and a negative return at the beginning of the period (or) vice-versa?

okia…

okia…

I have a number of savings and deposit products where I need a formula to calculate the Compound Annual Rate and Net Rate. For example there is a 1month product with a gross rate of 2.87% what would be the CAR in this case – the other products would be for 2mth, 3mth, 5mths etc.

I’m trying to calculate the monthly return for a portfolio of shares. Some appreciation in the portfolio value is due to injection of new monry in the portfolio. how can i calculate the return given this change?

I’m trying to do a CARG % calculation of a series of business revenue and units sold over a period of 10 years. There is no investment so the XIRR function won’t return a result unless the first value is negative. So, what different function can I use to obtain a CAGR for a series of all positive values?

Thanks for the CAGR calculation example. Very helpful (way more helpful than “help” available in Excel.

I WANT WORKSHEET LIKE PRODUCT WISE DEALER WISE AND MONTHLY GR IN % ..RAVI

I want a exel file or a formula to calculate CAGR in a excel sheet

I wanted to combine all such usefull tools in one xl file and post…:) in the process of accumalating the same… if any oen have any suggestions please let me know

Dear All,

I am not able to figure out the difference between CAGR and IRR…and which rate will be more..

Pls help.
Tarun
E-Mail- tarunkuwar@rediffmail.com

THANK YOU VERY MUCH!
ARE YOU STILL ACTIVE WITH THIS BLOG/SITE?

Hi, Ricemutt
I want to how will I calculate CAGR of sales for 4 yrs. Is it that the sales of begining year and the end year will be taken, if so then if the sales of end year is less than the begining sales, i think CAGR will be negative. However, the sales of the middle years are higher. Please advice what to do.

Thnaks

Hi,

What a great site. I want to demonstrate a CD on spreadsheet.

$10,000 investment. the credit union pays 6.5% p.a. compounded at the end of each quarter for 2 years.

I want to see what the value of the investment will be after each quarter.

Then I want to compare what the return each year would be with simple interest from the bank for the same investment over the same period.

XIRR didn’t to it for me and CAGR didn’t help since I am already told that the compounded interest rate is 6.5%.

Thanks for your help.

HOW TO TEST THE CAGR STATISTICALLY? IS IT POSSIBLE TO USE T-TEST ? KINDLY ADVAISE OR GIVE AN IDEA TO ANSWER TO MY QUESTION?

How to calculate CAGR in case of a Systematic Investment Plan? Plzzzzz help me….. urgently need help.

what a site! , this sunday afternoon i am in midst of my presentation for tomorows business review meet with the business head , and i desperately needed a simple formula sheet for CAGR , wow that was great , i could simply use it , thanks to the team
regards
ashish

I need a calculator for compound QUARTERLY growth rate.

How do we calculate CAGR in case of a positive result at the end of the period and a negative return at the beginning of the period (or) vice-versa? with formula and example ?

It’s very helpful work. Thanks for the team. Please also give me the solution for the following.

How do we calculate CAGR in case of a positive result at the end of the period and a negative return at the beginning of the period (or) vice-versa? with formula and example ?

You are a lifesaver. I was desperately looking for an easy way out to calculate CARG and now my search is over.

My thanks from the deepest part of my heart.

Thank you!! It is perfect, just what i needed and incredibly easy to use!! Saved me from a ton of endless calculations. Please, continue your work!! THK U!

Hi

I have downloaded the CAGR excel file. I work in a Bank and we are required to calculate CAGR while analysing financial condition of clients. This file is a great help to me. My colleagues are also using the file. I am going through various topics in your blog/site. Thanks for providing useful information in a clutter free manner.

regards

Srikanth

This is an extremely helpful tool- very much appreciated!

Hey,

First of all thanks for the wonderful calculators….I am looking for CAGR calculation for variable cashflows in regular savings…somewhat Like an SIP but in this case the first amount is higher and future amounts are fixed….investing say for a period of 10 years , how can I calculate the encashement @ 10% rate of return

This is kind of urgent …plzzzzzzz share ur expertise:)

Hi,

I need to calculate compound QUARTERLY growth rate. How do I do that?

Please let me know.

Thanks.

I need help!!

If we have quarterly GDP data:
(a) how could we calculate a quarterly compound growth rate?
(b) how could we calculate an equivalent annual growth rate?

I need help!!

If we have quarterly GDP data:
(a) how could we calculate a quarterly compound growth rate?
(b) how could we calculate an equivalent annual growth rate?

Im guessing that the last three questions about calculation quarterly date are from people having to send in their economis ‘case’ assessments by 5am tomorrow???

Sooo stuck aswel!

Hi,

I’m building a revenue forecast tool in Excel that applies an annual growth % for Years 1 to 4. This is then presented back by Year as monthly growth using = ((FV/PV)^(1/12)) – 1. However, I also have Seasonal Weightings that I would like to apply to each of the months (ie Jan:11%, Feb:6%, Mar:7%, etc, so that 100% across the Year). How can I apply Seasonal Weightings to the CAGR formula above?

excellent contents, thanks a ton

Dear sir,
kindly send me the Sip formula.
nandakumar

Nice work. The excel template is exactly what I need. Thank you.

Hi Ricemutt,
Could you explain how to calculate the CAGR for Systematic Investment plan??
Thanks in anticipation.
Mayank

Use MS excel and get the CAGR plugin from the net it’s really simple dude :)

if i have a current value on a regular savng plan how can i work out the CAGR – say $500 pcm for last 5 years, currently worth $55,000 (overall growth/profit as % is easy, but how do i get the CAGR taking account of the rising capital balance?).

hi, Thanks a lot for your template! I am already looking for this for ages!

please note i have tried CAGR USING EXCEL AND I GOT THE RESULT , BUT WHEN I USE THE SAME DATA TO CALCULATE USING NORMAL CAGR FORMULAE THE RESULT IS DIFFERENT .PLEASE EXPLAIN

I want more info about Investment…i want to calculate Quarterly…

Yes exactly what i was after, thanks!

Financial sector has to address its general public preception.

I like what you guys are up also. Such clever work and reporting! Carry on the excellent works guys I’ve incorporated you guys to my blogroll. I think it’ll improve the value of my web site;)

please give example for calculating cagr with xirr