A 65-year-old pensioner wants to invest 500,000 for the next 5 years. She is considering investing...

Transcribed Image Text:

A 65-year-old pensioner wants to invest £500,000 for the next 5 years. She is considering investing 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: 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 .co 8 9 10 A 11 12 13 14 15 16 Year 1 2 3 4 5 B RETIREMENT PLAN 500,000 5% 80% 8% 18% C Wealth at beginning of year D Invested in Invested in risky bonds 500,000 400,000 100,000 534,482 427,586 106,896 688,109 550,488 137,622 769,442 615,554 153,888 801,722 641,378 160,344 E Random number, normally distributed -0.0510 1.2083 0.2587 -0.2286 -0.6405 F Wealth at end of year 534,482 688,109 769,442 801,722 787,700 A 65-year-old pensioner wants to invest £500,000 for the next 5 years. She is considering investing 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: 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 .co 8 9 10 A 11 12 13 14 15 16 Year 1 2 3 4 5 B RETIREMENT PLAN 500,000 5% 80% 8% 18% C Wealth at beginning of year D Invested in Invested in risky bonds 500,000 400,000 100,000 534,482 427,586 106,896 688,109 550,488 137,622 769,442 615,554 153,888 801,722 641,378 160,344 E Random number, normally distributed -0.0510 1.2083 0.2587 -0.2286 -0.6405 F Wealth at end of year 534,482 688,109 769,442 801,722 787,700

Answer rating: 100% (QA)

The Excel formula used to calculate the yellowed values in the Monte Carlo simulation is RAND T... View the full answer