Help calculate CAGR

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Help calculate CAGR

Post by sp226 »

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 - year-end 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.
Gill
Posts: 6686
Joined: Sun Mar 04, 2007 8:38 pm
Location: Florida

Re: Help calculate CAGR

Post by Gill »

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
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
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

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...
Gill
Posts: 6686
Joined: Sun Mar 04, 2007 8:38 pm
Location: Florida

Re: Help calculate CAGR

Post by Gill »

sp226 wrote: Sun Sep 06, 2020 7:28 am 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...
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
User avatar
racy
Posts: 265
Joined: Sun Mar 30, 2008 7:38 am

Re: Help calculate CAGR

Post by racy »

Might try this: https://tinyurl.com/ovjbhu5
There are several calculators on the site.
Last edited by racy on Sun Sep 06, 2020 7:32 am, edited 1 time in total.
Call_Me_Op
Posts: 7980
Joined: Mon Sep 07, 2009 2:57 pm
Location: Milky Way

Re: Help calculate CAGR

Post by Call_Me_Op »

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.
Average return and CAGR are different - which do you want?
Best regards, -Op | | "In the middle of difficulty lies opportunity." Einstein
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

racy wrote: Sun Sep 06, 2020 7:30 am Might try this: https://tinyurl.com/ovjbhu5
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.
MikeG62
Posts: 3101
Joined: Tue Nov 15, 2016 3:20 pm
Location: New Jersey

Re: Help calculate CAGR

Post by MikeG62 »

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 - year-end totals and assuming I added 15% in new contributions every year.

Would appreciate an excel formula or any other way of figuring it out.
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
tonyclifton
Posts: 45
Joined: Sat Feb 08, 2020 5:25 pm

Re: Help calculate CAGR

Post by tonyclifton »

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!
hayesfj
Posts: 18
Joined: Sat Dec 26, 2009 9:39 am

Re: Help calculate CAGR

Post by hayesfj »

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!
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

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!
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.
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

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
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
Posts: 3101
Joined: Tue Nov 15, 2016 3:20 pm
Location: New Jersey

Re: Help calculate CAGR

Post by MikeG62 »

sp226 wrote: Sun Sep 06, 2020 8:24 am
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
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.
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).
Real Knowledge Comes Only From Experience
dbr
Posts: 33842
Joined: Sun Mar 04, 2007 9:50 am

Re: Help calculate CAGR

Post by dbr »

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!
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.
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

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!
thank you for your help, I don't think I am getting it though.
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?
dbr
Posts: 33842
Joined: Sun Mar 04, 2007 9:50 am

Re: Help calculate CAGR

Post by dbr »

sp226 wrote: Sun Sep 06, 2020 8:24 am
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
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.
No, you calculate the internal rate of return and as already mentioned a couple of time the Excel formula for that is XIRR.

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
dbr
Posts: 33842
Joined: Sun Mar 04, 2007 9:50 am

Re: Help calculate CAGR

Post by dbr »

sp226 wrote: Sun Sep 06, 2020 8:29 am
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!
thank you for your help, I don't think I am getting it though.
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?
What hayesfj just wrote above are the instructions to use Excel XIRR function. Also:

https://exceljet.net/excel-functions/ex ... r-function

https://freefincal.com/what-is-xirr/
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

I am not sure I am doing this right.
For simplicity I looked at 2010-2015.
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%					
Is this accurate?

The reason I am doubting it is that over the 2010-2020 timeframe, it's 24%, which is highly unlikely based on my asset allocation, and over 20 years it's 50%.
Credit_Guy
Posts: 2
Joined: Tue Aug 11, 2020 8:04 pm

Re: Help calculate CAGR

Post by Credit_Guy »

sp226 wrote: Sun Sep 06, 2020 9:10 am I am not sure I am doing this right.
For simplicity I looked at 2010-2015.
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%					
Is this accurate?

The reason I am doubting it is that over the 2010-2020 timeframe, it's 24%, which is highly unlikely based on my asset allocation, and over 20 years it's 50%.
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.

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.
diy60
Posts: 386
Joined: Wed Sep 07, 2016 6:54 pm

Re: Help calculate CAGR

Post by diy60 »

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%
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

dbr 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
How about this. Assuming the contribution was added at year end, and calculating the rate of return without it -so for 2011 it's =($762-647)/647
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%						
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

Credit_guy, diy - I see what I was doing wrong, thank you for your clarification.
wanderer
Posts: 211
Joined: Sat Aug 16, 2014 4:09 pm
Location: Houston, Texas, USA

Re: Help calculate CAGR

Post by wanderer »

Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

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/how-to-calcu ... t-returns/
thank you - that gives me CAGR of 17%. Perhaps I was contributing more than the 10% I estimated...
livesoft
Posts: 73338
Joined: Thu Mar 01, 2007 8:00 pm

Re: Help calculate CAGR

Post by livesoft »

sp226 wrote: Sun Sep 06, 2020 8:21 amYes, 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.
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.
Wiki This signature message sponsored by sscritic: Learn to fish.
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

livesoft wrote: Sun Sep 06, 2020 10:16 am
sp226 wrote: Sun Sep 06, 2020 8:21 amYes, 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.
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.
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.
longinvest
Posts: 4430
Joined: Sat Aug 11, 2012 8:44 am

Re: Help calculate CAGR

Post by longinvest »

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 - year-end totals and assuming I added 10% in new contributions every year.

Would appreciate an excel formula or any other way of figuring it out.
How about an easy-to-use 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 end-of-month portfolio balance during the year and evenly spread contributions in 12 equal monthly amounts to get a reasonable estimate of past returns.

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 | One-ETF global balanced index portfolio | VPW
dbr
Posts: 33842
Joined: Sun Mar 04, 2007 9:50 am

Re: Help calculate CAGR

Post by dbr »

sp226 wrote: Sun Sep 06, 2020 9:51 am
dbr 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
How about this. Assuming the contribution was added at year end, and calculating the rate of return without it -so for 2011 it's =($762-647)/647
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%						
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
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

longinvest wrote: Sun Sep 06, 2020 10:31 am
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 - year-end totals and assuming I added 10% in new contributions every year.

Would appreciate an excel formula or any other way of figuring it out.
How about an easy-to-use 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 end-of-month portfolio balance during the year and evenly spread contributions in 12 equal monthly amounts to get a reasonable estimate of past returns.
that is EXACTLY what I needed!!!!
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 (money-weighted return, internal rate of return)

Investor return as of 8/31/2020
Since* 12/31/1999 14.7%
* Annual compound return

Trailing portfolio return (time-weighted 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%
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

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
Yes, after I posted it that's exactly what I did, thank you.
longinvest
Posts: 4430
Joined: Sat Aug 11, 2012 8:44 am

Re: Help calculate CAGR

Post by longinvest »

sp226 wrote: Sun Sep 06, 2020 10:51 am that is EXACTLY what I needed!!!!
Great!
sp226 wrote: Sun Sep 06, 2020 10:51 am 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!
Just in case... A common mistake (which inflates returns) is to forget to include the employer match as a contribution.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

longinvest wrote: Sun Sep 06, 2020 10:58 am
sp226 wrote: Sun Sep 06, 2020 10:51 am that is EXACTLY what I needed!!!!
Great!
sp226 wrote: Sun Sep 06, 2020 10:51 am 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!
Just in case... A common mistake (which inflates returns) is to forget to include the employer match as a contribution.
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 :wink: )
User avatar
#Cruncher
Posts: 3056
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Help calculate CAGR

Post by #Cruncher »

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
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.

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]
  1. 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.
  2. 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)
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

#Cruncher wrote: Sun Sep 06, 2020 11:14 am 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.
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 lump-sum 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.
livesoft
Posts: 73338
Joined: Thu Mar 01, 2007 8:00 pm

Re: Help calculate CAGR

Post by livesoft »

sp226 wrote: Sun Sep 06, 2020 10:28 amI 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.
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.
Wiki This signature message sponsored by sscritic: Learn to fish.
liftingbrosef
Posts: 20
Joined: Sun Jul 12, 2020 12:40 pm

Re: Help calculate CAGR

Post by liftingbrosef »

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.
User avatar
Taylor Larimore
Advisory Board
Posts: 29959
Joined: Tue Feb 27, 2007 8:09 pm
Location: Miami FL

Need Help calculating total return.

Post by Taylor Larimore »

Bogleheads:

I would be interested to know how much it would be worth today (or 12-31-2019) 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 stay-out!”
"Simplicity is the master key to financial success." -- Jack Bogle
Carl53
Posts: 1968
Joined: Sun Mar 07, 2010 8:26 pm

Re: Help calculate CAGR

Post by Carl53 »

I would be interested to know how much it would be worth today (or 12-31-2019) 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/sp-500-return-calculator/

Dec 1949 (assumed to be EOM) to Dec 2019 would have grown to 1433000 with a compounded return of 11.11%.
User avatar
Taylor Larimore
Advisory Board
Posts: 29959
Joined: Tue Feb 27, 2007 8:09 pm
Location: Miami FL

S&P 500 Returns?

Post by Taylor Larimore »

Carl53 wrote: Sun Sep 06, 2020 3:35 pm
I would be interested to know how much it would be worth today (or 12-31-2019) 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/sp-500-return-calculator/

Dec 1949 (assumed to be EOM) to Dec 2019 would have grown to 1433000 with a compounded return of 11.11%.
Carl53:

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 long-term investor."
"Simplicity is the master key to financial success." -- Jack Bogle
dbr
Posts: 33842
Joined: Sun Mar 04, 2007 9:50 am

Re: Help calculate CAGR

Post by dbr »

It's $1.4 million.
User avatar
Taylor Larimore
Advisory Board
Posts: 29959
Joined: Tue Feb 27, 2007 8:09 pm
Location: Miami FL

The value of compounding

Post by Taylor Larimore »

dbr wrote: Sun Sep 06, 2020 7:06 pm It's $1.4 million.
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. :oops:

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
User avatar
Eagle33
Posts: 930
Joined: Wed Aug 30, 2017 3:20 pm

Re: Help calculate CAGR

Post by Eagle33 »

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.
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

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.
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 after-tax, 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.

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 month-to-month 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 +/- 1-2%. But as this was just curiosity, I am willing to accept the results with 1-2% margin of error.

Trailing investor return (money-weighted return, internal rate of return)

Investor return as of 8/31/2020
Since* 12/31/1999 10.2%
* Annual compound return

Trailing portfolio return (time-weighted 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 (time-weighted 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%
tonyclifton
Posts: 45
Joined: Sat Feb 08, 2020 5:25 pm

Re: Help calculate CAGR

Post by tonyclifton »

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.
Topic Author
sp226
Posts: 71
Joined: Sat May 16, 2015 8:33 am

Re: Help calculate CAGR

Post by sp226 »

tonyclifton wrote: Tue Sep 08, 2020 9:20 am
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.
yes, that is usually the problem.
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.
Post Reply