.

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