a) Calculate the average rates from your samples. Make sure that the average rate is what you expect. Using the average rates of return (not individual samples), what is (are) the best the investment (s)?
b) Use the 5000 sample rates to perform a Monte Carlo Estimate. Below the last sampe of returns for each investment, calculate the mean return using the "average()" function, and select the entire column of sample returns. Based on the expected value of the net profit after 10 years, which investment is the best choice?
c) Create a graph with the Cumulative Distribution Functions (CDFs) of the three investments on a single graph, by following these steps:
a. Copy the 5000 samples of returns from the three investment choices at the same time, select a new worksheet, and "paste-as-values". This should give you three columns with 5000 rows. Make sure if click on one of the cells, it is a number, not a formula.
b. Sort each of the columns from smallest to largest. Sort the columns independently, not at the same time.
c. Create a fourth column for the cumulative probability, or "P". The first row should contain "=1/5000". The remaining rows should contain the formula for "= | + 1/5000". This should produce a column of numbers that increase linearly to a value of "1.0". |
d. Insert a chart in excel: choose "scatterplot" type, "scatter with straight lines" subtype. Plot each column of sorted returns vs. P, all on the same chart.
e. To make it easier to analyze the graph, use the "Format Axis" dialog for the X-Axis, and make the maximum value "50000".
Question: Are any of the investment choices stochastically dominant? Looking at this graph, can you imagine a reason why you might choose
a different investment than the one with the highest mean return - if so, what is it and why?
d) Go back to the original worksheet with the samples of returns. Create a table of summary statistics for the three investments starting at worksheet row "5008" or anywhere below the last row of samples, and after your calculation of the mean for part a). Calculate each of the following using the indicated excel function. means select the entire column of 5000 samples, and use the cell range in the formula.
a. P0.05 (5th percentile): "=percentile(,0.05)"
b. P0.5 (median): "=percentile(,0.5)"
c. P0.95 (5th percentile): "=percentile(,0.05)"
d. Number of cases that lose money (i.e., returns are less than the original $10,000):
"countif(,"<10000")
e. Fraction of cases that lose money (i.e., returns are less than the original $10,000):
Divide the cell above by 5000.
Question: Would any of this information change your recommendation for the best investment from your answer in part a)? If so, what information and why? Explain your reasoning.
e) Create another column that indicates the best investment with "H"=High-risk stock, "L"=low-risk stock, "S"=savings (hint, if your resulting earnings were in cells F4, F5, and F6, respectively, then the formula would be "=IF(AND((F4>F5),(F4>F6)),"H",IF((F5>F6),"L","S"))".
Is the savings account ever the best investment for a particular sample? If so, what is the probability that the savings account is the best choice? Does this change your recommendation for which investment to choose?