Value Averaging Spreadsheet

by AAII Staff

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.

...To continue reading this article you must be a Computerized Investing Subscriber.

Gain exclusive access to this article and all of the benefits and investment education a Computerized Investing subscription offers.

Log inAlready a CI subscriber? Login to read the rest of this article.

SubscribeA subscription to Computerized Investing includes a monthly email and access to the CI Website, all of which aim to benefit your investing skills with respect to computers and the Internet.

Discussion

Joseph from NY posted over 3 years ago:

The Value Averaging Spreadsheet appears to be what I needed, so I downloaded it. I want to value average into a mutual fund over the next 20+ months. The initial investment was $3000 dollars and subsequent investments are set at the fund's $1000 minimum investment. Because the NAV of the fund is high, this months amount to invest calls for a dollar amount of $125.00; since this month's amount to invest is well below the $1000 minimum the fund requires how do I handle this? Do I ignore the spreadsheet's amounts to invest that are below the funds minimum or do I just forget using this spreadsheet?

Dave from WA posted over 3 years ago:

Joseph,
To make this spreadsheet work with a mutual fund investment that has a high minimum like $1000, I would suggest that you set "Dollar amount to increase" in cell A6 to at least 2 times the min. investment or in your case $2000. Though with value investing you can not predict how long it will take to become "fully invested" in your case you could increase the investment interval to one and a half months instead of the original one month. When you get numbers that are less than $1000, I would suggest if the number is > than $500 then go ahead and invest $1000. If less than $500 then invest zero for that time frame.

Dave from WA posted over 3 years ago:

One problem with this spreadsheet is that if you add anything to column D, it will tend to overstate your IRR calculation. To correct this you would need to add a column to capture the purchase price of the acquired shares and add that to the calculation of the "periodic investment" in column K.

john from OH posted over 2 years ago:

the spreadsheet works for small amount of investments. How can I use the spreadsheet if say my starting investment was $200,000 and I wanted to invest $2000 a month using a total stock market index fund as an investment vehicle? Should I define my expected portfolio value as an addition of $2000/month i.e 202000,204000,206000..... or should I use a certain expected rate of annual return say 6% or the historical stock market return of 9% ?

Zachariah Tripp from NH posted over 2 years ago:

I have been value averaging in my 401k for some time. I have my direct deposits go into the money market and even month I move to correct amount to the funds based on my value path. Last month (Jan-2013) I took profits in 3 of 5 funds. Felt good to have a plan and know when the value is above average so I can take profits and buy more when the return is below average.

John from Ohio, If I was you, I would make you own spreadsheet, it is not too difficult. Find the average monthly return of the fund in question and put together a excel file by month with that return and your monthly addition, that will be your path. Make installations accordingly.