The Motley Fool: The Foolish Four, Friday, February 27, 1998

<THE FOOLISH FOUR>

Foolish Four Report
by Robert Sheard

LEXINGTON, KY. (Feb. 27, 1998) -- By now you've probably heard the embarrassing announcement that the Beardstown Ladies incorrectly calculated their famous market-beating returns and are having to scramble to determine just how well their investment club portfolio has really done.

Like most investment clubs, the Beardstown Ladies save new money regularly and add to their portfolio over time. But instead of using the standard accounting practice of assigning a unit value (much like the mutual fund industry must do with their Net Asset Values), the Beardstown Ladies apparently inadvertently included their regular cash deposits as part of their returns, which, of course, inflated their return calculations.

I've no intention of flaming the Beardstown Ladies, because despite this oversight, they've done a great deal to bring the message to the general public that the individual investor can go it alone (or in small groups) without the help of the Wall Street Wise.

But the lesson out there for all of us is the need to understand how the math works when you add new money regularly to your portfolio. Back in October, I wrote a two-part column called "Motley Math." It includes several of the formulas you'll need to calculate everything from the simple gain or loss on a single stock to the much more complicated situation where one adds new money every month and must calculate the Internal Rate of Return.

I'll excerpt the section on the Internal Rate of Return here, but if you want the full 2-part series, please visit the Daily Dow articles from October 9 and 10, 1997:

To calculate the annualized return for the investor who adds money to her portfolio every month, or occasionally on no particular schedule, you need either a good financial calculator or software package, or a spreadsheet program that includes an Internal Rate of Return function. I use Microsoft Excel, and the best function I've discovered is the one listed as XIRR. (To find this function, since it's not listed in the primary list of functions in Excel, go to the Add-Ins option under the Tools Menu. From the list that pops up there, check the box next to Analysis ToolPak. Then the XIRR function should show up in the Financial category of your functions list.)

Let's set the scene. Wilma opens her brokerage account with $5,000 on January 1, 1994. Then every three months, she adds an additional $1,500. On October 10, 1997, her total portfolio value is $43,248.83. Given that she's deposited money on sixteen different occasions, how can she calculate an annualized return for her overall portfolio? She can't simply add up the total of her deposits and use that as the Original Value figure because each amount has been invested for a different length of time. So she must use an Internal Rate of Return calculation that accounts for the time value of each deposit.

If you're using the XIRR function in Excel, here's how to set up the simple two-column spreadsheet. In column one, list the dates of each cash addition or withdrawal from the account. (Don't include dividend or interest payments in this list. Those aren't additional deposits you've made, even if you're reinvesting them in more shares, but rather, they're part of the natural growth of the account. Only list money you've added or taken from the account directly.)

In the second column, list the amounts deposited or withdrawn on those dates. Be sure to input your withdrawals as negative numbers. Then on the final line of the spreadsheet, list today's date and the total value now. Input this total value as a negative number, as if you were withdrawing the entire account today.

  1/1/94    5,000.00  
   4/1/94    1,500.00  
   7/1/94    1,500.00  
  10/1/94    1,500.00  
   1/1/95    1,500.00  
   4/1/95    1,500.00  
   7/1/95    1,500.00  
  10/1/95    1,500.00  
   1/1/96    1,500.00  
   4/1/96    1,500.00  
   7/1/96    1,500.00  
  10/1/96    1,500.00  
   1/1/97    1,500.00  
   4/1/97    1,500.00  
   7/1/97    1,500.00  
  10/1/97    1,500.00  
 10/10/97  (43,248.83) 
 XIRR         21.86%

Then in an empty cell (it doesn't matter which cell you use), click the function button and select the XIRR entry from the Financial category. The Function Wizard will pop up and ask you to enter three variables. The first one asks for the cells you used for the values. Select the cells in your second column where you input the dollar amounts Values (for example, B1:B17). The second variable asks for the Dates. Input the cells where you listed the dates in column one (A1:A17). The third variable is called Guess, and you can simply leave that one empty. Click Finish and the calculated value will show up as a decimal figure. (In this case, it shows up as 0.2186.) You can either make the conversion to a percentage yourself by multiplying 0.2186 by 100, or format that cell to print out as a percentage.

Voila. That's it. Wilma's annualized return on her portfolio has been 21.86% since the beginning of 1994. For those investors who save regularly and add to their portfolios again and again, this is a crucial calculation to get an accurate picture of your returns. Each month you can add another line to your columns to account for new deposits and then you just need to update the final date and portfolio amount. The spreadsheet will immediately calculate an updated annualized return. Without such an Internal Rate of Return function, all you could do to get a sense of your overall return would be to try to track each separate savings deposit as a separate portfolio... an absolute nightmare, I think you'll agree.

This is the process the Beardstown Ladies should have used if they opted not to set a floating unit value like many investment clubs do. Check into it when making your own calculations so you're sure of getting an accurate picture of your real performance. Fool on!

[Want to be the first Fool on your block to get a copy of Robert Sheard's forthcoming book? Click here to pre-order your copy of The Unemotional Investor.]

TODAY'S NUMBERS

Stock Change Last -------------------- FOOL-4 +1.10% 5.15% DJIA +0.65% 8.06% S&P 500 +0.06% 8.13% NASDAQ -0.37% 12.74%

Day Month Year FOOL-4 +1.10% 3.23% 5.15% DJIA +0.65% 8.08% 8.06% S&P 500 +0.06% 7.04% 8.13% NASDAQ -0.37% 9.33% 12.74% Rec'd # Security In At Now Change 12/31/97 206 Eastman Ko 60.56 65.63 8.36% 12/31/97 291 Union Carb 42.94 46.44 8.15% 12/31/97 289 Int'l Pape 43.13 46.63 8.12% 12/31/97 276 Philip Mor 45.25 43.44 -4.01% Rec'd # Security In At Value Change 12/31/97 206 Eastman Ko 12475.88 13518.75 $1042.88 12/31/97 291 Union Carb 12494.81 13513.31 $1018.50 12/31/97 289 Int'l Pape 12463.13 13474.63 $1011.50 12/31/97 276 Philip Mor 12489.00 11988.75 -$500.25 CASH $77.19 TOTAL $52572.63