value investing spreadsheet

7/30/2019 Value Investing Spreadsheet

1/6

2July/August 2001

FEATUREFEATUREFEATUREFEATUREFEATURECICICICICI Implementing a Value Averaging StrategyImplementing a Value Averaging Strategy

Implementing a Value Averaging StrategyImplementing a Value Averaging StrategyImplementing a Value Averaging Strategyby John Bajkowski and John Markese

Even a casual observer of the mar-kets understands that the stock mar-

ket moves both up and downoftenin strong, but short bursts. Investorslooking to commit funds to the mar-ket may be paralyzed with the fear ofinvesting a significant sum of moneyjust prior to a severe market down-turn. However, by standing on thesidelines, investors lose out on partici-pating in the long-term superior re-turns of the stock market. The issue isone of market timing, and no matterwhat the currently popular marketprognosticator says, it is practically

impossible to consistently call markettops and bottoms.

Dollar cost averaging and its varia-tions, such as value averaging, offerinvestors an alternative to the all-or-nothing approach, allowing them toease into the market over time, whichreduces the timing risk. The mechani-cal aspects of averaging provide aninvestment discipline, require no mar-ket forecasts, and are relatively simpleto initiate.

The ConceptThe ConceptThe ConceptThe ConceptThe ConceptDollar cost averaging is simple in

concept: Invest a fixed amount at equalintervals and continue to do so over along period. The result is that moreshares of a stock or mutual fund arepurchased when prices are relativelylow, and fewer shares are purchasedwhen prices are relatively high. Thiscan result in a lower average cost pershare over time.

Value averaging is a variation: In-stead of investing a fixed dollaramount each interval, the amount in-vested varies so that the total value ofthe investment increases by a fixedsum or percentage each interval. Ifshare price increases alone cause thetotal value of the investment to in-crease above the planned fixedamount, then the investor must sellshares instead of adding to the invest-ment.

Neither approach requires a fore-cast of market direction. And with both

plans, the discipline of periodic in-vestment during all market situationsand the continuation of the plan overlong investment periodsof fiveyears, 10 years, 20 years, or evenlongerprovides substantial benefits,not the least of which is simply gettingstarted in an investment program inthe first place.

Investors who should use dollar costaveraging or value averaging include: Any investor with a pool of cash

or periodic cash flows who seeks

to invest in a risky asset, who hasa long-term investment horizon,and who feels that he or she can-not forecast short-term move inthe market.

Any investor who is having diffi-culty finding the right moment tomove into the market (or back intothe market if he or she was tempo-rarily out of the market).

One key to the successful use of anaveraging approach is to choose anappropriate long-term horizon. In or-

der to avoid the potential disaster ofplacing a substantial portion of yourportfolio in risky investments at thehigh point of a market cycle, take aminimum of two years, investingmonthly or quarterly, to complete themove into the market. Five years is anideal period, albeit a bit too long formany impatient investors.

Those investors without a signifi-cant pool of cash currently availablebut who instead have cash periodi-cally available, are spared the tempta-tion of rushing a large sum into themarket all at once. These investors arealready structured for dollar cost av-eraging, but without a plan, they maynever start an investment program.

Another consideration is the fre-quency of the investments. Any peri-odic interval could be used and, ofcourse, any amount or value. Invest-ing often enough over a uniform time

interval is important, and every quarter, two months, or month is reason

able. Investing weekly, however, iprobably overkill, while waiting every six months or every year to invesis too infrequent and may defeat thebenefits of diversifying the investments over time in an ever-changingmarket.

How They Work: An ExampleHow They Work: An ExampleHow They Work: An ExampleHow They Work: An ExampleHow They Work: An Example

Tables 1 and 2 show examples odollar cost averaging and its moremarket-tuned cousin, value averagingillustrating the structure of each in

vestment plan and highlighting thedifferences. The investments in theexample are the Vanguard 500 Indexmutual fund (Table 1), a broad-basedlarge-cap stock index fund, and Janu(Table 2), a growth mutual fund; thetime period covered is the lasfive-and-a-half calendar years, January 1996 through June 2001; the investment frequency is quarterly. Thesetwo averaging approaches could beused to invest in individual stocks awell.

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 cosaveraging approach: $1,000 is investedeach quarter at the prevailing price othe security.

For the value averaging approachthe same $3,000 initial investmenalong with a $1,000 quarterly increasein value is used: The amount investedquarterly varies such that the totavalue of the investment increases by$1,000 each quarter; if the share pricerises enough to cause the total value othe investment to increase by morethan $1,000 during the quarter, sharewould be sold to hold the increase in


2/6

C22

value to $1,000 for the period. For ex-ample, in the fourth quarter of 1998,Janus jumped from a net asset value of$26.85 to $33.65 (Table 2). To keep theincrease in value to $1,000, the follow-ing calculations must be made: At theend of the quarter, the investor held

521.415 shares with a net asset valueof $33.65 before any changes, so thevalue of the portfolio would have been$17,545.61 (521.415 x $33.65), an in-crease of $3,546, or $2,546 more thanthe planned $1,000 increase. There-fore, 75.65 shares ($2,545.61 dividedby $33.65, rounded) would have to besold.

While dollar cost averaging is un-changing, value averaging forces saleswhen prices rise sharply and forceslarger purchasesmore shares pur-

chasedwhen prices fall. For example

(Table 1), in September 1998 the shareprice of the Vanguard 500 Index fundfell to $94.56 from $105.30 the previ-ous quarter. That resulted in the needfor a $2,326 investment under the valueaveraging approach. Many value av-eraging investments during 2000 and

2001 are significantly above the $1,000investment increase goals for both theVanguard 500 Index Fund and JanusFund.

The End ResultsThe End ResultsThe End ResultsThe End ResultsThe End Results

Under the value averaging approachthe ending amount is known, but thetotal amount to be invested isnt whenyou start the investment program. Inour example, the portfolio would growto $25,000 over the five-and-a-halfyears. A total of $18,927 was invested

in the Vanguard 500 Index fund to

meet the goal, while $23,408 needed tobe invested in the Janus Fund to makethe portfolio grow to $25,000. Again ishould be noted that we did not reinvest distributions in our example.

Under the dollar cost averaging approach, the total value at the end o

the period could be any value, but thetotal amount invested can be determined. Our $25,000 investment in theVanguard 500 Index Fund went to$31,925, while it actually contracted to$24,482 for the Janus Fund exampleWhen you start the dollar cost averaging program, the amount to be invested is known, but the endingamount isnt.

Keep in mind that the goal of valueaveraging is to increase the portfolioby a fixed amount each period, and i

may take substantial total amount

Dollar Cost Averaging ($3,000 initialDollar Cost Averaging ($3,000 initialDollar Cost Averaging ($3,000 initialDollar Cost Averaging ($3,000 initialDollar Cost Averaging ($3,000 initial Value Averaging ($3,000 initialValue Averaging ($3,000 initialValue Averaging ($3,000 initialValue Averaging ($3,000 initialValue Averaging ($3,000 initial

investment, $1,000 invested each quarter)investment, $1,000 invested each quarter)investment, $1,000 invested each quarter)investment, $1,000 invested each quarter)investment, $1,000 invested each quarter) investment, $1,000 increase each quarterinvestment, $1,000 increase each quarterinvestment, $1,000 increase each quarterinvestment, $1,000 increase each quarterinvestment, $1,000 increase each quarter

TotalTotalTotalTotalTotal TotalTotalTotalTotalTotal

No. ofNo. ofNo. ofNo. ofNo. of No. ofNo. ofNo. ofNo. ofNo. of No. ofNo. ofNo. ofNo. ofNo. of No. ofNo. ofNo. ofNo. ofNo. of

AmountAmountAmountAmountAmount SharesSharesSharesSharesShares SharesSharesSharesSharesShares TotalTotalTotalTotalTotal TotalTotalTotalTotalTotal AmountAmountAmountAmountAmount SharesSharesSharesSharesShares SharesSharesSharesSharesShares TotalTotalTotalTotalTotal TotalTotalTotalTotalTotal

N A VN A VN A VN A VN A V InvestedInvestedInvestedInvestedInvested BoughtBoughtBoughtBoughtBought O w n e dO w n e dO w n e dO w n e dO w n e d InvestedInvestedInvestedInvestedInvested ValueValueValueValueValue InvestedInvestedInvestedInvestedInvested BoughtBoughtBoughtBoughtBought O w n e dO w n e dO w n e dO w n e dO w n e d InvestedInvestedInvestedInvestedInvested ValueValueValueValueValue

QuarterQuarterQuarterQuarterQuarter ($)($)($)($)($) ($)($)($)($)($) (#)(#)(#)(#)(#) (#)(#)(#)(#)(#) ($)($)($)($)($) ($)($)($)($)($) ($)($)($)($)($) (#)(#)(#)(#)(#) (#)(#)(#)(#)(#) ($)($)($)($)($) ($)($)($)($)($)

Dec1995 57.60 3,000 52.083 52.083 3,000 3,000 3,000 52.083 52.083 3,000 3,000

Mar1996 60.42 1,000 16.551 68.634 4,000 4,147 853 14.120 66.203 3,853 4,000

Jun1996 62.89 1,000 15.901 84.535 5,000 5,316 836 13.301 79.504 4,690 5,000

Sep1996 64.59 1,000 15.482 100.017 6,000 6,460 865 13.390 92.894 5,554 6,000

Dec1996 69.16 1,000 14.459 114.476 7,000 7,917 575 8.321 101.215 6,130 7,000

Mar1997 70.69 1,000 14.146 128.622 8,000 9,092 845 11.955 113.170 6,975 8,000

Jun1997 82.73 1,000 12.088 140.710 9,000 11,641 363 4.382 108.788 6,612 9,000

Sep1997 88.65 1,000 11.280 151.990 10,000 13,474 356 4.015 112.803 6,968 10,000

Dec1997 90.07 1,000 11.102 163.092 11,000 14,690 840 9.324 122.127 7,808 11,000

Mar1998 102.21 1,000 9.784 172.876 12,000 17,670 483 4.722 117.405 7,326 12,000

Jun1998 105.30 1,000 9.497 182.373 13,000 19,204 637 6.052 123.457 7,963 13,000

Sep1998 94.56 1,000 10.575 192.948 14,000 18,245 2,326 24.597 148.054 10,289 14,000

Dec1998 113.95 1,000 8.776 201.724 15,000 22,986 1,871 16.417 131.637 8,418 15,000

Mar1999 118.90 1,000 8.410 210.134 16,000 24,985 348 2.930 134.567 8,766 16,000

Jun1999 126.83 1,000 7.885 218.019 17,000 27,651 67 0.529 134.038 8,699 17,000

Sep1999 118.55 1,000 8.435 226.454 18,000 26,846 2,110 17.797 151.835 10,809 18,000

Dec1999 135.33 1,000 7.389 233.843 19,000 31,646 1,548 11.437 140.398 9,261 19,000

Mar2000 138.08 1,000 7.242 241.085 20,000 33,289 614 4.446 144.844 9,875 20,000Jun2000 134.15 1,000 7.454 248.539 21,000 33,342 1,569 11.697 156.541 11,444 21,000

Sep2000 132.59 1,000 7.542 256.081 22,000 33,954 1,244 9.384 165.925 12,689 22,000

Dec2000 121.86 1,000 8.206 264.287 23,000 32,206 2,780 22.816 188.741 15,469 23,000

Mar2001 107.07 1,000 9.340 273.627 24,000 29,297 3,792 35.411 224.152 19,260 24,000

Jun2001 113.02 1,000 8.848 282.475 25,000 31,925 334 2.952 221.200 18,927 25,000

Return on InvestmentReturn on InvestmentReturn on InvestmentReturn on InvestmentReturn on Investment 5454545454 .....55555%%%%% 7474747474.1%.1%.1%.1%.1%

Historical mutual fund prices provided by Media General Financial Services through MSN MoneyCentral Investor.

Table excludes impact of dividend and capital gain distributions.

Table1. Averaging With Vanguard 500 Index (VFINX) FundTable1. Averaging With Vanguard 500 Index (VFINX) FundTable1. Averaging With Vanguard 500 Index (VFINX) FundTable1. Averaging With Vanguard 500 Index (VFINX) FundTable1. Averaging With Vanguard 500 Index (VFINX) Fund


3/6

23July/August 2001

invested to do so, conceivably muchmore or much less in total than thecertain dollar cost averaging sum.

Which approach works best? Whileeither approach could dominate overany time period, value averaging prob-ably has the edge because it is more

aggressive. However, value averag-ing requires more monitoring, moretransactions costs and, because it trig-gers sales, potentially more tax conse-quences. Value averaging can be modi-fied so no sales take place, with futurevalue increases adjusted to compen-sate. Also, the loss potential is greaterfor value averaging because the totalamount that is required to be investedis unconstrained.

Please note that you cannot judgewhich approach did best in the ex-

amples simply by looking at endingportfolio values because the amountsinvested and the timing of the invest-

ments differ for the two approaches.The calculation to determine perfor-mance is called an internal rate of re-turn calculation that takes into con-sideration all the cash flows and theirtiming. These returns are given at thebottom of each example.

Value Averaging SpreadsheetValue Averaging SpreadsheetValue Averaging SpreadsheetValue Averaging SpreadsheetValue Averaging Spreadsheet

Dollar cost averaging is a very simpleapproach: An individual invests afixed amount of money at regular timeintervals. The value averaging ap-proach is more aggressive and tunedto the market because it forces you toinvest more money when the marketturns down and the total value of yourholding decreases. When the value ofyour holding goes up, you invest lessmoney buying the higher priced shares

and potentially even selling shares.Because the amount of money youneed to invest will change every pe-

riod with a value averaging strategya spreadsheet is a handy tool to calculate the periodic investment amount

One criticism of value averaging ithe forced sale of shares. Unless youinvestment is in a tax-sheltered account, you may be forced to pay capi

tal gains taxes earlier than plannedTherefore, the spreadsheet allows youto set whether or not you wish to selshares when the value of your fundincreases beyond the desired amount

Figures 1 and 2 present our valueaveraging spreadsheet. The spreadsheet can be downloaded either fromthe Files from AAII or Spreadsheets section of the AAII.com Download Library (www.aaii.com/dloads/). The Nasdaq-100 Trus(QQQ) is used as an example in the

spreadsheet. To use the spreadsheetyou would first enter the starting investment amount in cell A5 and the

Dollar Cost Averaging ($3,000 initialDollar Cost Averaging ($3,000 initialDollar Cost Averaging ($3,000 initialDollar Cost Averaging ($3,000 initialDollar Cost Averaging ($3,000 initial Value Averaging ($3,000 initialValue Averaging ($3,000 initialValue Averaging ($3,000 initialValue Averaging ($3,000 initialValue Averaging ($3,000 initial

investment, $1,000 invested each quarter)investment, $1,000 invested each quarter)investment, $1,000 invested each quarter)investment, $1,000 invested each quarter)investment, $1,000 invested each quarter) investment, $1,000 increase each quarterinvestment, $1,000 increase each quarterinvestment, $1,000 increase each quarterinvestment, $1,000 increase each quarterinvestment, $1,000 increase each quarter

TotalTotalTotalTotalTotal TotalTotalTotalTotalTotal

No. ofNo. ofNo. ofNo. ofNo. of No. ofNo. ofNo. ofNo. ofNo. of No. ofNo. ofNo. ofNo. ofNo. of No. ofNo. ofNo. ofNo. ofNo. of

AmountAmountAmountAmountAmount SharesSharesSharesSharesShares SharesSharesSharesSharesShares TotalTotalTotalTotalTotal TotalTotalTotalTotalTotal AmountAmountAmountAmountAmount SharesSharesSharesSharesShares SharesSharesSharesSharesShares TotalTotalTotalTotalTotal TotalTotalTotalTotalTotal

N A VN A VN A VN A VN A V InvestedInvestedInvestedInvestedInvested BoughtBoughtBoughtBoughtBought O w n e dO w n e dO w n e dO w n e dO w n e d InvestedInvestedInvestedInvestedInvested ValueValueValueValueValue InvestedInvestedInvestedInvestedInvested BoughtBoughtBoughtBoughtBought O w n e dO w n e dO w n e dO w n e dO w n e d InvestedInvestedInvestedInvestedInvested ValueValueValueValueValue

QuarterQuarterQuarterQuarterQuarter ($)($)($)($)($) ($)($)($)($)($) (#)(#)(#)(#)(#) (#)(#)(#)(#)(#) ($)($)($)($)($) ($)($)($)($)($) ($)($)($)($)($) (#)(#)(#)(#)(#) (#)(#)(#)(#)(#) ($)($)($)($)($) ($)($)($)($)($)

Dec1995 23.04 3,000 130.208 130.208 3,000 3,000 3,000 130.208 130.208 3,000 3,000

Mar1996 24.94 1,000 40.096 170.304 4,000 4,247 753 30.177 160.385 3,753 4,000

Jun1996 25.44 1,000 39.308 209.612 5,000 5,333 920 36.156 196.541 4,672 5,000

Sep1996 26.83 1,000 37.272 246.884 6,000 6,624 727 27.089 223.630 5,399 6,000

Dec1996 24.45 1,000 40.900 287.784 7,000 7,036 1,532 62.669 286.299 6,931 7,000

Mar1997 24.39 1,000 41.000 328.784 8,000 8,019 1,017 41.704 328.003 7,949 8,000

Jun1997 27.62 1,000 36.206 364.990 9,000 10,081 59 2.152 325.851 7,889 9,000

Sep1997 30.14 1,000 33.179 398.169 10,000 12,001 179 5.934 331.785 8,068 10,000

Dec1997 24.90 1,000 40.161 438.330 11,000 10,914 2,739 109.982 441.767 10,807 11,000

Mar1998 28.51 1,000 35.075 473.405 12,000 13,497 595 20.862 420.905 10,212 12,000

Jun1998 30.17 1,000 33.146 506.551 13,000 15,283 301 9.987 430.892 10,513 13,000

Sep1998 26.85 1,000 37.244 543.795 14,000 14,601 2,431 90.523 521.415 12,944 14,000

Dec1998 33.65 1,000 29.718 573.513 15,000 19,299 2,546 75.650 445.765 10,398 15,000

Mar1999 37.47 1,000 26.688 600.201 16,000 22,490 703 18.757 427.008 9,695 16,000

Jun1999 40.20 1,000 24.876 625.077 17,000 25,128 166 4.122 422.886 9,530 17,000

Sep1999 39.57 1,000 25.272 650.349 18,000 25,734 1,266 32.004 454.890 10,796 18,000

Dec1999 44.05 1,000 22.701 673.050 19,000 29,648 1,038 23.562 431.328 9,758 19,000

Mar2000 48.65 1,000 20.555 693.605 20,000 33,744 984 20.228 411.100 8,774 20,000

Jun2000 45.39 1,000 22.031 715.636 21,000 32,483 2,340 51.557 462.657 11,114 21,000

Sep2000 45.72 1,000 21.872 737.508 22,000 33,719 847 18.533 481.190 11,961 22,000

Dec2000 33.29 1,000 30.039 767.547 23,000 25,552 6,981 209.708 690.898 18,943 23,000

Mar2001 27.29 1,000 36.643 804.190 24,000 21,946 5,145 188.545 879.443 24,088 24,000

Jun2001 29.20 1,000 34.247 838.437 25,000 24,482 680 23.279 856.164 23,408 25,000

Internal Rate on ReturnInternal Rate on ReturnInternal Rate on ReturnInternal Rate on ReturnInternal Rate on Return 3.8%3.8%3.8%3.8%3.8% 16.4%16.4%16.4%16.4%16.4%

Historical mutual fund and industry prices provided by Media General Financial Services through MSN MoneyCentral Investor.

Table excludes impact of dividend and capital gain distributions.

Table 2. Averaging With Janus (JANSX) FundTable 2. Averaging With Janus (JANSX) FundTable 2. Averaging With Janus (JANSX) FundTable 2. Averaging With Janus (JANSX) FundTable 2. Averaging With Janus (JANSX) Fund


4/6

C24

dollar amount of the desired changein cell A6. Allowing for two separateentries is handy because some fundsrequire a higher investment initiallythan for subsequent purchases. Twoentries also allow you to apply a valueaveraging purchase or sales plan to analready existing investment. To do this,simply input the current value of yourholding in cell A5 and the portfoliochange amount in cell A6. If you wishto use the spreadsheet to average outof 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 donein whole share increments for stocks,while mutual funds can usually bepurchased or sold using fractionalshares. Enter a 1 in cell A7 if you wantto work with fractional share amounts,or 0 if you cannot. The message in cellB8 confirms your selection.

Cell A9 is where you indicate if youwish to sell shares when your portfo-

1

2

3

45

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

A B C D E F G H I J

Value Averaging Worksheet, July/August 2001 Computerized Investing, AAII

TickerQQQ Security Nasdaq-100 Trust, Series 1

$1,000 DollarAmount ofInitialInvestment

$1,000 DollarAmount ofIncrease Desired EachPeriod

0


5/6

25July/August 2001

net asset value or share price of thesecurity you are tracking.

Column D allows you to enter anyshare amounts that you might haveacquired, or even sold, since you lastrebalanced the portfolio. You woulduse this column to input any shares

acquired through dividend reinvest-ment. Column D is also where you canadjust for any difference in the amountof shares you expected to acquire orsell when you instructed your fund orbroker compared to the quantity actu-

ally transacted. Small differenceswould be expected because of the timelags.

Column E sums the total number ofshares from the last rebalancing andaccounts for any differences enteredin column D. Column F computes the

total value of your holding before thecurrent rebalancingmultiplying thetotal number of shares owned in col-umn E times the net asset value incolumn C. Column G compares thecurrent value of your holdings to the

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

3334

35

36

37

38

39

40

41

42

43

44

A B C D E F G H I J

Value Averaging Worksheet, July/August 2001 Computerized Investing, AAII

TickerQQQ Security Nasdaq-100 Trust, Series 1

$1,000 DollarAmount ofInitialInvestment

$1,000 DollarAmount ofIncrease Desired EachPeriod

0


6/6

C26

The spreadsheet is set up so, as morerows are needed over time, you cantake any row at row 18 or below andcopy it down the spreadsheet as far asdesired.

Dollar cost averaging and value av-eraging provide a clear path for inves-

tors to follow. With the pathwaymarked, taking the first few steps ofan investment plan should be thatmuch easier. Both dollar cost averag-ing and value averaging attempt toreduce one of the largest fears of risk-averse investorsmoving a large sum

of money into the market just before asevere market decline.

John Bajkowski is editor ofComputerized Investing and AAIIs vice presidenof financial analysis. John Markese is presi

dent of AAII.

Table 3. Spreadsheet FormulasTable 3. Spreadsheet FormulasTable 3. Spreadsheet FormulasTable 3. Spreadsheet FormulasTable 3. Spreadsheet Formulas

A3: Ticker

B3: Q Q Q

D3: Security

E3: Nasdaq-100 Trust, Series 1

A5: 1000

B5: Dollar Amount of Initial Investment

A6: 1000

B6: Dollar Amount of Increase Desired Each Period

A7: 0

B7:

value investing spreadsheet

Documents