Response to the following problem:
While most people believe that it is not possible to consistently time the market, there are several plans that allow investors to time purchases and sales of securities. These are referred to as formula plans-mechanical methods of managing a portfolio that attempt to take advantage of cyclical price movements. The objective is to mitigate the level of risk facing the investor. One such formula plan is dollar-cost averaging. Here, a fixed dollar amount is invested in a security at fixed intervals. One objective is to increase the value of the given security over time. If prices decline, more shares are purchased; when market prices increase, fewer shares are purchased per period. The essence is that an investor is more likely not to buy overvalued securities. Over the past 12 months, March 2011 through February 2012, Mary has used the dollarcost averaging formula to purchase $1000 worth of Neo shares each month. The monthly price per share paid over the 12-month period is given following. Assume that Mary paid no brokerage commissions on these transactions.
Create a spreadsheet model to analyse the following investment situation for Neo shares through dollar-cost averaging.
Year Month Price paid per share
2011 March $14.30
April 16 18
May 18.37
June 16.25
July 14.33
August 15.14
September 15.93
October 19.36
November 23.25
December 18.86
2012 January 22.08
February 22.01
Questions
1. What is the total investment over the period from March 2011 through February 2012?
2. What is the total number of Neo shares purchased over the 12-month period?
3. What is the average cost per share?
4. What is the year-end (February 2012) portfolio value?
5. What is the profit or loss as of the end of February 2012?
6. What is the return on the portfolio after the 12-month period?