Assignment 3

For the following questions, please show your work in an Excel worksheet. Provide clarifying comments when necessary. Please upload the Excel workbook to Blackboard when you are done.

Part I: Empirical Market Returns

The Excel workbook titled 'Market_Returns.xlsx' contains historical monthly returns on the aggregate stock market (Mkt Return) and the monthly return on very short term US treasuries (Risk free return) going back to January 1927. Use this data answer the following questions:

1) Compute historical average arithmetic and geometric returns, and std deviations for the stock market and short-term treasuries. What does the distribution of monthly returns for the stock market look like? How does it compare to a normal distribution? You can use the FREQUENCY function in Excel to compute a histogram. You can use NORMDIST and NORMINV functions for the normal distribution.

2) What is the 5% Value at Risk for the stock market using historical returns? What is the Expected shortfall at 5%? What is the Value at Risk at 5% assuming a normal distribution?

Market timing is the process of moving in and out of assets according to predictions of what their prices will do next. Although there is some empirical evidence of time-series predictability in the stock market, it is very difficult to implement in practice. And because of the vagaries of human emotions, the act of trying to time the stock market often produces far worse results than just buying a diversified bundle of stocks and holding them for the long haul. People tend to sell in a panic at the bottom and buy in a flush of confidence at the top. John Bogle, founder of the Vanguard Group of mutual funds, wrote of market timing: "After nearly 50 years in this business, I do not know of anybody who has done it successfully and consistently. I don't even know of anybody who knows anybody who has done it successfully and consistently."

3) Suppose you invest $1 at the beginning of 1927 in the short term treasuries. What would be the value of the $1 invested at the end of 2012? What would be the value of a $1 invested in the stock market at the end of 2012? Now, suppose you have perfect foresight, and at the beginning of each month you can tell which market was going to have a higher return that month. If the short-term treasury bills have a higher return than the stock market in that month, then you invest all your money in the treasury bills. If not, you invest all your money in the stock market. If you could do that, how much would a $1 invested in January 1927 grow to at the end of 2012? What do these results tell you about the feasibility of market timing?

4) What would be the value of a $1 invested in the stock market if you exclude the worst 12 months of returns (that is set those returns equal to zero)? What would be the value of a $1 invested in the stock market if you exclude the best 12 months of returns?

Part II: Dollar Cost Averaging

Dollar cost averaging involves investing a fixed amount at fixed intervals of time. It is different from investing at fixed intervals based on your income (such as committing yourself to investing a fixed amount of your salary every month toward your retirement). With dollar cost averaging, you invest a lump sum that you currently have over a fixed time interval, as opposed to, investing everything at once. For example, if you had $12,000 that you wanted to invest in a stock index fund, you would invest $1000 per month over a year, rather than investing the whole amount immediately. The rationale is that market volatility should then work in your favor, because you will automatically be purchasing more shares when the price is low, and fewer shares when the price is high.1

1 This is what is commonly claimed by financial advisors and media. See for instance: http://www.smart401k.com/Content/retail/resource-center/retirement-investing-basics/dollar-cost-averaging

The following article and video make the case for dollar cost averaging from a behavioral finance perspective:

- http://online.wsj.com/article/SB10001424127887324050304578410840698473554.html

- https://www.dimensional.com/famafrench/videos/dollar-cost-averaging.aspx

Malkiel and French argue that dollar cost averaging may reduce risk during downturns and reduce regret that the investor may experience. We will empirically assess these claims in the following questions:

5) Suppose you have $1200 to invest. You can invest the full amount ($1200) at the beginning of each year, or invest $100 beginning of each month over the same year. Compute what your returns would have been under the two strategies for each year from 1927 to 2012. You can transform the data in Excel using PIVOT tables. Plot the differences in returns between the lump-sum and dollar cost averaging strategies over time. Are there particular years when the dollar cost averaging strategy does better?

6) In the previous question, we have examined the historical record which only provides 85 unique non-overlapping one year observations. To assess the argument that dollar cost averaging reduces risk in downturns and investor regret, we need a distribution of one year returns. We can simulate returns as described in Chapter 5 of BKM. First, simulate a series of monthly returns for the stock market assuming a normal distribution. You can use NORMINV and RAND functions in Excel. NORMINV(RAND(),historical mean, historical std deviation) would provide a simulated return drawn from a normal distribution with the specified mean and std deviation. Cumulative 1 year return on a $1200 lump-sum investment would be $1200*(1+simulated rate1)*(1+simulated rate2)*.....*(1+simulated rate12) -$1200 . For the dollar cost averaging strategy, the return would be: $100*(1+simulated rate1) + ($100 + $100*(1+simulated rate1) )* (1+simulated rate2) + .... -$1200. Compute 5,000 of these 1 year returns for the two strategies.

7) Repeat the simulations in question 6 using the bootstrap methodology described in Chapter 5 of BKM. With the bootstrap method you pick 12 returns from the historical data (with replacement) to compute the 1 year cumulative returns. You can use the RANDBETWEEN() and VLOOKUP() functions to do the bootstrap simulations.

Using the 5,000 simulated returns in 6) & 7), compute the average and the std deviation of the returns of the two strategies. Plot the distribution of the returns to both strategies on the same chart. Does the dollar cost averaging strategy appear less risky? What is the worst case return to an investor under each strategy? What is the worst 90th and 75th percentile loss? What is the probability that an investor will lose more than 20% under each strategy? What about 10%? Why do you think dollar cost averaging reduces investor regret?