Help calculate CAGR
Help calculate CAGR
I've been using Quicken to track my finances since my first real job in 1999, and have 20 years of very accurate data.
But for some reason, I can't calculate my average returns accurately. The reports included in Quicken provide clearly incorrect results.
I'm not looking for perfect accuracy, but it would be nice to have a reasonably correct 5 or 10 or 20 year number.
I think I should be able to determine that with the data I have  yearend totals and assuming I added 10% in new contributions every year.
Would appreciate an excel formula or any other way of figuring it out.
But for some reason, I can't calculate my average returns accurately. The reports included in Quicken provide clearly incorrect results.
I'm not looking for perfect accuracy, but it would be nice to have a reasonably correct 5 or 10 or 20 year number.
I think I should be able to determine that with the data I have  yearend totals and assuming I added 10% in new contributions every year.
Would appreciate an excel formula or any other way of figuring it out.
Last edited by sp226 on Sun Sep 06, 2020 8:02 am, edited 1 time in total.
Re: Help calculate CAGR
It would help if you would inform those of us who are not as well informed as to what CAGR means... Is it compound annual growth rate?
Gill
Gill
Cost basis is redundant. One has a basis in an investment 
One advises and gives advice 
One should follow the principle of investing one's principal
Re: Help calculate CAGR
CAGR  Compound annual growth rate.
or whatever is the proper metric to estimate my returns.
Again, not looking for perfect accuracy, but a ballpark rate of return would be very nice...
or whatever is the proper metric to estimate my returns.
Again, not looking for perfect accuracy, but a ballpark rate of return would be very nice...
Re: Help calculate CAGR
I guessed right!
Gill
Cost basis is redundant. One has a basis in an investment 
One advises and gives advice 
One should follow the principle of investing one's principal
Re: Help calculate CAGR
Might try this: https://tinyurl.com/ovjbhu5
There are several calculators on the site.
There are several calculators on the site.
Last edited by racy on Sun Sep 06, 2020 7:32 am, edited 1 time in total.

 Posts: 7975
 Joined: Mon Sep 07, 2009 2:57 pm
 Location: Milky Way
Re: Help calculate CAGR
Average return and CAGR are different  which do you want?
Best regards, Op 

"In the middle of difficulty lies opportunity." Einstein
Re: Help calculate CAGR
thanks  the problem with most calculators, including portfolio visualizer, is that they don't allow new contributions as percentage of the total.
My portfolio, income,and savings all grew considerably over the past 20 years, but the contributions as percentage remained fairly steady at roughly 10%.
Last edited by sp226 on Sun Sep 06, 2020 8:02 am, edited 1 time in total.
Re: Help calculate CAGR
See here for CAGR:sp226 wrote: ↑Sun Sep 06, 2020 7:15 am I've been using Quicken to track my finances since my first real job in 1999, and have 20 years of very accurate data.
But for some reason, I can't calculate my average returns accurately. The reports included in Quicken provide clearly incorrect results.
I'm not looking for perfect accuracy, but it would be nice to have a reasonably correct 5 or 10 or 20 year number.
I think I should be able to determine that with the data I have  yearend totals and assuming I added 15% in new contributions every year.
Would appreciate an excel formula or any other way of figuring it out.
https://www.investopedia.com/ask/answer ... excel.asp
Shortcut to excel formula:
=(ending balance / beginning balance)^(1/number of years)1
Real Knowledge Comes Only From Experience

 Posts: 43
 Joined: Sat Feb 08, 2020 5:25 pm
Re: Help calculate CAGR
I use Quicken as well. Not as long as the OP but for many years...Here is what I noticed. Quicken does a great job of showing the rate of return when I am buying, depositing or receiving dividends.
Then I did a major rebalance. The first one since using Quicken and first time selling stock to adjust asset allocation. At that point, Quicken stopped reporting rate of return in the way I expected. The “cost” of the portfolio equaled the purchase price of the newly purchased securities and the prior “return” was gone. You can view this in the investing reports where the cost basis jumps up when you sell then purchase.
My workaround to find the CAGR and historic rate of return metrics is to use the reports built into the brokerage and 401k provider websites.
If you find a way to get Quicken to show this please let me know!
Then I did a major rebalance. The first one since using Quicken and first time selling stock to adjust asset allocation. At that point, Quicken stopped reporting rate of return in the way I expected. The “cost” of the portfolio equaled the purchase price of the newly purchased securities and the prior “return” was gone. You can view this in the investing reports where the cost basis jumps up when you sell then purchase.
My workaround to find the CAGR and historic rate of return metrics is to use the reports built into the brokerage and 401k provider websites.
If you find a way to get Quicken to show this please let me know!
Re: Help calculate CAGR
I think you might want to use the excel XIRR function instead of CAGR. Put the date of each contribution in Col A and the amount invested in Col B as a negative number. Do this for every investment date, so if you invested monthly for 20 years, you would have 240 rows of data. In the 241st row, put todays date in Col A and the current total value in Col B (as a positive number).
Below that somewhere, put in the XIRR formula. Assuming you had a header for date and contribution in Row 1, your formula should look something like:
=XIRR(b2:b242,a2:a242,.1)
XIRR can work with any series of dates, so you don't have to put in months you didn't contribute.
Good luck!
Below that somewhere, put in the XIRR formula. Assuming you had a header for date and contribution in Row 1, your formula should look something like:
=XIRR(b2:b242,a2:a242,.1)
XIRR can work with any series of dates, so you don't have to put in months you didn't contribute.
Good luck!
Re: Help calculate CAGR
Yes, this is what I found as well. It can handle individual securities just fine. It can handle small portfolios ok. But when there are multiple changes, across many accounts, the numbers are all over the place.tonyclifton wrote: ↑Sun Sep 06, 2020 7:56 am I use Quicken as well. Not as long as the OP but for many years...Here is what I noticed. Quicken does a great job of showing the rate of return when I am buying, depositing or receiving dividends.
Then I did a major rebalance. The first one since using Quicken and first time selling stock to adjust asset allocation. At that point, Quicken stopped reporting rate of return in the way I expected. The “cost” of the portfolio equaled the purchase price of the newly purchased securities and the prior “return” was gone. You can view this in the investing reports where the cost basis jumps up when you sell then purchase.
My workaround to find the CAGR and historic rate of return metrics is to use the reports built into the brokerage and 401k provider websites.
If you find a way to get Quicken to show this please let me know!
Re: Help calculate CAGR
This formula ignores regular contributions. A simple solution is to add all contributions over the years, deduct them from the ending value, and calculate it like that, but I don't think it's right.MikeG62 wrote: ↑Sun Sep 06, 2020 7:38 am See here for CAGR:
https://www.investopedia.com/ask/answer ... excel.asp
Shortcut to excel formula:
=(ending balance / beginning balance)^(1/number of years)1
Re: Help calculate CAGR
Good point and I agree that your workaround does not really solve for that wrinkle either. I suspect there is a way to do this, but it would take someone with better math skills than me to figure it out (or more interest in searching the internet for it).sp226 wrote: ↑Sun Sep 06, 2020 8:24 amThis formula ignores regular contributions. A simple solution is to add all contributions over the years, deduct them from the ending value, and calculate it like that, but I don't think it's right.MikeG62 wrote: ↑Sun Sep 06, 2020 7:38 am See here for CAGR:
https://www.investopedia.com/ask/answer ... excel.asp
Shortcut to excel formula:
=(ending balance / beginning balance)^(1/number of years)1
Real Knowledge Comes Only From Experience
Re: Help calculate CAGR
Yes, in the presence of contributions and withdrawals you have to do at least this to not have investment return and the growth due to adding money confused with each other. Of course you can compute a growth rate including your contributions as long as you don't interpret that as performance of your investments but rather as performance of your plan. Many plan managers or brokers report your internal rate of return (IRR) under the label "Personal Return." I first learned the concept of IRR in a excellent note explaining the personal return calculation for my 401k.hayesfj wrote: ↑Sun Sep 06, 2020 8:05 am I think you might want to use the excel XIRR function instead of CAGR. Put the date of each contribution in Col A and the amount invested in Col B as a negative number. Do this for every investment date, so if you invested monthly for 20 years, you would have 240 rows of data. In the 241st row, put todays date in Col A and the current total value in Col B (as a positive number).
Below that somewhere, put in the XIRR formula. Assuming you had a header for date and contribution in Row 1, your formula should look something like:
=XIRR(b2:b242,a2:a242,.1)
XIRR can work with any series of dates, so you don't have to put in months you didn't contribute.
Good luck!
Re: Help calculate CAGR
thank you for your help, I don't think I am getting it though.hayesfj wrote: ↑Sun Sep 06, 2020 8:05 am I think you might want to use the excel XIRR function instead of CAGR. Put the date of each contribution in Col A and the amount invested in Col B as a negative number. Do this for every investment date, so if you invested monthly for 20 years, you would have 240 rows of data. In the 241st row, put todays date in Col A and the current total value in Col B (as a positive number).
Below that somewhere, put in the XIRR formula. Assuming you had a header for date and contribution in Row 1, your formula should look something like:
=XIRR(b2:b242,a2:a242,.1)
XIRR can work with any series of dates, so you don't have to put in months you didn't contribute.
Good luck!
to simplify it, let's say that I'm looking only at the last 10 yrs, and contributions have been annual.
So starting in 2010, my data might be something like $1,000, $1,100, $1,320, 1,584, etc. Assuming that each of those years had 10% in new contributions, how would you calculate the rate of return?
Re: Help calculate CAGR
No, you calculate the internal rate of return and as already mentioned a couple of time the Excel formula for that is XIRR.sp226 wrote: ↑Sun Sep 06, 2020 8:24 amThis formula ignores regular contributions. A simple solution is to add all contributions over the years, deduct them from the ending value, and calculate it like that, but I don't think it's right.MikeG62 wrote: ↑Sun Sep 06, 2020 7:38 am See here for CAGR:
https://www.investopedia.com/ask/answer ... excel.asp
Shortcut to excel formula:
=(ending balance / beginning balance)^(1/number of years)1
An alternative calculation of the same situation to see the comparative performance of different investments without being confused by congributions and withdrawals is time weighted average return: https://www.investopedia.com/terms/t/ti ... tedror.asp
Re: Help calculate CAGR
What hayesfj just wrote above are the instructions to use Excel XIRR function. Also:sp226 wrote: ↑Sun Sep 06, 2020 8:29 amthank you for your help, I don't think I am getting it though.hayesfj wrote: ↑Sun Sep 06, 2020 8:05 am I think you might want to use the excel XIRR function instead of CAGR. Put the date of each contribution in Col A and the amount invested in Col B as a negative number. Do this for every investment date, so if you invested monthly for 20 years, you would have 240 rows of data. In the 241st row, put todays date in Col A and the current total value in Col B (as a positive number).
Below that somewhere, put in the XIRR formula. Assuming you had a header for date and contribution in Row 1, your formula should look something like:
=XIRR(b2:b242,a2:a242,.1)
XIRR can work with any series of dates, so you don't have to put in months you didn't contribute.
Good luck!
to simplify it, let's say that I'm looking only at the last 10 yrs, and contributions have been annual.
So starting in 2010, my data might be something like $1,000, $1,100, $1,320, 1,584, etc. Assuming that each of those years had 10% in new contributions, how would you calculate the rate of return?
https://exceljet.net/excelfunctions/ex ... rfunction
https://freefincal.com/whatisxirr/
Re: Help calculate CAGR
I am not sure I am doing this right.
For simplicity I looked at 20102015.
The first row is the portfolio values at years' end.
The second is the assumed 10% annual contribution.
I used IRR instead of XIRR because the contributions are regular (XIRR delivers the same result).
Is this accurate?
The reason I am doubting it is that over the 20102020 timeframe, it's 24%, which is highly unlikely based on my asset allocation, and over 20 years it's 50%.
For simplicity I looked at 20102015.
The first row is the portfolio values at years' end.
The second is the assumed 10% annual contribution.
I used IRR instead of XIRR because the contributions are regular (XIRR delivers the same result).
Code: Select all
Total Value $647,719.68 $847,579.18 $1,181,272.17 $1,642,410.26 $1,920,891.62 $2,168,688.85
10% annual cntrbtns $(647,719.68) $64,771.97 $84,757.92 $118,127.22 $164,241.03 $192,089.16 $216,868.89
IRR 7%
The reason I am doubting it is that over the 20102020 timeframe, it's 24%, which is highly unlikely based on my asset allocation, and over 20 years it's 50%.

 Posts: 2
 Joined: Tue Aug 11, 2020 8:04 pm
Re: Help calculate CAGR
Checking your math, it looks like your 7% is being calculated assuming you invest the $647k day 1 and then receive your future investments as cashflow to you instead of an outflow.sp226 wrote: ↑Sun Sep 06, 2020 9:10 am I am not sure I am doing this right.
For simplicity I looked at 20102015.
The first row is the portfolio values at years' end.
The second is the assumed 10% annual contribution.
I used IRR instead of XIRR because the contributions are regular (XIRR delivers the same result).Is this accurate?Code: Select all
Total Value $647,719.68 $847,579.18 $1,181,272.17 $1,642,410.26 $1,920,891.62 $2,168,688.85 10% annual cntrbtns $(647,719.68) $64,771.97 $84,757.92 $118,127.22 $164,241.03 $192,089.16 $216,868.89 IRR 7%
The reason I am doubting it is that over the 20102020 timeframe, it's 24%, which is highly unlikely based on my asset allocation, and over 20 years it's 50%.
I’m not sure exactly what the right start/end numbers would be for you, but I plugged in the following and got 13% which seems closer:
(647,720)
(64,772)
(84,757)
(118,127)
(164,241)
(192,089)
2,168,689
If you’ve ever taken money out, you’d want to account for that and net those numbers against the negative values that are assumed for the annual contributions.
Re: Help calculate CAGR
This is how I would do a rough approximation.
Code: Select all
Date $
Start 1/1/2010 647720
Contr 7/1/2010 64772
Contr 7/1/2011 84758
Contr 7/1/2012 118127
Contr 7/1/2013 164241
Contr 7/1/2014 192089
Contr 7/1/2015 216869
End 12/31/2015 2168689
XIRR 9.5%
Re: Help calculate CAGR
How about this. Assuming the contribution was added at year end, and calculating the rate of return without it so for 2011 it's =($762647)/647dbr wrote: ↑Sun Sep 06, 2020 8:32 am
An alternative calculation of the same situation to see the comparative performance of different investments without being confused by congributions and withdrawals is time weighted average return: https://www.investopedia.com/terms/t/ti ... tedror.asp
is that accurate?
Code: Select all
12/31/2009 12/31/2010 12/31/2011 12/31/2012 12/31/2013 12/31/2014 12/31/2015
Total Value $647,719.68 $847,579.18 $1,181,272.17 $1,642,410.26 $1,920,891.62 $2,168,688.85
Value without 10% contribution $582,947.71 $762,821.26 $1,063,144.95 $1,478,169.23 $1,728,802.46 $1,951,819.97
Rate of return 18% 25% 25% 5% 2%
Average Return 15%
Re: Help calculate CAGR
Credit_guy, diy  I see what I was doing wrong, thank you for your clarification.
Re: Help calculate CAGR
Perhaps what you are looking for is the "Modified Dietz Method".
https://www.investopedia.com/terms/m/mo ... of%20time.
https://www.fool.com/about/howtocalcu ... treturns/
https://www.investopedia.com/terms/m/mo ... of%20time.
https://www.fool.com/about/howtocalcu ... treturns/
Re: Help calculate CAGR
thank you  that gives me CAGR of 17%. Perhaps I was contributing more than the 10% I estimated...wanderer wrote: ↑Sun Sep 06, 2020 9:58 am Perhaps what you are looking for is the "Modified Dietz Method".
https://www.investopedia.com/terms/m/mo ... of%20time.
https://www.fool.com/about/howtocalcu ... treturns/
Re: Help calculate CAGR
This doesn't make sense. Years ago I used Quicken, but now use MS Money. For MS Money, I need to make sure the start and end dates are legit. For instance, to get YTD growth, I need to put date range 12/31/2019 to 12/31/2020 or the software will annualize the growth, so if I got 10% in first 6 months, it would say I have 20% CAGR unless I had a date range set. I know that Quicken did the same thing. There must be something on the internet (especially YouTube) if you search on "Quicken XIRR" or "Quicken CAGR" I recall there were 2 ways of clicking buttons in Quicken to get performance numbers and one would be "all over the place" and the other was what you want.
Re: Help calculate CAGR
I think the fundamental problem is that Quicken tries to calculate the growth of investments, whereas I am trying to find the growth of the total portfolio. It does well with individual securities.livesoft wrote: ↑Sun Sep 06, 2020 10:16 amThis doesn't make sense. Years ago I used Quicken, but now use MS Money. For MS Money, I need to make sure the start and end dates are legit. For instance, to get YTD growth, I need to put date range 12/31/2019 to 12/31/2020 or the software will annualize the growth, so if I got 10% in first 6 months, it would say I have 20% CAGR unless I had a date range set. I know that Quicken did the same thing. There must be something on the internet (especially YouTube) if you search on "Quicken XIRR" or "Quicken CAGR" I recall there were 2 ways of clicking buttons in Quicken to get performance numbers and one would be "all over the place" and the other was what you want.

 Posts: 4426
 Joined: Sat Aug 11, 2012 8:44 am
Re: Help calculate CAGR
How about an easytouse Blogleheads spreadsheet? Here's a wiki link: Calculating personal returns. There's a support thread. It requires monthly data, though. Maybe you could use a linear approximation for endofmonth portfolio balance during the year and evenly spread contributions in 12 equal monthly amounts to get a reasonable estimate of past returns.sp226 wrote: ↑Sun Sep 06, 2020 7:15 am I'm not looking for perfect accuracy, but it would be nice to have a reasonably correct 5 or 10 or 20 year number.
I think I should be able to determine that with the data I have  yearend totals and assuming I added 10% in new contributions every year.
Would appreciate an excel formula or any other way of figuring it out.
Here's what I mean by linear approximation. Let say that portfolio balance was $175,000 on December 31, 2010 and $265,000 on December 31, 2011, that would mean that the portfolio increased by (($265,000  $175,000) / 12) = $7,500 on average per month in 2011. So, I would set the portfolio balance to ($175,000 + $7,500) = $182,500 on January 31, 2011, ($182,500 + $7500) = $190,000 on February 28, 2011, and so on.
As for contribution, assuming I contributed $30,000 in 2011, I would set the contribution to ($30,000 / 12) = $2,500 from January to December 2011.
Last edited by longinvest on Sun Sep 06, 2020 10:51 am, edited 2 times in total.
Bogleheads investment philosophy  OneETF global balanced index portfolio  VPW
Re: Help calculate CAGR
That would be ok for each year. Did you follow the formula for compound return? It looks like you computed the arithmetic average rather than the compound growth rate. You need to compute the geometric average of the gain multipliers and then subtract 1 to get an annualized compound average. A gain muliplier for a year is 1 plus the return expressed as a decimal. Here is a definition: https://www.investopedia.com/terms/g/geometricmean.aspsp226 wrote: ↑Sun Sep 06, 2020 9:51 amHow about this. Assuming the contribution was added at year end, and calculating the rate of return without it so for 2011 it's =($762647)/647dbr wrote: ↑Sun Sep 06, 2020 8:32 am
An alternative calculation of the same situation to see the comparative performance of different investments without being confused by congributions and withdrawals is time weighted average return: https://www.investopedia.com/terms/t/ti ... tedror.asp
is that accurate?
Code: Select all
12/31/2009 12/31/2010 12/31/2011 12/31/2012 12/31/2013 12/31/2014 12/31/2015 Total Value $647,719.68 $847,579.18 $1,181,272.17 $1,642,410.26 $1,920,891.62 $2,168,688.85 Value without 10% contribution $582,947.71 $762,821.26 $1,063,144.95 $1,478,169.23 $1,728,802.46 $1,951,819.97 Rate of return 18% 25% 25% 5% 2% Average Return 15%
Re: Help calculate CAGR
that is EXACTLY what I needed!!!!longinvest wrote: ↑Sun Sep 06, 2020 10:31 amHow about an easytouse Blogleheads spreadsheet? Here's a wiki link: Calculating personal returns. There's a support thread. It requires monthly data, though. Maybe you could use a linear approximation for endofmonth portfolio balance during the year and evenly spread contributions in 12 equal monthly amounts to get a reasonable estimate of past returns.sp226 wrote: ↑Sun Sep 06, 2020 7:15 am I'm not looking for perfect accuracy, but it would be nice to have a reasonably correct 5 or 10 or 20 year number.
I think I should be able to determine that with the data I have  yearend totals and assuming I added 10% in new contributions every year.
Would appreciate an excel formula or any other way of figuring it out.
Literally took me 3 minutes to input 240 months worth of data, assume that each month I contributed 1/120 of the previous months' total, and I had al the numbers I was looking for.
Probably some wrong assumptions, especially in the early years when new contributions were likely a higher percentage, so I've got some work to do, but this is incredibly helpful!
Thank you so very much.
Trailing investor return (moneyweighted return, internal rate of return)
Investor return as of 8/31/2020
Since* 12/31/1999 14.7%
* Annual compound return
Trailing portfolio return (timeweighted return, comparable return)
Portfolio return as of 8/31/2020
1 month 4.7%
3 months 12.1%
6 months 10.3%
YTD 2.6%
1 year 13.1%
3 years* 10.4%
5 years* 12.7%
10 years* 15.1%
15 years* 8.2%
20 years* 21.3%
Re: Help calculate CAGR
Yes, after I posted it that's exactly what I did, thank you.dbr wrote: ↑Sun Sep 06, 2020 10:39 am
That would be ok for each year. Did you follow the formula for compound return? It looks like you computed the arithmetic average rather than the compound growth rate. You need to compute the geometric average of the gain multipliers and then subtract 1 to get an annualized compound average. A gain muliplier for a year is 1 plus the return expressed as a decimal. Here is a definition: https://www.investopedia.com/terms/g/geometricmean.asp

 Posts: 4426
 Joined: Sat Aug 11, 2012 8:44 am
Re: Help calculate CAGR
Great!
Just in case... A common mistake (which inflates returns) is to forget to include the employer match as a contribution.
Bogleheads investment philosophy  OneETF global balanced index portfolio  VPW
Re: Help calculate CAGR
I am the employer, so this is not a problem. But there are some mistakes for sure, some of which I've been able to correct very quickly ( a downpayment withdrawal in 2006, and it's very likely I didn't get 2000% return in 2000), but overall this is very helpful to calculate my returns MTD (millennium to date )longinvest wrote: ↑Sun Sep 06, 2020 10:58 amGreat!
Just in case... A common mistake (which inflates returns) is to forget to include the employer match as a contribution.
Re: Help calculate CAGR
You seem to assume, sp226, that the contribution for the first year equals the portfolio value at the end of the first year, and subsequent contributions for year Y + 1 equal 10% of the portfolio for year Y. This makes no sense to me. It would make more sense to assume the annual contribution is fixed. [1] In this case one can solve for the return with the Excel RATE function. [2] Here is an illustration using your year end values and an assumed initial investment of $500,000 and a $50,000 contribution at the end of each year.sp226 wrote: ↑Sun Sep 06, 2020 9:10 am... The first row is the portfolio values at years' end.
The second is the assumed 10% annual contribution. ...Code: Select all
Total Value 647,719.68 847,579.18 1,181,272.17 1,642,410.26 1,920,891.62 2,168,688.85 10% cntrbtns (647,719.68) 64,771.97 84,757.92 118,127.22 164,241.03 192,089.16 216,868.89
Code: Select all
Row Col A Col B Col C Col D
1 Initial investment 500,000
2 Annual contribution 50,000
3 Year Value Return Cash Flow
   
4 0 0 (500,000)
5 1 647,720 19.5% (50,000)
6 2 847,579 21.4% (50,000)
7 3 1,181,272 25.6% (50,000)
8 4 1,642,410 28.0% (50,000)
9 5 1,920,892 24.8% (50,000)
10 6 2,168,689 22.0% 2,118,689
11 IRR 22.0% [2]
 One could assume the annual contribution is fixed in real terms (i.e., increases each year in line with inflation) by using each year's Consumer Price Index to convert the year end values to be in constant dollars of the initial year. The calculated return would then be a real rate of return.
 One can also solve for the return with the Excel IRR function. Column D shows this for the case of the 22.0% return over six years.
Code: Select all
22.0% = IRR(D4:D10) 22.0% = RATE(A10, B$2, B$1, B10, 0) 22.0% = RATE( 6, 50000,500000, 2168689, 0)
Re: Help calculate CAGR
That's correct. While 10% is a crude approximation, it's surprisingly accurate looking back. It obviously wasn't a constant 10%, but it did grow substantially every year. With the spreadsheet posted here, I am able to quickly look at monthly increases, and correct for obvious lumpsum additions (where I see the balance jumped by 10+% in a month, for example). Other than that 10% of portfolio value per year is a pretty close guess.
Re: Help calculate CAGR
I'll disagree as I have done exactly what you want somehow when my mother used Quicken with her portfolio that she had entered into Quicken.

 Posts: 20
 Joined: Sun Jul 12, 2020 12:40 pm
Re: Help calculate CAGR
There is a report in the report center called "Investment Performance". If you change the date range on this to "Earliest to Date", it should calculate the Average Annual Growth Rate (IRR) over that time period and total it at the very bottom of the report. I've always assumed it was correct. According to the report, my IRR is just over 10% and I've been saving/investing since 2011. That also includes my ESPP which boosts it up a little higher than it would otherwise.
 Taylor Larimore
 Advisory Board
 Posts: 29938
 Joined: Tue Feb 27, 2007 8:09 pm
 Location: Miami FL
Need Help calculating total return.
Bogleheads:
I would be interested to know how much it would be worth today (or 12312019) if I had simply invested $1,000 in the S&P 500 stocks on the first day of January 1950, the year I started investing. According to the "Stock Trader's Almanac," the "Standard & Poor's 500 monthly closing price was 17.05 on January 31, 1950."
Yes, I'm aware that the S&P 500 Index began January 1, 1957.
Help anyone? Please include dividends.
Thank you and best wishes.
Taylor
I would be interested to know how much it would be worth today (or 12312019) if I had simply invested $1,000 in the S&P 500 stocks on the first day of January 1950, the year I started investing. According to the "Stock Trader's Almanac," the "Standard & Poor's 500 monthly closing price was 17.05 on January 31, 1950."
Yes, I'm aware that the S&P 500 Index began January 1, 1957.
Help anyone? Please include dividends.
Thank you and best wishes.
Taylor
Jack Bogle's Words of Wisdom: “Simply buy … a total stock market index fund. Then, once you have bought your stocks, get out of the casino—and stayout!”
"Simplicity is the master key to financial success."  Jack Bogle
Re: Help calculate CAGR
I would be interested to know how much it would be worth today (or 12312019) if I had simply invested $1,000 in the S&P 500 stocks on the first day of January 1950, the year I started investing. According to the "Stock Trader's Almanac," the "Standard & Poor's 500 monthly closing price was 17.05 on January 31, 1950."
Per https://dqydj.com/sp500returncalculator/
Dec 1949 (assumed to be EOM) to Dec 2019 would have grown to 1433000 with a compounded return of 11.11%.
 Taylor Larimore
 Advisory Board
 Posts: 29938
 Joined: Tue Feb 27, 2007 8:09 pm
 Location: Miami FL
S&P 500 Returns?
Carl53:Carl53 wrote: ↑Sun Sep 06, 2020 3:35 pmI would be interested to know how much it would be worth today (or 12312019) if I had simply invested $1,000 in the S&P 500 stocks on the first day of January 1950, the year I started investing. According to the "Stock Trader's Almanac," the "Standard & Poor's 500 monthly closing price was 17.05 on January 31, 1950."
Per https://dqydj.com/sp500returncalculator/
Dec 1949 (assumed to be EOM) to Dec 2019 would have grown to 1433000 with a compounded return of 11.11%.
Thank you so much. I'm not clear about the decimals. Is it $14,300.00? which seems low? $143,300 seems about right but it misses one decimal point?
Best wishes.
Taylor
Jack Bogle's Words of Wisdom: "To invest with success you must be a longterm investor."
"Simplicity is the master key to financial success."  Jack Bogle
 Taylor Larimore
 Advisory Board
 Posts: 29938
 Joined: Tue Feb 27, 2007 8:09 pm
 Location: Miami FL
The value of compounding
dbr: Thank you for your clarification. I am amazed.
Lesson learned: I should have simply invested $1,000 at age 26, done nothing, and I'd be richer today.
Best wishes.
Taylor
Jack Bogle's Words of Wisdom: "Stay the Course. No matter what happens, stick to your program. I've said "Stay the course" a thousand times, and I meant it every time. It is the most important single piece of investment wisdom I can give to you."
"Simplicity is the master key to financial success."  Jack Bogle
Re: Help calculate CAGR
Wasn't 1950 "BB" (before Bogle)? Bogle graduated magna cum laude from Princeton in 1951.
Rocket science is not “rocket science” to a rocket scientist, just as personal finance is not “rocket science” to a Boglehead.
Re: Help calculate CAGR
I've tried that report in the past  it does not give me an accurate result. Perhaps the problem is that I have 20 different investing accounts (several aftertax, personal 401k/Cash Balance/IRA, wife's 401k/IRA, kids' 529s and IRAs, HSAs, etc), and many assets were moved from one to another, possibly corrupting cost basis. So my totals are always accurate, but investment returns within the accounts are often not.liftingbrosef wrote: ↑Sun Sep 06, 2020 2:42 pm There is a report in the report center called "Investment Performance". If you change the date range on this to "Earliest to Date", it should calculate the Average Annual Growth Rate (IRR) over that time period and total it at the very bottom of the report. I've always assumed it was correct. According to the report, my IRR is just over 10% and I've been saving/investing since 2011. That also includes my ESPP which boosts it up a little higher than it would otherwise.
However, the link given above is fantastic https://www.bogleheads.org/wiki/Calcula ... al_returns
As I had very accurate data on EOM balance for the past 240 months, and most of my investing has been at regular intervals, climbing as the income grew, I was able to estimate the returns pretty closely. I could zero in on big monthtomonth jumps because those were likely much bigger contributions, and correct for those.
And this what I got. is it perfectly accurate? No, not at all. I estimated that I contributed 12% of the total portfolio every year. In reality it's probably +/ 12%. But as this was just curiosity, I am willing to accept the results with 12% margin of error.
Trailing investor return (moneyweighted return, internal rate of return)
Investor return as of 8/31/2020
Since* 12/31/1999 10.2%
* Annual compound return
Trailing portfolio return (timeweighted return, comparable return)
Portfolio return as of 8/31/2020
1 month 5.1%
3 months 12.4%
6 months 10.3%
YTD 3.0%
1 year 13.7%
3 years* 10.5%
5 years* 12.3%
10 years* 12.9%
15 years* 7.6%
20 years* 9.7%
Annual portfolio return (timeweighted return, comparable return)
2000 9.4%
2001 8.0%
2002 15.6%
2003 13.1%
2004 17.1%
2005 12.0%
2006 4.0%
2007 7.4%
2008 30.2%
2009 6.3%
2010 18.8%
2011 16.6%
2012 25.5%
2013 22.4%
2014 4.6%
2015 1.0%
2016 14.8%
2017 15.4%
2018 3.2%
2019 26.5%

 Posts: 43
 Joined: Sat Feb 08, 2020 5:25 pm
Re: Help calculate CAGR
liftingbrosef wrote: ↑Sun Sep 06, 2020 2:42 pm There is a report in the report center called "Investment Performance". If you change the date range on this to "Earliest to Date", it should calculate the Average Annual Growth Rate (IRR) over that time period and total it at the very bottom of the report. I've always assumed it was correct. According to the report, my IRR is just over 10% and I've been saving/investing since 2011. That also includes my ESPP which boosts it up a little higher than it would otherwise.
Thank you for this tip. This report isn’t accurate if I include a rebalancing I did in January as part of the timeframe. It could be because the 401k reported the shares as “added”/“removed” instead of “bought”/“sold.” I’ll look into this further.
Re: Help calculate CAGR
yes, that is usually the problem.tonyclifton wrote: ↑Tue Sep 08, 2020 9:20 amliftingbrosef wrote: ↑Sun Sep 06, 2020 2:42 pm There is a report in the report center called "Investment Performance". If you change the date range on this to "Earliest to Date", it should calculate the Average Annual Growth Rate (IRR) over that time period and total it at the very bottom of the report. I've always assumed it was correct. According to the report, my IRR is just over 10% and I've been saving/investing since 2011. That also includes my ESPP which boosts it up a little higher than it would otherwise.
Thank you for this tip. This report isn’t accurate if I include a rebalancing I did in January as part of the timeframe. It could be because the 401k reported the shares as “added”/“removed” instead of “bought”/“sold.” I’ll look into this further.
The program is accurate when all the transactions are accurate. And if there aren't a lot of them, it's easy to keep it clean.
In my case, there are 20 accounts, some going back 20 years, with hundreds of thousands of transactions  not to mention data corruption as it went through multiple versions and flavors from Quicken Basic in 1999 through the many upgrades and updates every few years, to what it is right now.
As a result, the values are correct, but cost basis is usually not.