The Motley Fool: The Foolish Four

<FOOLISH FOUR PORTFOLIO>

by Ann Coleman (TMF [email protected])

Reston, VA (March 17, 1999) -- If there's a magic phrase in investing, it's "annualized returns." Like EPS for businesses, annualized returns are the investor's bottom line.

As we discussed a couple of weeks ago, the Compound Annual Growth Rate (CAGR) or Average Annual Return (AAR) for your portfolio is the percentage rate at which your investment grows over time. You might want to review that column if you are shaky on the math for the CAGR, because today we are going to learn to use it in a spreadsheet where it really shines. CAGR on steroids!

So far, the spreadsheet that we have been developing this week just does a simple total return calculation for each stock and the portfolio as a whole, which is fine for your first year. But Foolish investors should be thinking of their investments in terms of decades, so today we will set up the formula to calculate your returns on an annualized basis. Warning -- watch out for the return when it is for short periods. A sudden jump in stock price early on can make the annualized rate look good -- a little too good.

First, you have to understand what we are doing. We are going to calculate the total return, which is simple enough (Current Value/$Invested), then we are going to take a root -- but instead of a nice simple square root or cube root or 25th root, we are going to take the root that corresponds to the time we've been invested, whether it's two days or thirty-five years.

For our Foolish Four portfolio, that time period began on December 24th, and right now it happens to be 83 days. Hang in there -- this isn't as bad as it looks at first. Eighty-three days is 83/365.25ths of a year. That's one heck of a fraction. (We are using 365.25 because a year is actually 365 and 1/4 days long -- remember leap year. We're letting the leap seconds pass.)

To take the 83/365.25th root, you raise your total return to a power of 365.25/83 (Get it? You inverted the fraction), which gives us a formula of (drum roll, please):

=((G7+H7)/F7)^(365.25/83)-1

Notice all those parentheses? Never, never fail to notice the parentheses! They tell Excel how to group the math and are extremely important. They can even be important when they are empty. In the formula above, Excel wouldn't know whether to divide the Dividends (H7) by the $Invested (F7) and add that to the Current Value (G7) or add the Dividends to the Current Value and divide that by the $Invested -- which is what you want it to do -- without those parentheses. Likewise, it wouldn't know if you were raising to a power of 365.25 and then dividing by 83 or raising to the power of the fraction 365.25/83. The answers are rather different. You gotta watch those parentheses.

Obviously this gives us the annualized growth rate as of today. Tomorrow, you will have to change your fraction to 365.25/84. What a pain! There ought to be an easier way, and there is, but it is probably spreadsheet specific. Those of you who don't have Excel can try this, but you will probably have to consult you your help files, or just count the days and change the fraction when you want an annualized growth rate.

In Excel you can substitute a time function to automatically calculate the correct fraction for the current day.

Excel uses a simplified dating system that starts with January 1, 1900, and counts Day 1, Day 2, Day 3, etc. By the time it gets to December 24th of last year, the day we bought our Foolish Four stocks, it was up to Day 36153. Today is Day 36236. (By the end of the century it will be up around 36525 -- 100 times the number of days in a year.) Excel also has a function that returns today's "date" for any particular instant in time. You just type =NOW(). (Notice the empty parentheses? I told you they were important!) So the fraction 365.25/83 can also be written 365.25/NOW()-36153. Changing our formula to incorporate that gives us a formula that will provide the CAGR every day for the next several thousand years. (See, it was worth it!)

Here's the full formula:

=(($G7+H7)/$F7)^(365.25/(NOW()-36153))-1

I hear you. You are asking, what if I didn't start on December 24th? Excel will give you its simplified date for any day from January 1, 1900 through December 31, 9999. You just have to ask correctly. Excel doesn't exactly spell the rules out, however. (Note to Editor: That is why I was so late!) Find a cell, any cell, and type =DATEVALUE("x/x/19xx"). Keep the quotes but use your date. If you get back a date, go to the format menu and format that cell as Number/General.

By this method, our Foolish Four is up 8.05% since inception and is growing at an annualized rate of 40.2%. Whoo-Hoo!

I think we are all about ready for a break from spreadsheets. But I have a parting "gift." If you want to see the spreadsheet I have been using for this demonstration, click here and you can download it directly to your computer. It is in Excel 5.0 format (only) so you will need Excel 5.0 or higher, or a spreadsheet that will convert an Excel file, to run it.

Fool on and prosper!

Today's Stock Lists | 1998 Dow Returns

03/17/99 Close

Stock Change Last -------------------- CAT +3 1/4 47.25 JPM -2 1/8 121.50 MMM -2 3/16 76.38 IP + 13/16 44.44

Day Month Year History FOOL-4 +1.08% 5.29% 6.36% 7.94% DJIA -0.51% 6.16% 7.99% 7.56% S&P 500 -0.65% 4.80% 5.90% 6.15% NASDAQ -0.42% 6.16% 10.78% 12.29% Rec'd # Security In At Now Change 12/24/98 9 JP Morgan 105.51 121.50 15.15% 12/24/98 24 Caterpillar 43.08 47.25 9.68% 12/24/98 14 3M 73.57 76.38 3.81% 12/24/98 22 Int'l Paper 43.55 44.44 2.04% Rec'd # Security In At Value Change 12/24/98 9 JP Morgan 949.62 1093.50 $143.88 12/24/98 24 Caterpillar 1034.00 1134.00 $100.00 12/24/98 14 3M 1030.00 1069.25 $39.25 12/24/98 22 Int'l Paper 958.12 977.63 $19.51 Dividends Received $15.04 Cash $28.26 TOTAL $4317.68

</FOOLISH FOUR PORTFOLIO>