Question 4 a. 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 u = 12% and standard deviation o = 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: D 1 MARCUS'S INVESTMENT/SAVINGS DECISION 2 Annual deposit 10000 3 Risk-free rate 0.03 4 Parameters of risky investment 5 Expected annual return 0.12 6 Standard deviation of return 0.35 7 Proportion invested in risky 0.7 8 Accumulation at age 50 462700.75 9 Age Investment at beginning of New investment period (307 Random Number, normally distributed Total investment at end of period 10 45 46 47 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 49 50 51 52 53 54 55 56 57 58 59 60 0 9029.45 20903.53 3263561 45899 80 9605138 9382707 121045.23 127097.31 126129.69 127939.70 259440 32 290949.65 476608.26 413562 11 462700.73 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 Total investment at beginning of period investment BOY+new Investment) 10000 1902944907 30903 50729 42635 61002 55899 80068 105051 3802 1038270698 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 0234302332 -0.507783094 -0.732013116 0.64071202 1.968071518 -0.067970991 1369325587 - 1.080349058 -0.021732861 9029.449067 20903.50729 32635.61002 45899.80068 95051 38024 9382706981 121045 2266 127097 3066 1261296885 12793970071 259440 32471 290949.652 476608 2619 413562 1061 462700.7342 Question 4 a. 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 u = 12% and standard deviation o = 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: D 1 MARCUS'S INVESTMENT/SAVINGS DECISION 2 Annual deposit 10000 3 Risk-free rate 0.03 4 Parameters of risky investment 5 Expected annual return 0.12 6 Standard deviation of return 0.35 7 Proportion invested in risky 0.7 8 Accumulation at age 50 462700.75 9 Age Investment at beginning of New investment period (307 Random Number, normally distributed Total investment at end of period 10 45 46 47 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 49 50 51 52 53 54 55 56 57 58 59 60 0 9029.45 20903.53 3263561 45899 80 9605138 9382707 121045.23 127097.31 126129.69 127939.70 259440 32 290949.65 476608.26 413562 11 462700.73 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 Total investment at beginning of period investment BOY+new Investment) 10000 1902944907 30903 50729 42635 61002 55899 80068 105051 3802 1038270698 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 0234302332 -0.507783094 -0.732013116 0.64071202 1.968071518 -0.067970991 1369325587 - 1.080349058 -0.021732861 9029.449067 20903.50729 32635.61002 45899.80068 95051 38024 9382706981 121045 2266 127097 3066 1261296885 12793970071 259440 32471 290949.652 476608 2619 413562 1061 462700.7342
