Motley Math Continued
[Ed Note: This article was originally published on Oct. 10, 1997, as a Foolish Four column.]
by Robert Sheard
(TMF Sheard)
LEXINGTON, KY. (Aug. 4, 1998) -- In yesterday's column, I outlined some simple plans for projecting the future growth of one's portfolio using a spreadsheet. Today, I want to wrap up our Motley Math lesson with some equations you can use (either on paper or with a spreadsheet) to track the real performance of your portfolio.
The simplest equation is one used to measure the total return of either an individual stock or an entire portfolio.
Current Value - Original Value
------------------------------ = Total Return
Original Value
For example, Fred invested $2,014 (including his commission) in his favorite blue-chip stock. When he sold it last week, he received $2,683 (after the commission). What percentage profit did Fred make?
$2,683 - $2,014
--------------- = 0.3322 (or 33.22%)
$2,014
The advantage of this equation is that you can use your total portfolio values to calculate the growth and you've already accounted for all the brokerage costs of trading. It's a completely accountable measure of your portfolio's progress.
The drawback, however, is that it doesn't account for time. If Fred's 33% gain occurred in nine months, for example, that's a fine return. But if it took him four years to achieve that return, it's pretty weak.
To add time to the picture, as you might guess, the equation becomes a little more complicated. You might want to use a spreadsheet or a calculator that can handle fractional roots here. But don't panic, it's not as hard as it sounds.
(Current Val. / Original Val.) ^ (1 / No. of Years) - 1
= Compound Annual Growth Rate
This Compound Annual Growth Rate (or CAGR) formula includes the element of time to calculate your annualized return. This is the return you'd have to achieve each year in a fixed-return investment to end up with the same result. The caret (^) denotes an exponent. If that's not clear to you, let's work through an example with real numbers.
Barney's portfolio at the beginning of 1993 was worth $25,000. Four years later, at the beginning of 1997, it was worth $63,000. What was his annualized return?
($63,000 / $25,000) ^ (1 / 4) - 1 =
2.52 ^ 0.25 - 1 =
(This means you must raise 2.52 to the 0.25 power, a fractional exponent. If you're using a spreadsheet, you can type in the formula just as I have here, with the caret symbol. If you're doing this on your calculator, consult the owner's manual for how to input fractional exponents).
1.2599 - 1 = 0.2599 (or 25.99% per year)
The two equations we've seen so far are relatively easy to use, even if you have to do the calculations manually. But both calculations have the limitation of only allowing for a single deposit at the beginning of the period. What about the investor who adds money to her portfolio every month, or occasionally on no particular schedule?
For that calculation, 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.
I hope these samples help you with the maintenance and accountability of your own portfolio, Fools. At a glance, you should know at all times how your long-term performance stacks up to the market indices and the basic Dow Dividend Approach. Fool on!
Check out the latest file updates for the Workshop:
New Rankings
| 1998 Returns
| New Database
[Robert Sheard is the author of the The Unemotional Investor (Simon & Schuster, 1998) available now at Amazon.com and your local bookseller.]