Why is it desirable to conduct Monte Carlo simulations using as many replications of the experiment as possible? [5 marks]

b. Explain in detail how pseudo-random numbers are generated in Excel. [5 marks]

c. In which range of numbers do we expect the random numbers drawn from a standard normal distribution to fall? Explain why. [5 marks]

d. Marcus is 45-year old. He has a new job and intends to save 10,000 today and in each of the next 14 years (15 deposits altogether). He is considering to invest in an investment policy in which he would invest 30% of his assets in a risk-free bond with 3% continuously compounded annual interest and the remaining 70% in a risky asset that has lognormally distributed returns with mean = 12% and standard deviation = 35%. Marcus applied Monte Carlo simulation to decide whether he should invest his money in this investment strategy. The Excel spreadsheet below reports the end-of-year wealth based on one simulation that he conducted. Write down and explain the Excel formula used to calculate the yellowed values in cells E11 and F11, in the Excel spreadsheet below

B D E F MARCUS'S INVESTMENT/SAVINGS DECISION 1 2 Annual deposit 10000 3 Risk-free rate 0.03 4 Parameters of risky investment 5 Expected annual return 6 Standard deviation of return 0.35 7 Proportion invested in risky 0.7 8 Accumulation at age 60 462700.73 9 0.12 Age Investment at beginning of New investment period (BOY) Total investment at beginning of period (investment BOY+ new investment) Random Number, normally distributed Total investment at end of period 10 11 45 10000 12 46 13 47 14 48 49 10000 10000 10000 10000 10000 10000 15 16 50 17 51 18 52 01 9029.45 20903.51 32635.61 45899.80 96051.38 93827.07 121045.23 127097.31 126129.69 127939.70 259440.32 290949.65 476608.26 413562.11 462700.73 10000 19029.44907 30903.50729 42635.61002 55899.80068 106051.3802 103827.0698 131045.2266 137097.3066 136129.6883 137939.7007 269440.3247 300949.652 486608.2619 423562.1061 -0.812923943 0.000354184 -0.157521283 -0.080117421 1.6561585 -0.901927446 0.234302332 -0.507783094 -0.732013116 -0.64071202 1.968071518 -0.06797099 1.369325587 -1.080348058 -0.021732861 9029.449067 20903.50729 32635.61002 45899.80068 96051.38024 93827.06981 121045.2266 127097.3066 126129.6883 127939.7007 259440.3247 290949.652 476608.2619 413562.1061 462700.7342 19 53 10000 10000 10000 10000 10000 10000 10000 10000 54 20 21 55 22 56 23 57 24 58 59 25 26 60 B D E F MARCUS'S INVESTMENT/SAVINGS DECISION 1 2 Annual deposit 10000 3 Risk-free rate 0.03 4 Parameters of risky investment 5 Expected annual return 6 Standard deviation of return 0.35 7 Proportion invested in risky 0.7 8 Accumulation at age 60 462700.73 9 0.12 Age Investment at beginning of New investment period (BOY) Total investment at beginning of period (investment BOY+ new investment) Random Number, normally distributed Total investment at end of period 10 11 45 10000 12 46 13 47 14 48 49 10000 10000 10000 10000 10000 10000 15 16 50 17 51 18 52 01 9029.45 20903.51 32635.61 45899.80 96051.38 93827.07 121045.23 127097.31 126129.69 127939.70 259440.32 290949.65 476608.26 413562.11 462700.73 10000 19029.44907 30903.50729 42635.61002 55899.80068 106051.3802 103827.0698 131045.2266 137097.3066 136129.6883 137939.7007 269440.3247 300949.652 486608.2619 423562.1061 -0.812923943 0.000354184 -0.157521283 -0.080117421 1.6561585 -0.901927446 0.234302332 -0.507783094 -0.732013116 -0.64071202 1.968071518 -0.06797099 1.369325587 -1.080348058 -0.021732861 9029.449067 20903.50729 32635.61002 45899.80068 96051.38024 93827.06981 121045.2266 127097.3066 126129.6883 127939.7007 259440.3247 290949.652 476608.2619 413562.1061 462700.7342 19 53 10000 10000 10000 10000 10000 10000 10000 10000 54 20 21 55 22 56 23 57 24 58 59 25 26 60