+1 (208) 254-6996 [email protected]
Select Page

Excel Project (Efficient portfolios and Efficient frontier)

You are assigned with five stocks  and you are expected to build the investment portfolios using those five stocks. Note that I showed you an example of a three-stock portfolio in the attached excel file. You are expected to apply the same concepts to the five-stock portfolios.

Don't use plagiarized sources. Get Your Custom Essay on
Excel Project
Just from \$13/Page

Please collect monthly stock price for the five companies that you are assigned from FINANCE.YAHOO.COM and employ the data time range from Jan 1, 2015 to Oct 30, 2020. Then using the risk-free rate of 0.33%, you are expected to develop the complete portfolio and develop the efficient frontier of these six securities (T-bill and five stocks). Following are the procedure in details:

1) Collect monthly stock price data from Finance.YAHOO.COM. The time range for the date should be from Jan 1, 2015 to Oct 30, 2020.

2) Compute monthly stock returns for each stock ( monthly rate of return = (P1 – P0) /P0; P is monthly adjusted close price). Then compute mean and standard deviation of stock returns for each company (See my excel example with the sheet entitled ‘Data’)

3) Develop Table of Covariance of these five stocks (See my excel example with the sheet entitled ‘Data’)

4) Develop Efficient Frontier for all risky portfolios constructed with 5 assigned stocks (See my excel example with the sheet entitled ‘EF’ and ‘Graph’)

5) Find the Minimum-Variance risky portfolio (See my excel example with the sheet entitled ‘MVP’)

6) Find the Efficient portfolios including a risk free asset with risk free rate of 0.33% (See my excel example with the sheet entitled ‘Sharpe Ratio for CML’ )

7) Draw the capital market line (CML) (See my example with the sheet entitled ‘Sharpe Ratio for CML’ and ‘Graph’)

### Video Clip for Excel project：

The attachments are the Companies assigned,  An example of a three-stock portfolio (excel template), and Portfolio Optimization Project Slides.

&A

Page &P

Solve List

## Graph

efficient frontier 0.0704793579016931 0.062094721368613 0.054521703149259 0.0481229449885874 0.0434246455348512 0.0410206293673384 0.0413100805473508 0.0442381056029334 0.0493396832553761 0.0560235417250364 0.0637942680682251 0.0723022722780676 0.0813164623065385 0.0906860189599101 0.100311412884134 0.110125581052238 0.120082247549928 0.130148712165965 0.140301343213612 0.150522706140621 0.160799694488253 0.171122286562571 0.181487889406868 0.191874810650129 0.202298946853395 0.212735523772302 0.223196977851075 0.233675452917815 0.244168757620845 0.254675058902781 0.265192812088546 0.004 0.006 0.008 0.01 0.012 0.014 0.016 0.018 0.02 0.022 0.024 0.026 0.028 0.03 0.032 0.034 0.036 0.038 0.04 0.042 0.044 0.046 0.048 0.05 0.052 0.054 0.056 0.058 0.06 0.062 0.064 Stocks 0.0780902519637998 0.0481039403180182 0.0616222205920844 0.0262018513268502 0.0108851976477998 0.0197152450303428 MVP 0.0408044213249035 0.0147908882617867

risk

return

efficient frontier 0.0704793579016931 0.062094721368613 0.054521703149259 0.0481229449885874 0.0434246455348512 0.0410206293673384 0.0413100805473508 0.0442381056029334 0.0493396832553761 0.0560235417250364 0.0637942680682251 0.0723022722780676 0.0813164623065385 0.0906860189599101 0.100311412884134 0.110125581052238 0.120082247549928 0.130148712165965 0.140301343213612 0.150522706140621 0.160799694488253 0.171122286562571 0.181487889406868 0.191874810650129 0.202298946853395 0.212735523772302 0.223196977851075 0.233675452917815 0.244168757620845 0.254675058902781 0.265192812088546 0.004 0.006 0.008 0.01 0.012 0.014 0.016 0.018 0.02 0.022 0.024 0.026 0.028 0.03 0.032 0.034 0.036 0.038 0.04 0.042 0.044 0.046 0.048 0.05 0.052 0.054 0.056 0.058 0.06 0.062 0.064 cap market line 0 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.1 0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18 0.19 0.2 0.21 0.22 0.23 0.24 0.25 0.26 0.27 0.28 0.29 0.00333333333333333 0.00671137302457121 0.0100894127158091 0.013467452407047 0.0168454920982849 0.0202235317895227 0.0236015714807606 0.0269796111719985 0.0303576508632364 0.0337356905544743 0.0371137302457121 0.04049176993695 0.0438698096281879 0.0472478493194258 0.0506258890106637 0.0540039287019016 0.0573819683931394 0.0607600080843773 0.0641380477756152 0.0675160874668531 0.070894127158091 0.0742721668493288 0.0776502065405667 0.0810282462318046 0.0844062859230425 0.0877843256142804 0.0911623653055182 0.0945404049967561 0.097918444687994 0.101296484379232 assets 0.0780902519637998 0.0481039403180182 0.0616222205920844 0.0262018513268502 0.0108851976477998 0.0197152450303428 efficient frontier 0.0704793579016931 0.062094721368613 0.054521703149259 0.0481229449885874 0.0434246455348512 0.0410206293673384 0.0413100805473508 0.0442381056029334 0.0493396832553761 0.0560235417250364 0.0637942680682251 0.0723022722780676 0.0813164623065385 0.0906860189599101 0.100311412884134 0.110125581052238 0.120082247549928 0.130148712165965 0.140301343213612 0.150522706140621 0.160799694488253 0.171122286562571 0.181487889406868 0.191874810650129 0.202298946853395 0.212735523772302 0.223196977851075 0.233675452917815 0.244168757620845 0.254675058902781 0.265192812088546 0.004 0.006 0.008 0.01 0.012 0.014 0.016 0.018 0.02 0.022 0.024 0.026 0.028 0.03 0.032 0.034 0.036 0.038 0.04 0.042 0.044 0.046 0.048 0.05 0.052 0.054 0.056 0.058 0.06 0.062 0.064 Stocks 0.0780902519637998 0.0481039403180182 0.0616222205920844 0.0262018513268502 0.0108851976477998 0.0197152450303428 MVP 0.0408044213249035 0.0147908882617867

risk

return

efficient frontier 0.0704793579016931 0.062094721368613 0.054521703149259 0.0481229449885874 0.0434246455348512 0.0410206293673384 0.0413100805473508 0.0442381056029334 0.0493396832553761 0.0560235417250364 0.0637942680682251 0.0723022722780676 0.0813164623065385 0.0906860189599101 0.100311412884134 0.110125581052238 0.120082247549928 0.130148712165965 0.140301343213612 0.150522706140621 0.160799694488253 0.171122286562571 0.181487889406868 0.191874810650129 0.202298946853395 0.212735523772302 0.223196977851075 0.233675452917815 0.244168757620845 0.254675058902781 0.265192812088546 0.004 0.006 0.008 0.01 0.012 0.014 0.016 0.018 0.02 0.022 0.024 0.026 0.028 0.03 0.032 0.034 0.036 0.038 0.04 0.042 0.044 0.046 0.048 0.05 0.052 0.054 0.056 0.058 0.06 0.062 0.064 Stocks 0.0780902519637998 0.0481039403180182 0.0616222205920844 0.0262018513268502 0.0108851976477998 0.0197152450303428 MVP 0.0408044213249035 0.0147908882617867

risk

return

efficient frontier 0.0704793579016931 0.062094721368613 0.054521703149259 0.0481229449885874 0.0434246455348512 0.0410206293673384 0.0413100805473508 0.0442381056029334 0.0493396832553761 0.0560235417250364 0.0637942680682251 0.0723022722780676 0.0813164623065385 0.0906860189599101 0.100311412884134 0.110125581052238 0.120082247549928 0.130148712165965 0.140301343213612 0.150522706140621 0.160799694488253 0.171122286562571 0.181487889406868 0.191874810650129 0.202298946853395 0.212735523772302 0.223196977851075 0.233675452917815 0.244168757620845 0.254675058902781 0.265192812088546 0.004 0.006 0.008 0.01 0.012 0.014 0.016 0.018 0.02 0.022 0.024 0.026 0.028 0.03 0.032 0.034 0.036 0.038 0.04 0.042 0.044 0.046 0.048 0.05 0.052 0.054 0.056 0.058 0.06 0.062 0.064 cap market line 0 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.1 0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18 0.19 0.2 0.21 0.22 0.23 0.24 0.25 0.26 0.27 0.28 0.29 0.00333333333333333 0.00671137302457121 0.0100894127158091 0.013467452407047 0.0168454920982849 0.0202235317895227 0.0236015714807606 0.0269796111719985 0.0303576508632364 0.0337356905544743 0.0371137302457121 0.04049176993695 0.0438698096281879 0.0472478493194258 0.0506258890106637 0.0540039287019016 0.0573819683931394 0.0607600080843773 0.0641380477756152 0.0675160874668531 0.070894127158091 0.0742721668493288 0.0776502065405667 0.0810282462318046 0.0844062859230425 0.0877843256142804 0.0911623653055182 0.0945404049967561 0.097918444687994 0.101296484379232 assets 0.0780902519637998 0.0481039403180182 0.0616222205920844 0.0262018513268502 0.0108851976477998 0.0197152450303428 efficient frontier 0.0704793579016931 0.062094721368613 0.054521703149259 0.0481229449885874 0.0434246455348512 0.0410206293673384 0.0413100805473508 0.0442381056029334 0.0493396832553761 0.0560235417250364 0.0637942680682251 0.0723022722780676 0.0813164623065385 0.0906860189599101 0.100311412884134 0.110125581052238 0.120082247549928 0.130148712165965 0.140301343213612 0.150522706140621 0.160799694488253 0.171122286562571 0.181487889406868 0.191874810650129 0.202298946853395 0.212735523772302 0.223196977851075 0.233675452917815 0.244168757620845 0.254675058902781 0.265192812088546 0.004 0.006 0.008 0.01 0.012 0.014 0.016 0.018 0.02 0.022 0.024 0.026 0.028 0.03 0.032 0.034 0.036 0.038 0.04 0.042 0.044 0.046 0.048 0.05 0.052 0.054 0.056 0.058 0.06 0.062 0.064 Stocks 0.0780902519637998 0.0481039403180182 0.0616222205920844 0.0262018513268502 0.0108851976477998 0.0197152450303428 MVP 0.0408044213249035 0.0147908882617867

risk

return

Portfolio Optimization

Excel Project

Portfolio Mean-Variance Analysis

To construct a n-Asset portfolio

The expected return on a portfolio is the weighted average of the expected returns of the individual assets in the portfolio.

w1 + w2 + w3 …+ wn = 1

Portfolio variance is the weighted sum of all the variances and covariances:

There are n variances, and n2 – n covariances

All feasible/Attainable portfolios

All feasible/Attainable portfolios lie inside a bullet-shaped region, called the minimum-variance boundary or frontier.

Feasible set

Efficient Portfolios and Efficient Frontier

Mean-Variance Criterion suggests that investors should only choose efficient portfolios. An efficient portfolio is one that offers:

the most return for a given amount of risk, or

the least risk for a give amount of return.

The collection of efficient portfolios is called the efficient frontier for risky securities.

Minimum Variance Portfolio (MVP)

How can we find the weights of the securities so that we can minimize the variance of the portfolio?

The Optimal Risky Portfolio With A Risk-Free Asset

With a risk-free asset available, investors choose the efficient portfolios on the capital allocation line with the steepest slope

E(rA)

A

E(r)

Efficient Frontier for risky portfolio

E(rP)

CAL (M)

P

E(r)

s

The Capital Market Line (CML)

M

E(rP&F)

F

Risk Free

P&F

Efficient Frontier for risky portfolio

E(rP)

P

P&F

E(rP&F)

CAL (M) = CML

The optimal CAL is called the Capital Market Line or CML

The CML dominates the EF for risky portfolio

6-

The Capital Market Line (cont.)

The Capital Market Line (CML) is all linear combinations of the risk-free asset and Tangent Portfolio M and it is the new efficient frontier.

Slope =Sharpe Ratio = (E(rp) – rf) / standard deviation of portfolio, that is the return per unit of risk.

CML maximizes the slope or the return per unit of risk or it equivalently maximizes the Sharpe ratio

What does the CML tell us?

The expected rate of return on any efficient portfolio (p) is equal to the risk-free rate plus Slope of Tangent Portfolio times the risk of the portfolio (p).

Return on P = Risk free rate + Sharpe ratio of Tangent Portfolio x Risk of P

=++×××+=

å

1122

ˆˆˆˆˆ

pnnii

i

rwrwrwrwr

p

m

f

m

f

p

r

r

r

r

s

s

´

+

=

ˆ

Order your essay today and save 10% with the discount code ESSAYHELP