Message Font: Serif | Sans-Serif

No. of Recommendations: 0
How would you go about calcualting CAGR with multiple purchases (drip's, buying on dips, etc.). I know there is a function in Excel that can do it for you, but I want to know how I would do it by hand. I'm the type that needs to see it before I can understand it. My problem is a lot of my stocks I purchased on a monthly basis, so I will have about 20 purchases per stock. Now I understand the CAGR equation, but with multiple purchase prices and dates, I just dont know what you use for values. Thanks for your help.

Eric
No. of Recommendations: 1
"Now I understand the CAGR equation, but with multiple purchase prices and dates, I just dont know what you use for values. Thanks for your help." - Jrice08

What I do is somewhat different from what you are attempting to do. I calculate my personal CAGR once a year based on my December 31 account value. I do not even need the formula to do that. I just divide the previous year's closing value into this year's value...presto! I have my CAGR.

If I have withdrawn funds I add these back into this year's value and if I had added funds, I subtract them. I live off of my IRA, so I am taking out my unearned income monthly with a simple automatic transfer into my checking account. That is also shown on my year-end statement, so the overall calculation is very easy.

By the way, I keep every year-end statement in a file so that I can go back at any time and see how my CAGR is behaving over time. I tend to keep the monthly statements for a couple of years before shreading them. The BuildMWell Company keeps things very simple because I am a pretty simple person, hopefully not a simpleton.
No. of Recommendations: 3
Eric,

Matt Richey wrote a 2-part article a while back on how to use a spreadsheet to track your port using NAV just like a mutual fund. It accounts for all cash in/out transactions. It's particularly handy if you have lots of contributions. For instance, a 401(k) plan that gets new money added weekly. He includes a sample Excel spreadsheet to do this. I went back and entered every statement I have (took the better part of a weekend) and can now see my actual cash-on-cash returns since day 1. I think this is very important to do because many investors don't properly account for cash in/out and may be deluding themselves as to their true CAGR to their great detriment. It also gave me the confidence to talk to some of my family members about helping them to manage their money because now I can show actual returns over many years.

Have fun tracking the Jrice08 fund.

Tom
No. of Recommendations: 0
How would you go about calcualting CAGR with multiple purchases (drip's, buying on dips, etc.).
Eric

Internal Rate of Return (IRR) which is the sum of the present values of each individual transaction using the same interest rate.

Denny Schlesinger
No. of Recommendations: 3

Hi Jrice,

I'm chuckling because Jim's long-term relaxed style of planning shows my short-term thinking in the harsh spotlight. I know the CAGR for every stock I've ever purchased and for each time I've purchased it. My last purchase was 13 days ago (OMTR) and has a gain of 20.7% and a CAGR of 629.6%. So what does that tell me, besides that fact that it got off to a good start? 1) I got lucky for 2 weeks. 2) I wish they would all do that! 3) Absolutely nothing of value.

But my purchases from 3, 6 12 months ago, those CAGR stats tells me if I am heading in the right direction, and whether or not I am on target to meet my CAGR goal for this year. Keeping a monthly tally also shows me which companies are slowing down, and for how long, which for me becomes a useful tool for companies that I have owned for more than one year. It highlights companies that are going flat and others that are turning a corner. If I look only at percentage gains of investments from purchase-to-today, it doesn't tell me WHEN those gains were made—Were they all from last week, or were they all made during the first 60 days I held them and remained flat ever since? Meaningless to a super-sized portfolio, maybe, but when working with small-sum ports, it helps me keep the focus where I want it—on CAGR.

I no longer look at gains—ever. I look at CAGR. Maybe that wasn't the lesson intended from BMW, but it sure helps me focus where I need to focus and has give a whole new view to buying (and selling) prices.

One CAGR for all purchases requires some fudging and calculating (You could calculate your average buy-price and weight them by multiplying by your number of days held for each PURCHASE (not each ticker) for a particular company.

The simplest way to track CAGR is for each purchase separately, but I'm not sure in your case (with DRPS) that it would tell you what you really want to know. Either way, if you care to email me, I will give you a template to track your CAGR either by purchase or by ticker. To kick-start the process yourself, you might try Excel Help, Functions, Financial. Check out IRR.

But for DRPS, I think Jim has the best idea. Buy them, forget them, calculate CAGR on Dec. 31st. Don't forget to stock up on alka-seltzer for January 1st.*

* Alka-Seltzer is made by Bayer. Please buy the generic equivalent from MRK because I see by my CAGR summaries, that my Merck CAGR is going flat. I doubt it's losing its fizz, it's probably just taking a nap. But see what CAGR can tell you? Exciting stuff! Right up there with your cholesterol count. :)

Best,

Dan
note to self: Stock up on generic Alka-Seltzer from Merck before Dec 31 and send 2 boxes to Sir IcyWolf for Christmas
No. of Recommendations: 0
Eric,

I'm sure my tracking is different than others, but what else is new.

Individual stocks owned for less than an average of 6 months I ignore CAGR and just use simple G/L %. I find that the CAGR number changes too wildly for short term purchases.

My YAWACAGR computation:
For each individual stock purchase I compute a work (work1) column of CAGR * #shares * #Days_owned and another work column of #shares * #days_owned (work2).

On a separate worksheet I sum this work1 column by stock symbol and divide by the sum of the work2 column by stock to compute average CAGR.

I take this one step further by adding the YOIC (Yield On Invested Capital) to the average CAGR to generate YAWACAGR (Yield Adjusted Weighted Average Compound Average Growth Rate). I feel like the YAWACAGR equalizes the comparison of high dividend stocks and low/no dividend stocks.

This may sound complicated, but Excel makes all this pretty easy.
---
I use the BuildMWell method for overall portfolio performance. Take what you got now, subtract what you had a year ago, adjust for cash added or taken out and see how you did.

John

No. of Recommendations: 0
So if my balance was \$10,000 on 12/31/05, I added \$4000 during the year, and ended the year with a balance of \$16,000. My CAGR for the year would be (16000-4000)/10000 to get 1.2 or 20%. Is this correct? It seems to simple, and it feels like it should matter when you added that \$4000.
No. of Recommendations: 3

Very observant, JRice. It IS too simple, and it is technically not correct, although the difference may be small (or not.) You are right, the time value of the \$4k added, is not included anywhere in this caluclation.

I suspect BMW's totals and CAGRs are so high he doesn't sweat the details, so he figures CAGR the easy way. Just think what his real CAGR must be! Scary, isn't it? If you want to track CAGR accurately, you MUST have the purchase dates, or actually, the days or fraction of year(s) invested.

Many here use Excel's IRR function, but I like to see the guts behind the formulas, so I calculate returns based on the # of days a stock is held, convert this to a percentage of year(s) held, and divide the return by this number, giving CAGR. In the end, it all comes out the same.

Example:

\$100 invested on 7/01/04.
Worth \$142 on 12/1/06.
Gain = \$42.
Invested days = 894 days (calc in Excel as: =<calc.date>*-<purch. date>)
<days>/<days in year> = years, giving us 894/365 = 2.44193 years
42% gain / 2.44193 years = 17.2% = CAGR (on 12/1/06)

*Calc.date is usually today's date [=today()] in excel, but we are using 12/1/06, because if someone reads this next week or next year, TODAY() is different each day a reader visits. Phew!

When my CAGR gets north of 30%, maybe I won't care either, but I hope I still do. :) In the meantime, I think it is worth at least a 10-point quiz to learn the math behind the entire method.

At the end of the year, as Jim would probably agree, "Your CAGR is what your CAGR is" no matter how you calculate it. But if we don't really know what our CAGR is, where do we go from there?

Hope that helps,

Dan
No. of Recommendations: 0
Dan,

This brings up an interesting point as to when should one sell. If you get quick short-term gains, calculating the CAGR based on the time you've owned the stock would result in a high CAGR. Sell and congratulate yourself on the high CAGR?

If you hold on to the stock and the price stays flat, your CAGR will drop on that stock as time becomes a more important factor.

In looking at my historical data, I see that I have had some enormous short-term CAGRs and other not so great long-term CAGRs.

I guess, I'll go back and read the posts on when to sell BMW stocks.

LNT
No. of Recommendations: 0
thanks for the explanation Dan. Now when I try to test your numbers, things dont seem to match up. So hopefully you can show me where I'm making my mistake.

7/01/04 - \$100
7/01/05 - (\$100*117.2%)=\$117.20
7/01/06 - (\$117.20*117.2%)=\$137.3584
12/1/06 - (\$137.3584*117.2%*.44193)=\$147.799

Now in this case am I doing the last step (the partial year) incorrectly? I know this is getting tedious, but I really like to understand what I am calculating, and not just rely on some equaton that is spitting out some number.

Also, say you purchased another \$100 on 8/01/05, would you incorporate this into your calculation to get an average CAGR for the investment, or would you simply track the additional purchase seperately?

Eric
No. of Recommendations: 5
Hi Dan
I don't want to pop any balloons, but I think your calculations give flattering CAGR.

\$100 invested on 7/01/04.
Worth \$142 on 12/1/06.
Gain = \$42.
Invested days = 894 days (calc in Excel as: =<calc.date>*-<purch. date>)
<days>/<days in year> = years, giving us 894/365 = 2.44193 years
42% gain / 2.44193 years = 17.2% = CAGR (on 12/1/06)

I do the same in Excel like this:
CAGR = (Ending \$ / Starting \$)^1/n – 1 n= years
Example =(G5/C5)^(1/((B\$49-F5)/365))-1
G5 = current price
C5 = Starting price
B\$49 = current date =TODAY()
F5 = purchased date

=(142/100)^(1/((E176-C176)/365))-1
Where E176 is your end date and C176 is your start date

The result is 15.6%

The difference is in the compounding and that little hat symbol ^ takes care of the compounding.

On the positive side your holding of less than a year will have much higher CAGR than you're currently figuring.
For example 13 days ago (OMTR) and has a gain of 20.7% and a CAGR of 629.6%
That has a CAGR of 19,583% I kid you not. You can prove this by multiplying a number by your 20.7% return 28.077 times , ie 365/13
Of course in one weeks time if there is no price change the CAGR plummets down to 3000%

Yes, I am a sad individual who likes numbers.

Cheers
Dean
No. of Recommendations: 0
I do the same in Excel like this:
CAGR = (Ending \$ / Starting \$)^1/n – 1 n= years
Example =(G5/C5)^(1/((B\$49-F5)/365))-1
G5 = current price
C5 = Starting price
B\$49 = current date =TODAY()
F5 = purchased date

=(142/100)^(1/((E176-C176)/365))-1
Where E176 is your end date and C176 is your start date

The result is 15.6%

The difference is in the compounding and that little hat symbol ^ takes care of the compounding.
(emphasis mine)

*************************************************************

Yeah, what he said.

For multiple purchases you'll also need to do weighted averaging of each position's CAGR if you want overall CAGR on that stock. I find that the easiest way to do this in Excel is to take the sumproduct of the #shares column and the CAGR column and then divide this by the sum of the #shares. Like this…

______A__________B
1____#sh_______CAGR%
2____100_______12.5
3____150_______14.0
4____ 75_______18.2

Overall CAGR = sumproduct(A2:A4,B2:B4)/sum(A2:A4)= 14.5

Now if you want your portfolio's true CAGR I still recommend the NAV method which properly accounts for the timevalue of cash in/out - especially if the deposits are of varying values and/or periods. Once NAV is determined CAGR is simply calculated on the NAV number over the period of interest as Dean describes above. I do it annually (although I sometimes can't help performing a quarterly updates).

Tom
No. of Recommendations: 1
I guess, I'll go back and read the posts on when to sell BMW stocks.

LNT

Your past CAGR does not matter because there is nothing you can do about it. On the other hand, you can influence your future CAGR and that is what should guide your trading. :)

Denny Schlesinger
No. of Recommendations: 0

Rate (IRR) 15.446%

Date Value Future Value

Dec 31, 2005 10,000,00 11,544.60
Apr 1, 2006 4,000,00 4,455.40
Dec 31, 2006 -16,000,00 -16,000.00

Total 0.00

Denny Schlesinger
No. of Recommendations: 2
I don't think anyone has quite gotten it right about how you calculate one CAGR for multiple purchases. The answer is that there is no simple formula, it is a process of trial and error, or if you want to use jargon, iteration.

The idea is that for each purchase, the amount grows by the same CAGR until today. So using the formula for one purchase, guess a CAGR and compute the value it would have today if it had grown at that CAGR. Do this for all purchases using the same CAGR. Then add up all the predicted present values and compare with the true value. If it is too high, then pick a lower CAGR, compute the present values for all purchases and compare again. Rinse and repeat until you have a CAGR that produces a present value that matches the actual current value.

This is what Excel does in its XIRR function:
Microsoft Excel uses an iterative technique for calculating XIRR. Using a changing rate (starting with guess), XIRR cycles through the calculation until the result is accurate within 0.000001 percent.

Why is this what you want? For a single purchase, the CAGR is the answer to the following question: If the rate of gains was constant instead of fluctuating, what constant gain rate would give the same final value as the actual history did? For multiple purchases, the CAGR calculated in this manner answers the same question.

This is different than calculating the CAGR for each purchase and averaging them.
No. of Recommendations: 0
The idea is that for each purchase, the amount grows by the same CAGR until today. So using the formula for one purchase, guess a CAGR and compute the value it would have today if it had grown at that CAGR. Do this for all purchases using the same CAGR. Then add up all the predicted present values and compare with the true value. If it is too high, then pick a lower CAGR, compute the present values for all purchases and compare again. Rinse and repeat until you have a CAGR that produces a present value that matches the actual current value.

spinning

Exactly!

http://boards.fool.com/Message.asp?mid=24928421

Denny Schlesinger
No. of Recommendations: 0
Thanks, you just help me to prove that Quicken is calculating my CAGR correctly. I entered your hypothetical buy/sell example into Quicken as a dummy stock. Then I asked Quicken to tell me the CAGR. It came out to 15.58% which rounds to your 15.6%

For those of you seeking a shorter, quicker route, let Quicken maintain your investments and it will tell you your CAGR for one stock, all stocks, or whatever group of stocks you want. I assume Money would do the same.

ToroBravo2003
No. of Recommendations: 0

Hi all,

Good catch. You're right, my formula is also a shortcut and is not 100% accurate beyond 1 year. (That should tell you how long my long-term focus is.)

Once and for all, let's go straight to the horse's mouth, ok? Please note the bolded (by me, not original) statement that states, in effect "CAGR is an imaginary number." That is why calculating true CAGR requires (many) reiterrations (guess/refine/repeat with new result,repeat,repeat...)

From Investopedia:

Compound Annual Growth Rate - CAGR

The year-over-year growth rate of an investment over a specified period of time.

The compound annual growth rate is calculated by taking the nth root of the total percentage growth rate, where n is the number of years in the period being considered.

This can be written as follows:

CAGR =
{Ending Value / Beginning Value)^( 1/# of years) -1
* [This is hard to write in our discussion board format. See the article. The formula is actually a graphcis image and thus will not insert here at all.]

CAGR isn't the actual return in reality. It's an imaginary number that describes the rate at which an investment would have grown if it grew at a steady rate. You can think of CAGR as a way to smooth out the returns.

Don't worry if this concept is still fuzzy to you - CAGR is one of those terms best defined by example. Suppose you invested \$10,000 in a portfolio on Jan 1, 2005. Let's say by Jan 1, 2006, your portfolio had grown to \$13,000, then \$14,000 by 2007, and finally ended up at \$19,500 by 2008.

Your CAGR would be the ratio of your ending value to beginning value (\$19,500 / \$10,000 = 1.95) raised to the power of 1/3 (since 1/# of years = 1/3), then subtracting 1 from the resulting number:

1.95 raised to 1/3 power = 1.2493. (This could be written as 1.95^0.3333).
1.2493 - 1 = 0.2493
Another way of writing 0.2493 is 24.93%.

Thus, your CAGR for your three-year investment is equal to 24.93%, representing the smoothed annualized gain you earned over your investment time horizon.

http://www.investopedia.com/terms/c/cagr.asp

Sorry for the confusion,

Dan
No. of Recommendations: 0
RaptorD said, "Don't worry if this concept is still fuzzy to you - CAGR is one of those terms best defined by example. Suppose you invested \$10,000 in a portfolio on Jan 1, 2005. Let's say by Jan 1, 2006, your portfolio had grown to \$13,000, then \$14,000 by 2007, and finally ended up at \$19,500 by 2008...your CAGR for your three-year investment is equal to 24.93%, representing the smoothed annualized gain you earned over your investment time horizon."

Look, Raptor D, you have just screwed up the BMW Method. Why would you accept a 30% gain in 2005, then a 10% gain in 2006 and a 39.3% gain in 2007? What was wrong in 2006? I think that 30% in 2005 was fine, but the goal is to beat yourself at this game. 2006 should have been better than 2005...maybe 34%. Then the 39.3% in 2007 is respectable.

So, the numbers look like this. \$10,000 in 2005 goes to 13,000. \$13,000 goes to 17,420 and that goes to \$24,266 in 2007. The CAGR is 34.38%...on average. That one bad year cost you almost 10% in lost CAGR! You are better than that! Now, get to work!
No. of Recommendations: 0

"You are better than that! Now, get to work! "

LOL, Jim. I would settle for 34.38% on average if I HAD to. But the example is straight out of Investopedia; I take no credit, nor CAGR. Besides, I would have tried to hold in 2006 like you LTBH BIG BOYS.

OK, I admit it--I probably would have sold,

Dan
No. of Recommendations: 1

Jim, I just noticed: "39.3% in 2007 is respectable."

RESPECTABLE? Wow, you need to come over to Raptor HQ and have a Rum 'n' Coke, and we can discuss what I'm doing wrong. You're teaching. I'm buying.

Dan
No. of Recommendations: 0

Hi L,

Unfortunately, your question is a great one, and my answer is not. Maybe a 90-day period of flat is a flag to check into a sale?

REALLY good question. Varying CAGR. Hmmm...

Sorr, not much help.

Dan
No. of Recommendations: 0
The idea is that for each purchase, the amount grows by the same CAGR until today. So using the formula for one purchase, guess a CAGR and compute the value it would have today if it had grown at that CAGR. Do this for all purchases using the same CAGR. Then add up all the predicted present values and compare with the true value. If it is too high, then pick a lower CAGR, compute the present values for all purchases and compare again. Rinse and repeat until you have a CAGR that produces a present value that matches the actual current value.

This is what Excel does in its XIRR function:
Microsoft Excel uses an iterative technique for calculating XIRR. Using a changing rate (starting with guess), XIRR cycles through the calculation until the result is accurate within 0.000001 percent.

I'm feeling a little sheepish asking this, but how exactly would one use the XIRR function to calculate CAGR of a stock position? I've stared at it and can't figure out how to correctly enter the data. So say, just hypothetically now, that I've been buying a stock that's rocketing downward (cough, DRL, cough) and I want to use XIRR to calculate the CAGR of following data set:

Date Price Qty
10/04/06 \$5.69 6
10/24/06 \$4.70 7
11/20/06 \$4.16 7
12/06/06 \$3.48 9

I know that the number will be really large given the extremely short timespan, but I'd appreciate knowing how to correctly use this function. I'd like to compare it with the results based on the [(V2/V1)^(1/n)]-1 formula I'm currently using.

Thanks.

Tom
No. of Recommendations: 1
Dan,

Going by the CAGR by itself is not enough. Right now I've got some huge CAGRs because I made a hundred bucks in a few days. A hundred bucks is not enough of a real percentage gain to sell immediately.

I like your idea of a 90-day period to check on the sale. I've still got some \$100 gains that I have owned for 500 days. I don't even want to calculate the CAGR on that. This really puts the time value of money in a different light.

LNT
No. of Recommendations: 1

C D
-34.14 10/4/2006
-32.9 10/24/2006
-29.12 11/20/2006
-31.32 12/6/2006
87.29 12/13/2006

then in another cell =XIRR(C1:C5,D1:D5)
the last row is the total value of those shares today. I think this is right. The first four rows are the cost of your shares not counting comission.
No. of Recommendations: 0
Thanks, Noob! I'll try that when I get home.

Tom