[Solved] Portfolio Stock Prices and Returns: What

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 29, 2024

Portfolio Stock Prices and Returns: What excel formulas should be used to find each of the following?: - Annual Expected Return (for each stock) -

Portfolio Stock Prices and Returns:

What excel formulas should be used to find each of the following?:

- Annual Expected Return (for each stock)

- Annual Variance (for each stock)

- Annual Standard Deviation (for each stock)

- Covariance Matrix

- Annual Expected Returns of the Stock

- Risk Aversion Coefficient Factor (A)

- Expected Return of Portfolio

- Variance of Portfolio

- Standard Deviation of Portfolio

Note:

- You can make up the weights in the portfolio. I want to see what it looks like equally weighted (Equally-Weighted Portfolio) versus mixed weighted (Portfolio).

- The monthly price is the adjusted close values and the returns is calculated by using HPR formula (HPR=(end - initial)/initial).

- All data below are in the same spreadsheet tab on excel.

( image text in transcribed

image text in transcribed

D66 A M N Stock KL Date 2017-12-01 2018-01-01 2018-02-01 2018-03-01 2018-04-01 2018-05-01 2018-06-01 2018-07-01 2018-08-01 2018-09-01 2018-10-01 2018-11-01 2018-12-01 2019-01-01 2019-02-01 2019-03-01 2019-04-01 2019-05-01 2019-06-01 2019-07-01 2019-08-01 2019-09-01 2019-10-01 2019-11-01 2019-12-01 B C E G H I J K L Prices Returns Stock AB Stock CD Stock EF Stock GH Stock IJ Stock KL Stock AB Stock CD Stock EF Stock GH Stock U 58.26817 52.7562 44.6878 14.1593 225.697 53.271 59.80769 54.424 40.9887 14.8984 223.052 51.3053 2.64% 3.16% -8.28% 5.22% -1.17% 57.35223 49.3235 39.7105 15.3525 224.776 50.7586 -4.11% -9.37% -3.12% 3.05% 0.77% 55.85993 51.7112 40.0397 12.8791 222.523 50.1195 -2.60% 4.84% 0.83% -16.11% -1.00% 56.71877 48.8116 40.4721 11.8673 230.021 50.5349 1.54% -5.61% 1.08% -7.86% 3.37% 56.67609 49.5421 42.8068 12.0998 245.465 51.8892 -0.08% 1.50% 5.77% 1.96% 6.71% 58.71924 50.6114 42.436 11.4399 236.54 52.3148 3.60% 2.16% -0.87% -5.45% -3.64% 59.89139 46.6785 42.3179 12.1202 253.974 53.5274 2.00% -7.77% -0.28% 5.95% 7.37% 61.33771 49.0513 38.9907 12.8978 270.228 52.5313 2.41% 5.08% -7.86% 6.42% 6.40% 60.58638 40.4438 37.642 13.292 268.987 51.6441 -1.22% -17.55% -3.46% 3.06% -0.46% 56.66494 36.1896 39.2978 9.98175 267.408 51.7443 -6.47% -10.52% 4.40% -24.90% -0.59% 56.46145 34.9425 40.4539 12.1945 277.302 59.9215 -0.36% -3.45% 2.94% 22.17% 3.70% 53.1471 32.3059 38.3964 10.0082 239.044 59.7455 -5.87% -7.55% -5.09% -17.93% -13.80% 59.29221 35.1913 37.8914 9.7538 266.515 62.5077 11.56% 8.93% -1.32% -2.54% 11.49% 59.57044 35.4807 42.1901 8.61896 269.098 64.384 0.47% 0.82% 11.34% -11.63% 0.97% 57.86267 35.5106 44.8067 8.15915 272.482 64.7573 -2.87% 0.08% 6.20% -5.33% 1.26% 60.87384 40.0826 45.2346 7.89301 297.768 64.7668 5.20% 12.88% 0.95% -3.26% 9.28% 58.85867 42.5715 44.415 8.66954 293.889 68.4477 -3.31% 6.21% -1.81% 9.84% -1.30% 60.31292 45.942 38.7076 10.4044 305.962 66.1681 2.47% 7.92% -12.85% 20.01% 4.11% 64.61201 48.7642 39.5232 11.9345 313.437 67.8835 7.13% 6.14% 2.11% 14.71% 2.44% 61.27902 50.8539 39.6819 12.1349 318.868 72.2353 -5.16% 4.29% 0.40% 1.68% 1.73% 64.61152 48.3406 40.5476 11.5076 292.857 74.8027 5.44% -4.94% 2.18% -5.17% -8.16% 67.35789 44.2265 38.0383 12.392 298.769 69.9314 4.25% -8.51% -6.19% 7.69% 2.02% 70.45742 48.5431 39.9787 12.3622 313.792 71.2854 4.60% 9.76% 5.10% -0.24% 5.03% 72.39835 45.587 41.0237 11.2123 331.211 67.7056 2.75% -6.09% 2.61% -9.30% 5.55% -3.69% -1.07% -1.26% 0.83% 2.68% 0.82% 2.32% -1.86% -1.69% 0.19% 15.80% -0.29% 4.62% 3.00% 0.58% 0.01% 5.68% -3.33% 2.59% 6.41% 3.55% -6.51% 1.94% -5.02% Stock AB Stock CD Stock EF Stock GH Stock U Stock KL Annual Expected Return Annual Variance Annual Standard Deviation 35 Covariance Matrix Stock AB Stock CD Stock EF Stock GH Stock U Stock KL 37 38 39 Stock AB Stock CD Stock EF Stock GH Stock U Stock KL 40 41 42 Annual Expected Returns of the Stocks Returns Stock AB Stock CD Stock EF Stock GH Stock U Stock KL Risk-Free Rate Risk Aversion coefficient (A) Portfolio Stock AB Stock CD Stock EF Stock GH Stock U Stock KL SUM 65 66 67 68 Expected Return of Portfolio Variance of Portfolio Standard Deviation of Portfolio 69 Equally-Weighted Portfolio Weights Stock AB Stock CD Stock EF Stock GH Stock U Stock KL SUM 80 Expected Return Variance Standard Deviation 82 83 D66 A M N Stock KL Date 2017-12-01 2018-01-01 2018-02-01 2018-03-01 2018-04-01 2018-05-01 2018-06-01 2018-07-01 2018-08-01 2018-09-01 2018-10-01 2018-11-01 2018-12-01 2019-01-01 2019-02-01 2019-03-01 2019-04-01 2019-05-01 2019-06-01 2019-07-01 2019-08-01 2019-09-01 2019-10-01 2019-11-01 2019-12-01 B C E G H I J K L Prices Returns Stock AB Stock CD Stock EF Stock GH Stock IJ Stock KL Stock AB Stock CD Stock EF Stock GH Stock U 58.26817 52.7562 44.6878 14.1593 225.697 53.271 59.80769 54.424 40.9887 14.8984 223.052 51.3053 2.64% 3.16% -8.28% 5.22% -1.17% 57.35223 49.3235 39.7105 15.3525 224.776 50.7586 -4.11% -9.37% -3.12% 3.05% 0.77% 55.85993 51.7112 40.0397 12.8791 222.523 50.1195 -2.60% 4.84% 0.83% -16.11% -1.00% 56.71877 48.8116 40.4721 11.8673 230.021 50.5349 1.54% -5.61% 1.08% -7.86% 3.37% 56.67609 49.5421 42.8068 12.0998 245.465 51.8892 -0.08% 1.50% 5.77% 1.96% 6.71% 58.71924 50.6114 42.436 11.4399 236.54 52.3148 3.60% 2.16% -0.87% -5.45% -3.64% 59.89139 46.6785 42.3179 12.1202 253.974 53.5274 2.00% -7.77% -0.28% 5.95% 7.37% 61.33771 49.0513 38.9907 12.8978 270.228 52.5313 2.41% 5.08% -7.86% 6.42% 6.40% 60.58638 40.4438 37.642 13.292 268.987 51.6441 -1.22% -17.55% -3.46% 3.06% -0.46% 56.66494 36.1896 39.2978 9.98175 267.408 51.7443 -6.47% -10.52% 4.40% -24.90% -0.59% 56.46145 34.9425 40.4539 12.1945 277.302 59.9215 -0.36% -3.45% 2.94% 22.17% 3.70% 53.1471 32.3059 38.3964 10.0082 239.044 59.7455 -5.87% -7.55% -5.09% -17.93% -13.80% 59.29221 35.1913 37.8914 9.7538 266.515 62.5077 11.56% 8.93% -1.32% -2.54% 11.49% 59.57044 35.4807 42.1901 8.61896 269.098 64.384 0.47% 0.82% 11.34% -11.63% 0.97% 57.86267 35.5106 44.8067 8.15915 272.482 64.7573 -2.87% 0.08% 6.20% -5.33% 1.26% 60.87384 40.0826 45.2346 7.89301 297.768 64.7668 5.20% 12.88% 0.95% -3.26% 9.28% 58.85867 42.5715 44.415 8.66954 293.889 68.4477 -3.31% 6.21% -1.81% 9.84% -1.30% 60.31292 45.942 38.7076 10.4044 305.962 66.1681 2.47% 7.92% -12.85% 20.01% 4.11% 64.61201 48.7642 39.5232 11.9345 313.437 67.8835 7.13% 6.14% 2.11% 14.71% 2.44% 61.27902 50.8539 39.6819 12.1349 318.868 72.2353 -5.16% 4.29% 0.40% 1.68% 1.73% 64.61152 48.3406 40.5476 11.5076 292.857 74.8027 5.44% -4.94% 2.18% -5.17% -8.16% 67.35789 44.2265 38.0383 12.392 298.769 69.9314 4.25% -8.51% -6.19% 7.69% 2.02% 70.45742 48.5431 39.9787 12.3622 313.792 71.2854 4.60% 9.76% 5.10% -0.24% 5.03% 72.39835 45.587 41.0237 11.2123 331.211 67.7056 2.75% -6.09% 2.61% -9.30% 5.55% -3.69% -1.07% -1.26% 0.83% 2.68% 0.82% 2.32% -1.86% -1.69% 0.19% 15.80% -0.29% 4.62% 3.00% 0.58% 0.01% 5.68% -3.33% 2.59% 6.41% 3.55% -6.51% 1.94% -5.02% Stock AB Stock CD Stock EF Stock GH Stock U Stock KL Annual Expected Return Annual Variance Annual Standard Deviation 35 Covariance Matrix Stock AB Stock CD Stock EF Stock GH Stock U Stock KL 37 38 39 Stock AB Stock CD Stock EF Stock GH Stock U Stock KL 40 41 42 Annual Expected Returns of the Stocks Returns Stock AB Stock CD Stock EF Stock GH Stock U Stock KL Risk-Free Rate Risk Aversion coefficient (A) Portfolio Stock AB Stock CD Stock EF Stock GH Stock U Stock KL SUM 65 66 67 68 Expected Return of Portfolio Variance of Portfolio Standard Deviation of Portfolio 69 Equally-Weighted Portfolio Weights Stock AB Stock CD Stock EF Stock GH Stock U Stock KL SUM 80 Expected Return Variance Standard Deviation 82 83