May 26, 2011, 10:06 a.m.
posted by handcore
Calculate Investment Return Over Different Time Periods
By calculating the compound annual return you earned over different lengths of time, you can determine your portfolio performance.
If you own mutual funds, you're probably familiar with returns calculated for different periods. Mutual funds typically publish their returns [Hack #63] for the most recent one-year, three-year, five-year, and ten-year compound annual returns, and will publish their compound annual return since the inception of the fund if it has existed for longer than ten years. For mutual funds, the long-term returns are more important, because funds can run hot and cold in shorter periods. Individual investors and investment clubs can also use compound annual return calculations to see how they're doing. However, you must understand internal rate of return calculations to evaluate your performance in a meaningful way.
1 Using Internal Rate of Return to Calculate Performance
For mutual funds or your portfolio, long-term performance is usually more revealing than short-term returns. Long-term results can indicate how well an investment or portfolio performs through good times and bad. Although long-term performance still does not guarantee future results, you can forecast similar returns for the future with more confidence. On the other hand, short-term performance can be misleading, and the distortion increases as the timeframe shortens.
The compound annual returns you want to calculate are also known as the internal rate of return, which is described in the introduction to this chapter. It takes some time to adjust to internal rate of return, so if you haven't read the introduction, now's the time to do so. The calculation of internal rate of return takes into account any cash that you contribute to or withdraw from a portfolio, and when those cash flows occur. The first step is to determine what is a cash flow for this type of calculation.
Suppose you have a portfolio that you seeded with $10,000 five years ago, and you have conscientiously deposited your birthday money every year since. In this scenario, your first cash flow in is the $10,000 that you deposited. The annual birthday deposits are also cash flows in. If you withdrew any money from the account, each withdrawal would be a cash flow out.
Now, how about the stock dividends that you receive and reinvest in more shares of stock? Reinvested dividends are not cash flows for an internal rate of return calculation, because the money for the reinvested dividends doesn't come out of your wallet. Reinvested dividends are just like the interest a savings account pays. The interest you receive and the subsequent compounding on that interest increases the yield on your savings account.
The following identify other investment events and whether those events generate portfolio cash flows:
- Unreinvested dividends in a portfolio
If you deposit unreinvested dividends into the money market fund associated with your portfolio's brokerage account, the money doesn't leave your portfolio, so it isn't a cash flow. However, if you withdraw the dividends you receive, that withdrawal is a cash flow for the purposes of the portfolio IRR calculation.
- Unreinvested dividends for a single stock
If you don't reinvest the dividends for a single stock, the money leaves that investment and becomes a cash flow for the IRR calculation for that stock.
- Switching investments in a portfolio
For the IRR for a portfolio, changing the investments you use doesn't generate cash flows, because the money remains in the portfolio. Selling an investment and keeping the proceeds in the portfolio money market also does not generate a cash flow for a portfolio.
The spreadsheet in Figure shows three internal rate of return calculations for a five-year-old portfolio. Column A lists cash flow dates, including the initial date, the birthday deposit dates, and one date at the end of each year for the calculation of the return. Because the Excel XIRR function works with an array of cells such as B2 through B4, the cash flows for each period that you want to evaluate appear in their own columns.
For the first year (2000), column B shows three cash flows in, which are negative numbers. Cell B2 represents the opening balance for the account. Cell B3 is the birthday deposit. Cell B4 is the value of the account at the end of the first year. The cash flows in are negative because they represent cash coming out of your wallet and into the portfolio. The ending value must be a positive number (for a cash flow out) for the IRR calculation to work, although the money isn't actually coming out of your portfolio.
Cell G4 shows the IRR for 2000. The IRR calculation produces a 47 percent annual compound return. That's fantastic! However, closer inspection says otherwise. Because the initial contribution occurred in June of 2000, this return represents an annual return based on a portfolio in existence for only six months. The portfolio did increase in that six months by almost $3,000, but the IRR calculation extrapolates the return to a full year. In effect, it calculates the return as if you received a similar increase during an additional six-month period.
Column C includes the cash flows for years 2000 and 2001, which cover the first 18 months for the portfolio. As you can see in Figure, the IRR for the first 18 months is dramatically lower than that of the first 6 months (25 percent compared to 47 percent), even though the portfolio increased by a similar amount. Finally, column D includes all the cash flows from the initial deposit to the most recent balance.
IRR calculated for different timeframes
The Excel functions for calculating IRR for different timeframes are shown in Figure. The XIRR function accepts two parameters: the first parameter is a range of cells that contain the cash flows and the second parameter is a range of cells for the dates on which those cash flows occurred. For example, the cash flows for the first six months are in column B, so the first parameter for the one-year XIRR is B2:B4. The dates for those cash flows are in column A, so the second parameter is the range A2:A4.
The Excel functions for IRR
IRR over 6 months (cell E4) = XIRR(B2:B4,A$2:A$4) IRR over 18 months (cell E6) = XIRR(C2:C6,A$2:A$6) IRR over 5 years(cell E12) = XIRR(D2:D12,A$2:A$12)
Calculating the internal rate of return for a series of cash flows requires an iterative approach. In effect, you must try different answers until you get the correct one, which happens to be the interest rate that sets the present worth of all the cash flows to zero. You really don't need to understand what that means. However, by default, XIRR uses 10 percent as its guess. If XIRR can't find an answer that is accurate to 0.000001 percent after 100 iterations, it displays #NUM! in the formula cell. When this occurs, enter a different interest rate guess.
The spreadsheet in Figure shows that the compound annual return for a short period of time can be misleading. Longer-term results that cover the ups and downs of the business cycle are more indicative of your performance.