The
Dow Approach
The
Dow Approach
| Dow Dividend Strategy | What's Here | The Statistics Center | Spin-Off Help | The Archives | |||||||||||||||||||||||
FOOL GLOBAL WIRE LEXINGTON, KY. (Mar. 7, 1997) -- Among our most firmly held beliefs in Fooldom is the notion that investors should see the stock market as a long-term savings vehicle, and that we should view ourselves perhaps as savers even before investors. Another firmly held belief is total accountability, tracking real returns including all costs against the market indices used as benchmarks by Wall Street professionals. There's a problem putting these two beliefs next to each other, however. It's pretty difficult to calculate an accurate return if you regularly add to or withdraw money from your portfolio. Short of buying a commercial portfolio management package, there's not a simple way to figure out what financial types call the Internal Rate of Return. Or so I thought until recently. Our spreadsheet guru, MF Sargon, taught me how to use one of Microsoft Excel's function wizards that will calculate this for you in a snap. But it takes a few minutes to set up the first time. So for those of you who don't consider yourself among the spreadsheet elite (I'm with you), here's a quick and dirty guide to using the spreadsheet function you need. The problem, of course, is that when you add or withdraw money from your portfolio, the base amount you use to compare your returns changes. For instance, if you start with $25,000 and add $5,000 six months later, you can't use either $25,000 or $30,000 as your original value. One would under-state your return; the other would over-state it. You need a time-weighted return that considers how long the added money was invested to get an accurate return. The function you need to locate in Excel is called XIRR. In many of your spreadsheets it won't show up in the default function list. If that's the case (as it was in mine), you need to look under the Tools menu and select Add-Ins. Make sure the feature called Analysis ToolPak is turned on. That should add the XIRR function to your master list. To use the function you need to set up two columns, one using the dates of each of your additions or withdrawals from the portfolio, the other listing the amount. For a reason the financial analysts understand but I'm not sure I can explain quickly, you should list your cash additions as negative numbers and your withdrawals as positive numbers. Then list your final portfolio value as if you were withdrawing the whole amount (a positive number). What you're really calculating is the rate of return for a series of cashflows, but the net result is accurate for our purposes. For example, let's assume you started 1996 with $25,000, then added $5,000 on Feb 15, another $10,000 on July 16, and $3,000 on November 23. At the end of the year, your portfolio was worth $50,000. This is how the two columns would look in your spreadsheet: 1/1/96 -25,000 2/15/96 -5,000 7/16/96 -10,000 11/23/96 -3,000 12/31/96 50,000 In the cell where you want to calculate the return, select the XIRR function and you'll see three arrays you can specify. The second column of the spreadsheet you've set up is what you include in the Values array and the dates, of course, go in the Dates array. Simply leave the Guess array blank. Using the numbers in my example, the return equals 0.2059 (or 20.59%). This return will always be calculated on an annualized basis, so you can use this for any time period. If you're calculating a period shorter than a year, though, you must take that annualized return and scale it back to the period you're measuring. In a very simple spreadsheet, then, you can simply record each deposit or withdrawal for your entire investment career and then test the actual return for any period you select. For investors who add money regularly, this is crucial to get an accurate measure portfolio returns. If you have any questions using this function, you can drop me a note (although keep in mind I'm no Excel expert), or check with Sargon (he is). Hope it helps you track your own portfolios more easily. Fool on!
(c) Copyright 1997, The Motley Fool. All rights reserved. This material is for personal use only. Republication and redissemination, including posting to news groups, is expressly prohibited without the prior written consent of The Motley Fool. |
|
||||||||||||||||||||||
|
|||||||||||||||||||||||
|
