Value Investing Spreadsheet

Even a casual observer of the markets understands that the stock market moves both up and down—often in strong, but short bursts. Investors looking to commit funds to the market may be paralyzed with the fear of investing a significant sum of money just prior to a severe market downturn. However, by standing on the sidelines, investors lose out on participating in the long-term superior returns of the stock market. The issue is one of market timing, and no matter what the currently popular market prognosticator says, it is practically impossible to consistently call market tops and bottoms.

Dollar cost averaging and its variations, such as value averaging, offer investors an alternative to the all-or-nothing approach, allowing them to ease into the market over time, which reduces the timing risk. The mechanical aspects of averaging provide an investment discipline, require no market forecasts, and are relatively simple to initiate.

The Concept

Dollar cost averaging is simple in concept: Invest a fixed amount at equal intervals and continue to do so over a long period. The result is that more shares of a stock or mutual fund are purchased when prices are relatively low, and fewer shares are purchased when prices are relatively high. This can result in a lower average cost per share over time.

Value averaging is a variation: Instead of investing a fixed dollar amount each interval, the amount invested varies so that the total value of the investment increases by a fixed sum or percentage each interval. If share price increases alone cause the total value of the investment to increase above the planned fixed amount, then the investor must sell shares instead of adding to the investment.

Neither approach requires a forecast of market direction. And with both plans, the discipline of periodic investment during all market situations and the continuation of the plan over long investment periods—of five years, 10 years, 20 years, or even longer—provides substantial benefits, not the least of which is simply getting started in an investment program in the first place.

Investors who should use dollar cost averaging or value averaging include:

• Any investor with a pool of cash or periodic cash flows who seeks to invest in a risky asset, who has a long-term investment horizon, and who feels that he or she cannot forecast short-term move in the market.
• Any investor who is having difficulty finding the right moment to move into the market (or back into the market if he or she was temporarily out of the market).

One key to the successful use of an averaging approach is to choose an appropriate long-term horizon. In order to avoid the potential disaster of placing a substantial portion of your portfolio in risky investments at the high point of a market cycle, take a minimum of two years, investing monthly or quarterly, to complete the move into the market. Five years is an ideal period, albeit a bit too long for many impatient investors.

Those investors without a significant pool of cash currently available but who instead have cash periodically available, are spared the temptation of rushing a large sum into the market all at once. These investors are already structured for dollar cost averaging, but without a plan, they may never start an investment program.

Another consideration is the frequency of the investments. Any periodic interval could be used and, of course, any amount or value. Investing often enough over a uniform time interval is important, and every quarter, two months, or month is reasonable. Investing weekly, however, is probably overkill, while waiting every six months or every year to invest is too infrequent and may defeat the benefits of diversifying the investments over time in an ever-changing market.

How They Work: An Example

Tables 1 and 2 show examples of dollar cost averaging and its more market-tuned cousin, value averaging, illustrating the structure of each investment plan and highlighting the differences. The investments in the example are the Vanguard 500 Index mutual fund (Table 1), a broad-based large-cap stock index fund, and Janus (Table 2), a growth mutual fund; the time period covered is the last five?and?a?half calendar years, January 1996 through June 2001; the investment frequency is quarterly. These two averaging approaches could be used to invest in individual stocks as well.

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

The examples use a $3,000 initial investment coupled with an $1,000 quarterly contribution for the dollar cost averaging approach: $1,000 is invested each quarter at the prevailing price of the security.

For the value averaging approach, the same $3,000 initial investment along with a $1,000 quarterly increase in value is used: The amount invested quarterly varies such that the total value of the investment increases by $1,000 each quarter; if the share price rises enough to cause the total value of the investment to increase by more than $1,000 during the quarter, shares would be sold to hold the increase in value to $1,000 for the period. For example, in the fourth quarter of 1998, Janus jumped from a net asset value of $26.85 to $33.65 (Table 2). To keep the increase in value to $1,000, the following calculations must be made: At the end of the quarter, the investor held 521.415 shares with a net asset value of $33.65 before any changes, so the value of the portfolio would have been $17,545.61 (521.415 x $33.65), an increase of $3,546, or $2,546 more than the planned $1,000 increase. Therefore, 75.65 shares ($2,545.61 divided by $33.65, rounded) would have to be sold.

While dollar cost averaging is unchanging, value averaging forces sales when prices rise sharply and forces larger purchases—more shares purchased—when prices fall. For example (Table 1), in September 1998 the share price of the Vanguard 500 Index fund fell to $94.56 from $105.30 the previous quarter. That resulted in the need for a $2,326 investment under the value averaging approach. Many value averaging investments during 2000 and 2001 are significantly above the $1,000 investment increase goals for both the Vanguard 500 Index Fund and Janus Fund.

The End Results

Under the value averaging approach the ending amount is known, but the total amount to be invested isn’t when you start the investment program. In our example, the portfolio would grow to $25,000 over the five?and?a?half years. A total of $18,927 was invested in the Vanguard 500 Index fund to meet the goal, while $23,408 needed to be invested in the Janus Fund to make the portfolio grow to $25,000. Again it should be noted that we did not reinvest distributions in our example.

Under the dollar cost averaging approach, the total value at the end of the period could be any value, but the total amount invested can be determined. Our $25,000 investment in the Vanguard 500 Index Fund went to $31,925, while it actually contracted to $24,482 for the Janus Fund example. When you start the dollar cost averaging program, the amount to be invested is known, but the ending amount isn’t.

Keep in mind that the goal of value averaging is to increase the portfolio by a fixed amount each period, and it may take substantial total amounts invested to do so, conceivably much more or much less in total than the certain dollar cost averaging sum.

Which approach 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 no sales take place, with future value increases adjusted to compensate. Also, the loss potential is greater for value averaging because the total amount that is 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 calculation that takes into consideration all the cash flows and their timing. These returns are given at the bottom of each example.

Value Averaging Spreadsheet

Dollar cost averaging is a very simple approach: An individual invests a fixed amount of money at regular time intervals. The value averaging approach is more aggressive and tuned to the market because it forces you to invest more money when the market turns down and the total value of your holding decreases. When the value of your holding goes up, you invest less money buying the higher priced shares and potentially even selling shares. Because the amount of money you need to invest will change every period with a value averaging strategy, a spreadsheet is a handy tool to calculate the periodic investment amount.

One criticism of value averaging is the forced sale of shares. Unless your investment is in a tax-sheltered account, you may be forced to pay capital gains taxes earlier than planned. Therefore, the 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 our value averaging spreadsheet. The spreadsheet can be downloaded either from the “Files from AAII” or “Spreadsheets” section of the AAII.com Download Library (www.aaii.com/dloads/). The Nasdaq-100 Trust QQQ is used as an example in the spreadsheet. To use the spreadsheet, you would first enter the starting investment amount in cell A5 and the dollar amount of the desired change in cell A6. Allowing for two separate entries is handy because some funds require a higher investment initially than for subsequent purchases. Two entries also allow you to apply a value averaging purchase or sales plan to an already existing investment. To do this, simply input the current value of your holding in cell A5 and the portfolio change amount in cell A6. If you wish to use the spreadsheet to average out of a security, enter a negative value in cell A6.

Cell A7 is where you indicate if you can purchase or sell fractional shares. Sales or purchases are normally done in whole share increments for stocks, while mutual funds can usually be purchased or sold using fractional shares. Enter a 1 in cell A7 if you want to work with fractional share amounts, or 0 if you cannot. The message in cell B8 confirms your selection.

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

Column A lists the date of each rebalancing. You can use any time period desired—simply input the values in column A. Column B calculates the desired value for each time period, and column C is where you input the net asset value or share price of the security you are tracking.

Column D allows you to enter any share amounts that you might have acquired, or even sold, since you last rebalanced the 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 in the amount of shares you expected to acquire or sell when you instructed your fund or broker compared to the quantity actually transacted. Small differences would be expected because of the time lags.

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 holding before the current rebalancing—multiplying the total number of shares owned in column E times the net asset value 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. If you specified that you do not wish to sell any shares, a zero will appear in this column when your holding goes above your desired amount. Column H calculates the number of shares you will buy or sell to rebalance, and column I estimates the number of shares you will own after the rebalancing. Column J keeps a running total of the total invested in the security.

The spreadsheet is set up so, as more rows are needed over time, you can take any row at row 18 or below and copy it down the spreadsheet as far as desired.

Dollar cost averaging and value averaging provide a clear path for investors to follow. With the pathway marked, taking the first few steps of an investment plan should be that much easier. Both dollar cost averaging and value averaging attempt to reduce one of the largest fears of risk-averse investors—moving a large sum of money into the market just before a severe market decline.