Optimal Risky Portfolio Investment Opportunity Set Paper

Content Type

User Generated

User

RzvylPnaaa

Subject

Other

Description

Excel Project based on the introduction.
Following all the steps in the PDF file.

portfolio optimization and passive portfolio management strategy

Unformatted Attachment Preview

DATE 5/1/2014 6/1/2014 7/1/2014 8/1/2014 9/1/2014 10/1/2014 11/1/2014 12/1/2014 1/1/2015 2/1/2015 3/1/2015 4/1/2015 5/1/2015 6/1/2015 7/1/2015 8/1/2015 9/1/2015 10/1/2015 11/1/2015 12/1/2015 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 9/1/2017 Price- AAPL Price- AMZN Price- TGT 79.96 85.50 87.96 94.31 93.16 99.86 109.97 102.50 108.80 119.29 116.01 116.68 121.46 117.43 113.56 105.72 103.73 112.38 111.25 99.41 91.93 91.32 103.50 89.01 94.83 91.33 99.56 101.36 108.59 109.06 106.16 111.82 117.16 132.26 139.30 139.29 148.13 140.23 144.81 159.68 150.65 312.55 324.78 312.99 339.04 322.44 305.46 338.64 310.35 354.53 380.16 372.10 421.78 429.23 434.09 536.15 512.89 511.89 625.90 664.80 675.89 587.00 552.52 593.64 659.59 722.79 715.62 758.81 769.16 837.31 789.82 750.57 768.66 823.48 845.04 886.54 924.99 994.62 968.00 987.78 980.60 961.35 47.99 49.36 50.76 51.17 53.87 53.13 63.60 65.74 63.75 66.54 71.56 68.74 69.17 71.65 71.85 68.34 69.54 68.23 64.09 64.69 64.52 69.90 73.92 71.42 61.79 63.20 68.19 63.54 62.67 62.71 70.48 66.46 59.33 54.08 51.25 51.87 51.22 49.09 53.20 51.19 56.02 R - AAPL 0.067 0.028 0.070 -0.012 0.069 0.096 -0.070 0.060 0.092 -0.028 0.006 0.040 -0.034 -0.033 -0.072 -0.019 0.080 -0.010 -0.113 -0.078 -0.007 0.125 -0.151 0.063 -0.038 0.086 0.018 0.069 0.004 -0.027 0.052 0.047 0.121 0.052 0.000 0.061 -0.055 0.032 0.098 -0.058 0.092 10/1/2017 11/1/2017 12/1/2017 1/1/2018 2/1/2018 3/1/2018 4/1/2018 5/1/2018 6/1/2018 7/1/2018 8/1/2018 9/1/2018 10/1/2018 11/1/2018 12/1/2018 1/1/2019 2/1/2019 3/1/2019 4/1/2019 165.23 167.98 166.01 164.25 174.74 165.26 162.78 184.07 183.04 188.16 225.08 223.99 217.17 177.20 157.07 165.73 172.41 189.95 195.69 1105.28 1176.75 1169.47 1450.89 1512.45 1447.34 1566.13 1629.62 1699.80 1777.44 2012.71 2003.00 1598.01 1690.17 1501.97 1718.73 1639.83 1780.75 1818.86 56.05 56.86 62.59 72.15 72.33 67.15 70.21 70.49 74.25 78.69 85.35 86.72 82.21 69.76 65.51 72.36 72.00 80.26 81.38 0.016 -0.012 -0.011 0.062 -0.056 -0.015 0.123 -0.006 0.028 0.179 -0.005 -0.031 -0.203 -0.121 0.054 0.040 0.097 0.030 AAPL MEAN RETURN STD DEV. 0.015 R - AMZN 0.038 -0.037 0.080 -0.050 -0.054 0.103 -0.087 0.133 0.070 -0.021 0.125 0.018 0.011 0.211 -0.044 -0.002 0.201 0.060 0.017 -0.141 -0.061 0.072 0.105 0.092 -0.010 0.059 0.014 0.085 -0.058 -0.051 0.024 0.069 0.026 0.048 0.042 0.073 -0.027 0.020 -0.007 -0.020 0.140 R - TGT 0.028 0.028 0.008 0.052 -0.014 0.180 0.033 -0.031 0.043 0.073 -0.040 0.006 0.035 0.003 -0.050 0.017 -0.019 -0.063 0.009 -0.003 0.080 0.056 -0.034 -0.145 0.023 0.076 -0.071 -0.014 0.001 0.117 -0.059 -0.114 -0.093 -0.054 0.012 -0.013 -0.042 0.080 -0.038 0.090 0.001 ER- AAPL 0.052 0.014 0.056 -0.025 0.056 0.084 -0.081 0.047 0.080 -0.038 -0.005 0.029 -0.044 -0.045 -0.084 -0.033 0.065 -0.023 -0.126 -0.095 -0.026 0.105 -0.168 0.042 -0.058 0.064 -0.002 0.048 -0.015 -0.046 0.031 0.027 0.102 0.037 -0.014 0.047 -0.067 0.017 0.083 -0.069 0.079 ER- AMZN 0.009 -0.067 0.049 -0.080 -0.086 0.070 -0.119 0.099 0.038 -0.053 0.093 -0.013 -0.019 0.180 -0.072 -0.031 0.172 0.035 -0.008 -0.166 -0.090 0.040 0.075 0.063 -0.036 0.031 -0.013 0.058 -0.084 -0.079 -0.007 0.038 -0.004 0.018 0.014 0.044 -0.054 -0.008 -0.036 -0.051 0.106 ER- TGT 0.019 0.019 0.000 0.043 -0.021 0.172 0.028 -0.035 0.038 0.069 -0.043 0.003 0.032 0.000 -0.053 0.013 -0.023 -0.067 0.003 -0.008 0.074 0.052 -0.037 -0.149 0.015 0.068 -0.076 -0.022 -0.008 0.108 -0.064 -0.121 -0.104 -0.069 -0.006 -0.031 -0.062 0.058 -0.058 0.068 -0.019 0.063 -0.006 0.216 0.042 -0.044 0.079 0.040 0.042 0.045 0.124 -0.005 -0.226 0.056 -0.118 0.135 -0.047 0.082 0.021 AMZN 0.014 0.096 0.142 0.003 -0.074 0.045 0.004 0.052 0.058 0.081 0.016 -0.053 -0.164 -0.063 0.099 -0.005 0.109 0.014 TGT 0.030 0.009 0.007 -0.021 -0.021 0.050 -0.064 -0.028 0.108 -0.012 0.020 0.174 0.009 -0.016 -0.191 -0.140 0.007 -0.006 0.050 0.007 0.035 -0.032 0.188 0.026 -0.058 0.060 0.026 0.031 0.036 0.119 0.003 -0.218 0.033 -0.135 0.091 -0.069 0.038 -0.004 -0.006 0.075 0.126 -0.006 -0.083 0.030 -0.008 0.039 0.049 0.077 0.020 -0.046 -0.164 -0.090 0.054 -0.037 0.065 0.002 VARIANCE-COVARIANCE MATRIX R - AAPL R - AAPL R - AMZN R - TGT R - AMZN R - TGT
Purchase answer to see full attachment

Tags: business and finance exchange models portfolio project Optimal Risky Portfolio Managemen Stratety

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Find the solution sheets attached herewith.

4/10/2019

Optimal Risky Portfolio

Student Name
INSTITUTE NAME

Table of Contents
Finance Project-1 .....................................................................................................2
1. Optimal Risky Portfolio.......................................................................................2
2. Methodology ........................................................................................................3
3. Return on Risk Based Investments ......................................................................4
4. Return on the Risk Free Investments ...................................................................4
5. Calculating Optimal Risky Portfolio ...................................................................4
6. Conclusion ...........................................................................................................5

1|Page

Finance Project-1
1. Optimal Risky Portfolio
When the market is on a roll, even the greatest fund manager faces one dilemma. The
dilemma is not to identify which stocks to go with but to identify how much money to go
in with. This is due to the reason that greater return attracts greater risk. Thus, a fund
manager, who has already communicated his risk appetite for different funds to the
stakeholders, can never enter into a venture which does not meet his risk appetite. This
matter becomes highly significant for the fund managers who are managing a pool of
investors, as their investors believe that they would invest within the communicated risk
perimeters. Any loss incurred due to excessive risk or opportunity forgone due to underutilized funds could be fatal to the career of the fund managers.
Keeping above matter in view, Optimal Risky Portfolio provides a solution to the fund
managers. Using artificial intelligence, excel based calculation creates various investment
mix/ portfolios combinations, along with the return associated with each choice. Different
combinations ensure that the risk is appropriately shared over a portfolio. Thus, the fund
manager has to establish the risk appetite and the built-in formulas ensure that he/she
exactly knows the expected return along with the amount of investment to be made in
each portfolio. Since various possibilities are already mapped, therefore, the fund
manager can also identify the excess return which a slight change in risk appetite would
attract. Providing the fund manager with extra time to concentrate on refining his/her
judgment, gathering market Intel. etc., while leaving the historic data-based analysis to
the Microsoft Excel. This calculation could come in handy in case of any inquiry
conducted by law enforcement agencies and withstand independent audit as the
calculation provides concrete justification/ evidence for the basis used by a fund manager
to make the investment in different portfolios.
Graphical representation of the Optimal Risk Portfolio helps the fund manager in
identifying and remembering the historic trend. Further, it can assist the portfolio

2|Page

manager in presenting his decisions to higher management or stakeholders and make it
easier for him to communicate complex analytics through graphical methods.

2. Methodology
There are 3 steps to the creation of Optimal Risky Portfolio.
Identifying return on Risk-Based Investments
Identifying return on Risk-Free Investments
Calculating Optimal Risky Portfolio
Now we will discuss the first step to the creation of Optimal Risky Portfolio which
establishes the returns from Risk-Free Investments. But before that, we would have to do
some basic data building exercise and establish some important business indicators like
return, variance, standard deviation, etc.
In this assignment, Past 5 year’s price trends of Apple (APPL), Amazon (AMZN) &
Target (TGT) have been used to determine the return and excess return available on
them. This is the actual raw data which provides the foundation to the whole calculation.
Thus, the source of data must be accurate and reliable; further, a high level of diligence
must be applied to ensure that the data is correct as an error in this could alter the overall
analysis.
Now, using the Variance-Covariance Matrix, the variances & standard deviations of the
companies are determined. The variance and standard deviation is a measure to identify
how spread out a data set is. Thus, using historical data to ...