Spreadsheet: 1c4ebcaa-4d04-458a-9841-9b40052f74bd
Sheet: Sheet1

Value Averaging Spreadsheet
By AAII Staff
Second Quarter 2011 Computerized Investing
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, [NAME] 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 [NAME], former AAII president, and [NAME], 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.
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.
The Value Averaging Spreadsheet tab in this worksheet uses
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.
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.
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 [NAME] 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 [NAME]). This value is used to
calculate the internal rate of return in cell [NAME] 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.
[NAME], former president of AAII, [NAME], president of AAII, and
[NAME], CFA, editor of Computerized Investing, contributed to this article.