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 0.12 6 Standard deviation of return 0.35 7 Proportion invested in risky 0.7 8 Accumulation at age 60 462700.73 9 Total Age Investment at| beginning of New investment period (BOY) Random Number, normally distributed investment at end of period 10 11 45 12 46 13 14 15 16 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 47 48 49 50 51 52 53 54 55 56 57 58 59 60 17 Total investment at beginning of period (investment BOY+ new investment) 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 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 18 19 20 21 22 23 24 25 26 -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 10000 10000 10000 10000 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 0.12 6 Standard deviation of return 0.35 7 Proportion invested in risky 0.7 8 Accumulation at age 60 462700.73 9 Total Age Investment at| beginning of New investment period (BOY) Random Number, normally distributed investment at end of period 10 11 45 12 46 13 14 15 16 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 10000 47 48 49 50 51 52 53 54 55 56 57 58 59 60 17 Total investment at beginning of period (investment BOY+ new investment) 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 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 18 19 20 21 22 23 24 25 26 -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 10000 10000 10000 10000
