<FOOLISH FOUR PORTFOLIO>
Final Word on Spreadsheets
And a correction
by Ann Coleman
(TMF [email protected])
Alexandria, VA (March 19, 1999) -- Uncle!
I give up. I can't send any more spreadsheets out the old fashioned way (as e-mail).
I really blew it Wednesday. I offered to send out copies of the sample spreadsheet I'd been using as we discussed tracking investments on Monday, Tuesday, and Wednesday. I'm afraid I quit answering my mail about noon yesterday when tendonitis set in. Instead, we have set up a spot on the web from which you can download the file.
990319_FOOLISH4.xls
and you can download it immediately.
The only excuse I have for such amazing lack of foresight is that I really didn't think very many people would want it. But, hey, you guys are really into this stuff! I'm proud of you. Hundreds and hundreds of you!
Even if you were one of the early ones and received the spreadsheet I sent out yesterday, you may want to download this one. It has a bit more explanatory stuff, and I added formulas for the CAGR of each stock. Plus the formula for the portfolio CAGR has been corrected.
Alert reader STJolly777 of AOL pointed out (so nicely!) that the CAGR formula for the portfolio as a whole counted dividends twice. In a way I'm glad this happened, because one thing I felt I was leaving out of our spreadsheet discussion was a warning on the perils of spreadsheets. Wonderful as they are, they aren't always smart. Sometimes they can be too helpful -- like a toddler who puts your sneakers in the dishwasher for you because they are dirty.
What happened with the CAGR formula is that I set it up to add the dividends to the current value of the investments. That, of course, is the way to do it most of the time. Later I took a look at how the spreadsheet was set up and decided that the dividend total should be included in the account total, so I dragged that cell to the Current Value column.
Because I just dragged it over there, the spreadsheet, ever helpful, changed the formula to include the new location instead of the old one. However, the dividends were now being added to the account total, which was also in the formula. So the formula took the account total, which included dividends, and added the dividends to it. Sneaky. And definitely something you want to watch out for when you move stuff around.
Another alert reader, Tim Lutz, pointed out that there is an easier way to do the time calculations. This is rather embarrassing, because it is so much simpler. Rather than calculating the date that you started in "Excel Days" as I described in depth on Wednesday, you can simply reference the cell that has the date in it. The new spreadsheet uses the expression (Now()-A1), where A1 can be any cell with a date in it, to calculate how many days it has been from then to now. This makes the CAGR formula much simpler.
Thanks to both Steve and Tim.
I should probably also warn you that the learning process for spreadsheets can include some frustrating times. Fortunately, we have a message board set up to help get you through those rough spots: Spreadsheet Advice. I will check in there from time to time and offer as much help as I can. Don't be afraid to ask simple questions -- those are the ones I can answer.
Fool on and prosper
Today's Stock Lists | 1998 Dow Returns
03/19/99
Close
Stock Change Last -------------------- CAT --- 46.81 JPM -1 1/16 123.13 MMM -2 1/8 71.50 IP + 1/2 46.06 |
Day Month Year History
FOOL-4 -0.66% 4.60% 5.66% 7.23%
DJIA -0.94% 6.41% 8.25% 7.82%
S&P 500 -1.31% 4.92% 6.02% 6.27%
NASDAQ -1.68% 5.83% 10.43% 11.95%
Rec'd # Security In At Now Change
12/24/98 9 JP Morgan 105.51 123.13 16.70%
12/24/98 24 Caterpillar 43.08 46.81 8.66%
12/24/98 22 Int'l Paper 43.55 46.06 5.77%
12/24/98 14 3M 73.57 71.50 -2.81%
Rec'd # Security In At Value Change
12/24/98 9 JP Morgan 949.62 1108.13 $158.51
12/24/98 24 Caterpillar 1034.00 1123.50 $89.50
12/24/98 22 Int'l Paper 958.12 1013.38 $55.26
12/24/98 14 3M 1030.00 1001.00 -$29.00
Dividends Received $15.04
Cash $28.26
TOTAL $4289.30
</FOOLISH FOUR PORTFOLIO> |