1. A 65-year old pensioner wants to invest 500,000 for the next 5 years. She is considering to invest in an investment strategy in which she would invest 20% of his assets in a risk-free bond with 5% continuously compounded annual interest and the remaining 80% in a risky asset with 8% mean return and 18% standard deviation.

The pensioner applied Monte Carlo simulation to decide whether she should invest her money in this investment strategy. The Excel spreadsheet below reports the end-of-year wealth based on one simulation that she conducted.

Write down and explain the Excel formula used to calculate the yellowed values in cells E12 and F12, in the Excel spreadsheet below:

A E F B C D RETIREMENT PLAN 500,000 5% 80% 1 2 Current wealth 3 Risk-free rate 4 Proportion invested in risky 5 Parameters of risky investment 6 Expected annual return 7 Standard deviation of return 8 9 10 8% 18% Year 11 12 13 14 15 16 1 2 3 4 5 Random Wealth at Wealth at beginning Invested in Invested in number, end of risky bonds of year normally year distributed 500,000 400,000 100,000 -0.0510 534,482 534,482 427,586 106,896 1.2083 688,109 688,109 550,488 137,622 0.2587 769,442 769,442 615,554 153,888 -0.2286 801,722 801,722 641,378 160,344 -0.6405 787,700 A E F B C D RETIREMENT PLAN 500,000 5% 80% 1 2 Current wealth 3 Risk-free rate 4 Proportion invested in risky 5 Parameters of risky investment 6 Expected annual return 7 Standard deviation of return 8 9 10 8% 18% Year 11 12 13 14 15 16 1 2 3 4 5 Random Wealth at Wealth at beginning Invested in Invested in number, end of risky bonds of year normally year distributed 500,000 400,000 100,000 -0.0510 534,482 534,482 427,586 106,896 1.2083 688,109 688,109 550,488 137,622 0.2587 769,442 769,442 615,554 153,888 -0.2286 801,722 801,722 641,378 160,344 -0.6405 787,700