Uncertainty and Monte Carlo Simulation

This assignment is designed to familiarize you with using tools of Monte Carlo simulation to explore uncertainty and decisions under uncertainty.

You have just inherited an unexpected $10,000. Being a sensible and conservative person, you decide to invest this amount for at least the next 10 years. According to your financial advisor, there are three choices: a high-risk stock, a low-risk stock, or just put the money in a savings account. The high-risk stock could earn the most, but could also lose the most value, the low-risk stock has less uncertainty, and the savings account will earn a fixed low rate with no uncertainty. Your advisor estimates the uncertainty in the average growth rate of each investment as:

High-risk stock: Lognormal with mean 7% and standard deviation 5%, minus 3%

Low-risk stock: Lognormal with mean 5% and standard deviation 2%, minus 1%

Savings account: constant rate of 2%

Setting up the spreadsheet

An excel template will be provided to you, with key parameter values, and a column of 5000 samples from a Uniform(0,1) random variable.

Step 1: using the distribution parameters and the built-in excel function =LOGNORM.INV(), generate a column of samples of growth rate from the high-risk asset, and another column of sample rates from the low-risk asset. For consistency, create a third column for the savings account, consisting of “2” in every row.

Step 2: Create another column to calculate the net value if $10000 were invested in the high-risk asset for each sample rate of return. Assume for each sample that the funds are left in that asset for 10 years, compounding at the given sample rate. The formula should look something like: “=$J$2*(1 + D7/100)^10”, where cell $J$2 contains $10000, and cell D7 contains the same rate of return for the asset. Make another column for the low-risk asset, using its sample rates, and another column for the savings account.

Answer the following questions:

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)?

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?

Create a graph with the Cumulative Distribution Functions (CDFs) of the three investments on a single graph, by following these steps:

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.

Sort each of the columns from smallest to largest. Sort the columns independently, not at the same time.

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 “= <cell above me> + 1/5000”. This should produce a column of numbers that increase linearly to a value of “1.0”.

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.

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?

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. <Range> means select the entire column of 5000 samples, and use the cell range in the formula.

P0.05 (5th percentile): “=percentile(<Range>,0.05)”

P0.5 (median): “=percentile(<Range>,0.5)”

P0.95 (5th percentile): “=percentile(<Range>,0.05)”

Number of cases that lose money (i.e., returns are less than the original $10,000):

“countif(<Range>,”<10000”)

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.

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?

**Are you looking for a similar paper or any other quality academic essay? Then look no further. Our research paper writing service is what you require. Our team of experienced writers is on standby to deliver to you an original paper as per your specified instructions with zero plagiarism guaranteed. This is the perfect way you can prepare your own unique academic paper and score the grades you deserve.**

**Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.**