Value Averaging Spreadsheet

While the goal of most investors is to “buy low and sell high,” some of us have the uncanny knack of doing just the opposite—buying at the very peak and selling at the very bottom. The market moves up and down, and very few investors have demonstrated the ability to consistently predict where it is headed over a long period of time. For someone looking to commit a large amount of money to the market, the specter of another market correction can be a disturbing thought. However, history has shown that sitting on the sidelines can be even more destructive, as we miss out on the superior long-term returns of the stock market.

One way to counteract the fluctuations of the market, thereby reducing timing risk, is to follow a “formula strategy” that “mechanically” guides your investing. Perhaps the best-known formula plan is dollar cost averaging, whereby you invest a fixed dollar amount in an asset at equal intervals over a long period. As a result, more shares of a stock or mutual fund are purchased when prices are relatively low, while fewer shares are purchased when prices are relatively high. Over time, this strategy can lead to a lower average per-share cost, which, in turn, increases the rate of return.

In the August 1988 AAII Journal, Michael Edleson introduced an alternate concept to dollar cost averaging called value averaging. Instead of investing a fixed dollar amount each period, you set the value of your investment holding to increase by a fixed amount or percentage each period. If share price increases alone cause the total value of your investment to increase above the planned periodic fixed increase amount, you must sell shares instead of adding to the investment. This investment accumulation strategy, which Edleson expands upon in his book “Value Averaging: The Safe and Easy Strategy for Higher Investment Returns” (John Wiley & Sons, 2006), is more flexible than dollar cost averaging, has a lower per-share purchase cost, and tends to have a higher rate of return.

In this installment of Spreadsheet Corner, we revisit a value averaging spreadsheet developed in the July/August 2001 issue of CI by John Markese, former AAII president, and John Bajkowski, AAII president and former editor of Computerized Investing.

Dollar Cost Averaging Versus Value Averaging

Compared to dollar cost averaging, value averaging is a more aggressive approach because it forces you to invest more money when the market is falling and the total value of your holdings is decreasing. When the value of your holdings goes up, you invest less money buying the higher-priced shares, and there is the potential that you may need to sell shares.

Choosing an appropriate long-term time horizon is key to successfully implementing an averaging strategy. Choosing a longer horizon will help you avoid the potential disaster of investing a substantial portion of your portfolio in the market at its high point. At a minimum, take two years—investing monthly or quarterly—to complete your move into the market. More patient investors may choose a longer period, perhaps as long as five years.

Investors who do not already have a significant pool of cash but do have cash periodically available are spared the temptation of rushing into the market all at once. While such investors are perfectly positioned for an averaging strategy, they may never start an investment program without a system such as this.

Lastly, the frequency of your investments must be taken into consideration. Investing often enough over a uniform time interval is important. Quarterly or monthly investments are reasonable. Investing more frequently, such as weekly, is probably overkill, while investing less often is too infrequent and possibly defeats the benefits of diversifying over time in an ever-changing market.

Comparing the Strategies

Table 1 compares dollar cost averaging to value averaging, illustrating the structure of each investment plan and highlighting their differences. We used the PowerShares QQQ exchange-traded fund (QQQQ), which tracks the NASDAQ-100 index, a listing of the 100 largest non-financial stocks listed on the NASDAQ Stock Market. The time period covered is the last 24 months, March 2, 2009, through March 1, 2011; the investment frequency is monthly. Keep in mind that these averaging techniques can be used to invest in individual stocks and closed-end mutual funds as well.

We ignored dividend and capital gains distributions to simplify the presentation, but for investors the reinvestment of all dividends and distributions should be part of any investment plan.

Table 1 uses a $1,000 initial investment coupled with a $500 monthly contribution for the dollar cost averaging approach: $500 is invested on the first trading day of each month at the prevailing price of the exchange-traded fund (ETF).

For the value averaging approach, the same $1,000 initial investment is used along with a $500 monthly increase in value: The amount actually invested each month varies such that the total value of our investment increases by $500 each month; if the share price increases enough to cause the total value of our holdings to increase by more than $500 during the month, we would sell shares to hold the increase in value to $500 for the period. For example, in July 2010 QQQQ shares jumped in price from $42.54 to $46.51. To keep the increase in value for the month to $500, the following calculations must be made: At the beginning of August, before any changes were made to the portfolio, the investor held 211.57 shares of QQQQ at a price of $46.51 per share. Between July 1, 2010, and August 2, 2010, the price increased $3.97 per share ($46.51 – $42.54) or roughly $840, $340 more than the planned $500 increase for the month. Therefore, 7.308 shares ($339.93 divided by $46.51) need to be sold.

While dollar cost averaging is constant and unchanging, value averaging forces sales when prices rise sharply and forces larger purchases—more shares purchased—when prices fall. For example, between May 3, 2010, and June 1, 2010, QQQQ shares fell from $49.74 to $45.50. This resulted in the need for a $1,182 investment under the value averaging approach.

With value averaging, when you first begin the program, the ending investment value is known, but the total investment amount isn’t. In our example in Table 1, the portfolio grew to $12,500 over 24 months, while a total of $8,527 was invested in the PowerShares QQQ ETF. Again, note that we did not reinvest distributions in this example.

Under the dollar cost averaging approach, we knew that the total amount invested over 24 months would be $12,500. This investment in QQQQ shares grew to $17,136 over 24 months. When you start a dollar cost averaging program, the amount you will invest is known, but the ending investment value is not.

Remember that the goal of value averaging is to increase your portfolio by a fixed amount each period, and it may take substantial investments to do so, conceivably much more than or much less than those demanded by the dollar cost averaging total.

Which Method Works Best?

While either approach could dominate over any time period, value averaging probably has the edge because it is more aggressive. However, value averaging requires more monitoring, more transactions costs and, because it triggers sales, potentially more tax consequences. Value averaging can be modified so that no sales take place, with future value increases adjusted to compensate. Also, the loss potential is greater for value averaging because the amount required to be invested is unconstrained.

Please note that you cannot judge which approach did best in the examples simply by looking at ending portfolio values because the amounts invested and the timing of the investments differ for the two approaches. The calculation to determine performance is called an internal rate of return (IRR), which takes into consideration all the cash flows and their timing. From Table 1, we see that both dollar cost averaging and value averaging yield lower average cost per share values than the average monthly price of QQQQ shares over the test period. However, value averaging has a lower per-share cost—$38.71 versus $41.40—as well as a higher internal rate of return—36.1% compared to 33.6%.

Value Averaging Spreadsheet

Since the amount of money you need to invest with a value averaging strategy will change every period depending on the price movement in the security, a spreadsheet is a useful tool for calculating the periodic investment amount.

While Edleson views the potential for forced sales as an advantage of value averaging, others view it in a negative light. Unless your investment is in a tax-sheltered account, you may be forced to pay capital gains taxes earlier than you otherwise had planned. Our value averaging spreadsheet allows you to set whether or not you wish to sell shares when the value of your fund increases beyond the desired amount.

Figures 1 and 2 present the Value Averaging Spreadsheet. It is also available for download from the AAII.com Download Library from both the “Files from AAII” and “Spreadsheets” sections. We use the Vanguard Total Stock Market ETF (VTI) as an example in the spreadsheet. To use the spreadsheet, you first enter the initial investment amount in cell A5 and the dollar amount by which you want your investment to grow each period in cell A6. Allowing for two separate entries is useful since some funds require a higher initial investment than is required for subsequent purchases. The existence of two entries also allows you to apply a value averaging plan to an existing investment. To do this, you would input the current value of your holding in cell A5 and the desired periodic change amount in cell A6. If you wish to use value averaging to exit a position over time, enter a negative value in cell A6.

Cell A7 is where you indicate whether you can purchase or sell fractional shares. Sales or purchases are normally done in whole increments for stock transactions, while mutual funds can usually be purchased or sold using fractional shares. Enter a “1” in cell A7 if you wish to deal with fractional shares, or a “0” if you do not. The message in cell B8 confirms your selection.

Figure 1. Value Averaging Spreadsheet With No Selling of Shares

To download the Excel Spreadsheet, click here.

Cell A9 is where you indicate if you wish to sell shares when your portfolio increases beyond the desired amount in a period. Enter “1” in cell A9 if you wish to sell shares; enter “0” if you do not wish to sell on those occasions. As confirmation, a formula in cell B10 will report how the spreadsheet calculates the reinvestment amount.

Column A lists the date of each rebalancing. You can use any time period you want—simply input the dates in column A. Column B automatically calculates the desired value of your holdings for each time period based on the values you enter in cells A5 and A6. Column C is where you input the net asset value or share price of the security.

Column D allows you to enter any share amounts that you may have acquired, or sold, since the last time you rebalanced your portfolio. You would use this column to input any shares acquired through dividend reinvestment. Column D is also where you can adjust for any difference between the number of shares you instructed your fund or broker to buy or sell and the quantity actually transacted. Small differences are not uncommon because of the time lag.

Figure 2. Value Averaging Spreadsheet With Selling of Shares

To download the Excel Spreadsheet, click here.

Column E sums the total number of shares from the last rebalancing and accounts for any differences entered in column D. Column F computes the total value of your holdings before the current rebalancing—multiplying the total number of shares reported in column E by the net asset value or share price in column C. Column G compares the current value of your holdings to the desired value and calculates how much money you need to invest or withdraw for the period. If you specified in cell A9 that you do not wish to sell any shares, a zero will appear in column G when your holdings go above the desired amount. Column H calculates the number of shares you need to buy or sell to rebalance, and column I estimates the number of shares you own after rebalancing. Column J keeps a running total of the amount you have invested in the security.

Looking at Figures 1 and 2, we see that the average share price of VTI over the 25-month period is $53.64 (Cell C44). By comparison, the average share cost when we did not sell shares for rebalancing is $50.18 (Figure 1, cell J44) and $50.24 when we were selling shares for rebalancing (Figure 2, cell J44). Row 42 in both Figures 1 and 2 shows the share price of VTI one month after our final rebalancing (cell C42), along with the final portfolio value of $25,457 (cell J42). This value is used to calculate the internal rate of return in cell J45 using the XIRR function in Excel. The internal rate of return on an investment is defined as the “annualized effective compounded return rate.” Specifically, the IRR of an investment is the interest rate at which the net present value of costs (negative cash flows) of the investment equals the net present value of the benefits (positive cash flows) of the investment. This calculation uses the periodic cash flows over the 25-month investment period as well as the final portfolio value (which is viewed as a cash inflow at the end of the averaging period) along with the dates of these cash flows from Column A. It is important that the values in Column A are formatted as dates and not text, otherwise the XIRR function will not work.

For a full listing of the underlying formulas used in this value averaging spreadsheet, you can refer to the online version of this article at ComputerizedInvesting.com or download the Value Averaging Spreadsheet from the AAII Download Library.

Conclusion

Dollar cost averaging and value averaging provide investors with a clearly defined investment plan. Having the path laid out before you should make the first steps much easier. Both averaging strategies attempt to reduce one of the biggest fears faced by investors—investing a large sum of money into the market prior to a severe market downturn.

Click here to download spreadsheet.