.
I bet it used to be time-consuming to manually calculate returns other than at year
end. Even now that computers run our lives, there are only so many ways to
calculate compound returns for investors. Outside of using a website that offers
portfolio services—and by risking the accompanying lack of privacy involved—
there are a few built-in spreadsheet functions that can help us track growth.
One is the function XIRR. I know several people who claim that XIRR does not
work. I used to be one of them, but it was just 2 years ago I think, that I finally got
a huge spreadsheet to track Compound CAGR accurately using XIRR.
XIRR is useful to track returns in accounts that have many cash transactions,
meaning deposits, withdrawals and payments made and received, while tracking
the returns of income along with them. Considering that most of us don’t have a
real use for all the requirements of XIRR, let’s leave that for the few who can
benefit from it to learn by studying the function on their own or from someone
with considerable experience with it. The restrictions include a strict requirement
that all transactions be grouped sequentially and with no gaps, and for all dates
to be in the same column, and the same for cash balance recording. The cash
records for XIRR also require all cash inputs to be negative and outlays to be
positive, not exactly intuitive. Anyway, if we need it, it’s available but very, very
tricky IMHO…
More valuable to the majority of investors is a simple function that provides
accurate returns (Capital Appreciation Growth Rate, or CAGR) for any
investment where we know the starting and ending dates of the period
considered, and the investment’s value at those dates. With the CAGR formula,
we can calculate last year’s returns, the year before, or cumulative returns from
01/01/88-06/06/2017.
The formula for year-end returns is actually a special case version of the CAGR
formula. To calculate our returns in at year end we use the formula Return =
YearEndValue/BeginYearValue-1. It is a special case because one side
of the CAGR formula becomes greatly simplified. To calculate CAGR for a
period other than 1 year, or for a multiple-year period, we must of course
introduce a factor for time. It just so happens that the case for simple one-year
returns simplifies the equation. Not to worry, it isn’t that complicated; we just
have to watch where we are pointing to our variables and follow some simple
rules.
Return = YearEndValue/BeginYearValue-1.
=================================
CAGR=(Cost/CurrentValue)^(1/YEARS)-1
=================================
So we can see what happens when YEARS=1; the formula becomes simplified—
Simplified right into our formula for 1-year returns.
For a custom period, the only additional variable we need is an exact calculation
to determine the portion of a year, or years, our desired period to calculate
returns for, represents. Some people simply use Days/365, but using another
simple spreadsheet function eliminates several problems and choices which can
be confusing. When we talk of CAGR, we aren’t estimating, we aren’t assuming
anything, we are saying that our Capital Appreciation Growth Rate, annualized, is
13.7%, and anything you want to compare to it will be apples to apples,
regardless of US or European calendar rules, regardless of Leap Years,
whatever. This is the answer, period.
So the simple function for YEARS in our formula is YEARFRAC. (Clever name,
eh?) It looks like this:
YEARFRAC(StartDate,EndDate,3) where “3” is “basis,” a variable choice that determines
days in a year, US, or European calendar and accounts for Leap Years, etc. The subtleties are not worth our
discussing here; I recommend we simply use “3”. If you must know why, please
see the Help function in Excel or Google Docs for the YEARFRAC function.
Now our CAGR formula has changed somewhat, but we still need the same 4 variables.
CAGR=(Cost/CurrentValue)^(1/YEARFRAC(StartDate,EndDate)-1.
Note 1: While the formula is simple, the structure is rather rigid. The
parentheses and the sequence of variables must be precise. You will see what I
mean when you point to the dates in reverse order some day or miss a
parentheses somewhere. Often the mistake will produce a number that “looks”
approximately right but will make us look like fools to people who make their own
CAGR calculations (and there are lots of them) or worse, misguide us in our
investment choices.
Note 2: Variables in any formula must have a name. There are no perfect names
for the variables in CAGR because it is flexible—“Cost”, for example doesn’t
have to be Cost. It could be BeginValue, it could be 4-27-14Value, it could be …
anything. I only point this out so we don’t get mired down in thinking that the only
times we can calculate CAGR are at the beginnings and endings of weeks,
months, years, decades, etc. They can be any dates for which we have
corresponding investment values. For equity investing, this means we can
make our calculations for any period when we have the value twice—at the start
(not necessarily “Cost” at all) and at the end of the period. We could call them Go
and Stop. Or First and Last.
If you’ve followed this far, you deserve a reward. If you will open a blank
worksheet, we can build a template so you will always have the formula, and you
can plug in (or point to) values in any document or spreadsheet in the future.
The Boss has given us brokerage statements for 8 years, from 2009-2016 and to
5/31/17. We’ve been asked to figure CAGR for each of the 8 years, cumulative
for that period, and also CAGR for the period from 1/1/17-5/31/17.
Starting in cell A1, enter the following constants in columns A & B, and the
formulae in columns C & D until your table looks like the following:
(Note: Before the formulae in C2 &D2, type an apostrophe, so the formula
appears as text so we can check them. We won’t see the apostrophe.) Also
note we need an entry for 2008 (not in our desired period) because we need a
start value for 2009 and the start of one year can be the end of the previous
year. Lastly, for reference, in cell F1, type the formula for CAGR, format font as
Blue, Bold and 2 pts larger than the rest of your sheet. Right under that, also for
reference type the formula for YEARFRAC and paint with the same format as
above, Blue,Bold,+2pts
CAGR=(CurrentPrice/Cost)^(1/YEARS)-1
YEARFRAC(StartDate,EndDate,3)
When the constants are filled in, in C3, type ‘=(B3/B2)^(A2,A3,3))-1
(Don’t forget the apostrophe!)
In column C, yes, we could use the “year-end shortcut” method, but we’re here to
learn for real life, any situation required, right? So we’ll use the same CAGR
formula in columns C&D as shown. I told you it was versatile. 
Note that cell C2
is blank, and note the placement of the last (right) parentheses—it is NOT at the
end.
When you’re satisfied that C3 is correct, copy that cell, then move to D3 and
the formula.
**Date Value CAGR Cumulative CAGR** **CAGR=(CurrentPrice/Cost)^(1/YEARS)-1**
1/1/2008 17,341.00 < blank > < blank > **YEARFRAC(StartDate,EndDate,3)**
1/1/2009 15,680.00 =(B3/B2)^(1/YEARFRAC(A2,A3,3))-1 =(B3/B$2)^(1/YEARFRAC(A$2,A3,3))-1
1/1/2010 16,389.00
1/1/2011 17,905.00
1/1/2012 21,430.00
1/1/2013 23,417.00
1/1/2014 26,554.00
1/1/2015 28,139.00
1/1/2016 30,117.00
1/1/2017 32,434.00
5/31/2017 34,540.00
In cell D3, <add “$” in front of both “2”’s ‘=(B3/B$2)^(1/YEARFRAC(A$2,A3,3))-1
(The $’s tie each line of the Cumulative CAGR back to the first date and the First
Value.)
When you’re satisfied both formula look like they should, in each of the 2
cells, (delete the leading apostrophe so the formula
is active)
Next, C3:D3. <%> (and to 1 decimal point)
C4:D12
Almost done. Now we just need to take care of one of those pesky rules we must
follow for this to work.
Important: Without the last step, your project will be cursed by the 0CAGR
Goddess of ShopPhobia AND the IRS, and don’t think I’m kidding. Do not
skip this last step.
-----> ----- >
Voila’! CAGR
**Date Value CAGR Cumul CAGR**
1/1/2008 17,341.00 <blank> <blank>
1/1/2009 15,680.00 -9.6% -9.6%
1/1/2010 16,389.00 4.5% -2.8%
1/1/2011 17,905.00 9.3% 1.1%
1/1/2012 21,430.00 19.7% 5.4%
1/1/2013 23,417.00 9.2% 6.2%
1/1/2014 26,554.00 13.4% 7.4%
1/1/2015 28,139.00 6.0% 7.2%
1/1/2016 30,117.00 7.0% 7.1%
1/1/2017 32,434.00 7.7% 7.2%
5/31/2017 34,540.00 16.5% 7.6%
Very Well Done.
Interesting CAGR Fact: *
Investors often think that if we ever lose 50%, it’s best to do it in our early years,
the years when our portfolios are small, than at the end when we’re pressing
toward those millions (well, not me, but some are). But it isn’t true. 50% is 50%
and if we lose it the first year, our wouldabeen $2m jackpot at the end will still be
sliced down to $1m.
The sequence of gains and losses does not matter. If we lose 50% in
Year 1, and in the next 4 years gain varying amounts our cumulative CAGR will
be the same if we reverse the order:
Lose 50%, gain 10%, gain 14%, Gain 3% = Gain 3%, gain 14%, gain 10%, lose 50%
So here we 1) find another reason to follow Rule #1. Never lose money and
possibly 2) dispel a misunderstanding about the rule’s source. See, I’m certain
this is part of what my neighbor, a certain Warren E. Buffett, meant when he
preached following Rule 1–not what many people think—that you should never
lose money on any investment. The person who never loses never invests;
maybe he’s extremely risk adverse, who knows. Maybe he’s a Trust Fund Baby.
Or maybe he just outright lies. Unless we read it on the internet. Everything we
read on the internet is the Truth. “Including this CAGR discussion?” you ask.
Sure, you betcha!
But no TFB here. I’ve lost; Been there, Done that, got the t- … well, actually
lost many a shirt.
Dan