Retrieve the data on the companies' historical data and calculate annual rate of return for the past 20 years for each security.

Apple:

Year	Year Open	Year Close	Annual Return
2002	21.9	14.33	-34.57%
2003	14.33	21.37	49.13%
2004	21.37	64.4	201.36%
2005	64.4	143.78*	123.26%
2006	71.89	84.84	18.01%
2007	84.84	198.08	133.47%
2008	198.08	85.35	-56.91%
2009	85.35	210.732	146.90%
2010	210.732	322.56	53.07%
2011	322.56	405	25.56%
2012	405	532.17	31.40%
2013	532.17	561.02	5.42%
2014	561.02	772.66*	37.72%
2015	110.38	105.26	-4.64%
2016	105.26	115.82	10.03%
2017	115.82	169.23	46.11%
2018	169.23	157.74	-6.79%
2019	157.74	293.65	86.16%
2020	293.65	530.76*	80.75%
2021	132.69	177.57	33.82%
2022	177.57	129.93	-26.83%

Walmart

Year	Year Open	Year Close	Annual Return
2002	57.55	50.51	-12.23%
2003	50.51	53.05	5.03%
2004	53.05	52.82	-0.43%
2005	52.82	46.8	-11.40%
2006	46.8	46.18	-1.32%
2007	46.18	47.53	2.92%
2008	47.53	56.06	17.95%
2009	56.06	53.45	-4.66%
2010	53.45	53.93	0.90%
2011	53.93	59.76	10.81%
2012	59.76	68.23	14.17%
2013	68.23	78.69	15.33%
2014	78.69	85.88	9.14%
2015	85.88	61.3	-28.62%
2016	61.3	69.12	12.76%
2017	69.12	98.75	42.87%
2018	98.75	93.15	-5.67%
2019	93.15	118.84	27.58%
2020	118.84	144.45	21.30%
2021	144.45	144.69	0.37%
2022	144.69	141.79	-2.00%

Coca-cola

Year	Year Open	Year Close	Annual Return
2002	47.15	43.84	-7.02%
2003	43.84	50.75	15.76%
2004	50.75	41.64	-17.95%
2005	41.64	40.31	-3.19%
2006	40.31	48.25	19.70%
2007	48.25	61.37	27.19%
2008	61.37	45.27	-26.23%
2009	45.27	57	25.91%
2010	57	65.77	15.39%
2011	65.77	69.97	6.39%
2012	69.97	72.50*	3.62%
2013	36.25	41.31	13.96%
2014	41.31	42.22	2.20%
2015	42.22	42.96	1.75%
2016	42.96	41.46	-3.49%
2017	41.46	45.88	10.66%
2018	45.88	47.35	3.20%
2019	47.35	55.35	16.90%
2020	55.35	54.84	-0.92%
2021	54.84	59.21	7.97%
2022	59.21	63.61	7.43%

Pfizer

Year	Year Open	Year Close	Annual Return
2002	39.85	30.57	-23.29%
2003	30.57	35.33	15.57%
2004	35.33	26.89	-23.89%
2005	26.89	23.32	-13.28%
2006	23.32	25.90	11.06%
2007	25.9	22.73	-12.24%
2008	22.73	17.71	-22.09%
2009	17.71	18.19	2.71%
2010	18.19	17.51	-3.74%
2011	17.51	21.64	23.59%
2012	21.64	25.08	15.90%
2013	25.08	30.63	22.13%
2014	30.63	31.15	1.70%
2015	31.15	32.28	3.63%
2016	32.28	32.48	0.62%
2017	32.48	36.22	11.51%
2018	36.22	43.65	20.51%
2019	43.65	39.18	-10.24%
2020	39.18	39.13524*	-0.11%
2021	36.81	59.05	60.42%
2022	59.05	51.24	-13.23%

Whirlpool

Year	Year Open	Year Close	Annual Return
2002	73.33	52.22	-28.79%
2003	52.22	72.65	39.12%
2004	72.65	69.21	-4.74%
2005	69.21	83.76	21.02%
2006	83.76	83.02	-0.88%
2007	83.02	81.63	-1.67%
2008	81.63	41.35	-49.34%
2009	41.35	80.66	95.07%
2010	80.66	88.83	10.13%
2011	88.83	47.45	-46.58%
2012	47.45	101.75	114.44%
2013	101.75	156.86	54.16%
2014	156.86	193.74	23.51%
2015	193.74	146.87	-24.19%
2016	146.87	181.77	23.76%
2017	181.77	168.64	-7.22%
2018	168.64	106.87	-36.63%
2019	106.87	147.53	38.05%
2020	147.53	180.49	22.34%
2021	180.49	234.66	30.01%
2022	234.66	141.46	-39.72%

The correlation between the five stock:

	apple	walmart	coca-cola	pfizer	whirlpool
apple	1
walmart	0.01343934	1
coca-cola	0.23629607	0.04100856	1
pfizer	-0.1846297	0.09394331	0.36256833	1
whirlpool	0.3959328	0.17937555	0.39760544	0.27231346	1

1. Choose percentages of your initial investment that you want to allocate amongst the five (5) securities (weights in the portfolio).

• a. Create embedded formulae which generate statistical properties of the portfolio upon insertion of the weights.
• b. Observe the mean, the standard deviation, and the CV of the annual rate of return of the portfolio.

2. Find the combination of the weights that minimizes CV of the portfolio.

• a. How does the CV of the optimal portfolio compare with the CVs of its constituents?
• b. What is the expected rate of return and standard deviation of the rate of return of the portfolio?

3. Choose different values within the range of the standard deviation of the portfolio, and for each chosen value, locate the corresponding point on the efficient frontier by finding the weights that maximize the expected rate of return of the portfolio.

• a. Subsequently, construct the efficient frontier of your portfolio.

4. Assume that you initially invested $1,000,000 in the portfolio and that the distribution of the annual rate of return of the portfolio is normal.

• a. What is the distribution of the return of the portfolio 20 years after its formation?
• b. Provide the graph of the distribution of the return of the portfolio.

Please provide explanations of all calculations and the justifications. Make sure to also paste all underlying Excel formulae that you used for calculations in the solution.