In this module, I'm going to show you how to compute an efficient frontier with a

risk-free asset in Excel. And then using the solutions from this

optimization problem, I'm going to show you how to construct a sharp optimum

portfolio. And from the sharp optimum portfolio, I'm

going to show you how to compute the capital asset pricing model, and the

security market line that. That goes with the capital asset pricing

model. We're going to work mainly with two

worksheets. One of them is labeled Risk Free Frontier;

this is where we're going to do the calculation for the Risk Free Frontier.

And the other one is labeled Sharpe Portfolio; this is where we're going to do

a calculation for the soft, Sharpe Optimal Portfolio and.

This security market line. We're going to work with the same 8 asset

classes that we had introduced in the previous Excel module.

Here are the expected returns on these eight risky asset classes.

Here are the variance, covariance elements for the eight risky assets.

I've also listed the volatility but we won't be directly using it.

Now In my portfolio there actually nine positions.

The eight risky positions from before and another position x zero.

Which is the position that I'm going to be holding in the risk free asset.

Now all of these nine positions should add up to be equal to one Which is what is

there in this gray cell. This simply adds up the 9 positions from

B21 through J21. The orange cell here is a constrained cell

that we're going to be using. When we set up the solver for optimizing

for the efficient portfolio. The misc free rate that I'm going to be

using in this problem is 1.5 %. I'm going to be using the risk adjusted

return formulation for constructing in the efficient frontier and in that formulation

I need the risker version parameter which is, given over here, that's 0.1.

This cell just gives you the return on the portfolio.

It you click on that cell, you will see just some product of the risky positions

times their expected return plus the risk free rate times the position that I'm

holding in the risk-free asset. The expression for validity is the same

from before. I take the risky positions and multiply

that by the variance, covariance take the square root multiply that by 100 in order

to represent the volatility in percentages.

This orange cell now lists the objective that I'm trying to maximize this is going

to be the expected return on my entire portfolio that is including both the risk

free part and the risky part. And I'm going to subtract from it the risk

free aversion parameter tau times the variance, which is only associated with

the risk free part. If you click on that one, you will notice

you will get its B27, which is the return on the portfolio minus tau, which is the

risk aversion parameter times probability squared.

And this is what I want to maximize, with the constraint that the position that I

hold in both the risk-free and the risky asset should add up to one.

That is it must be a portfolio. So now let's go to solver.

If you go to solver, here's the optimization problem that has been set up.

The objective is the orange cell B31. I'm going to maximize that by changing the

position that I am holding, subject to the constraint that T21, which is the sum of

the positions, must equal M21, which is just.

Set to be equal to one. You solve this, and you end up getting

that the optimum position is what is listed over here right now.

I put 32% of my initial dollar in the risk-free asset, and the remaining.

68 percent is the dollar amount that I'm going to be spending in the risky assets.

So in the module, in the theoretical module we said that no matter what tau is

the only thing that the investors are going to do is select a certain amount to

put in the risk free asset. Take the remaining amount and put it into

one particular portfolio that I was calling the sharp optimal portfolio.

So in this work sheet I'm going to numerically show you that this is indeed

what happens. So in the rows b 34 through 39, what I

have done Is I've taken the risky positions.

So here, the green cell here, is simply a repeat of the risky positions.

It's B21 all the way up through I21. This is, this is the position that's there

in the last risky asset. I'm taking the sum of those positions, so

here's just some of the whiskey positions and remember the codes the total saying

risk[UNKNOWN] must add up to one I know that I put 32 cents of my initial dollars

into the risk three assets and therefore 68 cents what must be sitting in the risky

asset is exactly what this number is. I'm going to take these risky positions

going to normalize them by the sum to get a portfolio.

And this is the sharpe optimal portfolio. I'm going to run this optimization again

to convince you that no matter what tau is the optimal portfolio, the sharp optimal

portfolio that I end up getting from the calculations exactly the same portfolio.

So lets do that and then I'll go forward and show what happens with the sharpe

ratio and so on. Okay, so I'm going to change the tao

perameter to something larger. So let's say we may get to .2.

So in the risk inversion parameter tao goes up.

I don't like risk and therefor I'll Tend to put more money into the risk-free

asset. So this 0.32 should go up.

But the portfolio that, of the risky asset that I'm going to hold should remain the

same. The only thing that should change is the

amount of dollars that I put into the risky asset versus the risk free asset.

So let's do this optimization. We'll call solver again.

Click on solve. It crank up and now it gives you an

answer. So instead of 32%, now we are putting 66%

of your initial dollar into the risk free asset because your risk aversion parameter

went up. 34% is the amount that you're going to put

in the risky assets. The risky asset positions have changed.

But once you normalize it by the sum of the position of the risky asset, you again

get back the same portfolio. These numbers have not changed.

So here I'm numerically verifying for you what I theoretically told you, that all

that people are going to do in a market where there is free asset, is to diversify

between the risk free asset and the Sharpe Optimal Portfolio.

Now let's just do some calculations on the Sharpe Optimal.

Portfolio. Here I'm listing the excess return on the

sharpe optimal portfolio. I simply take the positions in the sharpe

optimal portfolio multiplying them by the expected return on the various aspects and

subtracting from their, form that sum the risk free rate.

Here is the volatility, same expression as before.

And the ratio of the excess return to volitility is the sharp ratio.

So the sharp ratio for this market turns out to be .76.

So the point of this particular worksheet was to show you how to construct the

optimazation problem for a market with a risk, Risk free asset.

Argue to you numerically, that the Sharp optimal portfolio always remains the same

regardless of what Tau is. In the next worksheet I'm going to repeat

some of these calculations. And then show you how to compute a

security market. See is again the same story.

I have the assets and now I'm ignoring completely all the calculations that we

had done and shown you, going to show you a direct way of getting to the sharpe

optimal portfolio. Here's the risk free rate, here now are

the excess return. Mu hats as I'm going to call them in my,

text modules which is as simply take the expected return and subtract the risk free

rate. So that's what I'm doing over here.

In order the calculate the Sharpe optimal portfolio, I know that the position that

I'm going to hold are going to be V inverse mu_hat.

So the quantity over here is just in-, v inverse mu_hat, that's the calculation

that is being done here. If you click on this, it's going to be the

inverse of this matrix times mu_hat that will give you the answer.

So that's the positions, still not a portfolio.

I take the sum of those positions. And I end up getting 1364.93.

I divide out by this sum to get a portfolio.

And the portfolio that you end up getting, exactly the same one that we saw in the

risk-free frontier version. Mean excess return volatility is the

optimum Sharpe ratio to be 0.76. Now what I've done here is computed the

betas. What is the BETA?

Beta is the correlation between the asset, and the shop optimal portfolio.

If you work through the calculations, what it's going to be doing is taking the

corresponding, the rule of the variance, co-variance matrix, or equal.

Equivalently the column of the variance co-variance matrix corresponding to that

particular asset and multiplying it to the sharpe optimal portfolio.

So if you look at this cell what has been done is its taken the column, I take the

sharpe optimal portfolio, multiply them together that'll give me the co-variance

between the particular asset and the sharpe optimal portfolio.

Divided by the volatility of the shop optimal portfolio, that gives you the

beta. Now why am I doing this with respect to

the shop optimal portfolio whereas in the notes I'm going, I was telling you that

the beta is defined in terms of the market portfolio?

This is because I've already argued to you in the theoretical part of the module that

the shop optimal portfolio is in fact the market portfolio.

And therefore in the theoretical calculation that we want to do in this

sheet. I might, might as well replace the Sharp

optimal portfolio for the market portfolio, that's what I've done.

I've computed the beta by looking at the covariance.

Of the particular asset and the shop optimal portfolio divided by the

volatility of the shop optimal portfolio. These are the not, these are the different

betas, some of them are positvely correlated, some of them are negatively

correlated. Here is a positive correlation.

Positive and all of these are negatively correlated to the market.

What do I do in the next line? I look at the implied return, how do I

look at the in-flight attorney nine use the security market line's.

You click on any one of them. If you look at the excess return must be

equal to the excess return of the sharp optical portfolio or equal to the market

portfolio times the beta of the assets so the implied returns should simply be B33

which is the beta times the excess return in the market or equally in this

particular case the excess returned shopped optimal portfolio.

I'm going to compare that with the excess return that I'd previously computed So

it's 1.65. If you look at this error this is going to

be what is the applied return, minus the excess return.

And that's what I'm putting in this cell down below.

And it turns out that for this particular market, all these numbers turn out to be

zero. For those of you who have been carefully

looking at my numbers that I plotted in the text module you'll see that for asset

one and asset seven it doesn't follow the line exactly but here you're getting all

the values to be equal to zero.the difference between these two is thoughtful

I showed you in the notes were based off of Matlab in math lab has a higher

precision than Excel It actually brings up an interesting question.

If the precision of the program that you're using to compute numbers can make a

difference between whether a particular asset is efficient or inefficient.

That's a very non-robust or fragile way of thinking about mean variance portfolio

selection. It tells you that mean variance portfolio

selection, in some way when you implement it, you have to be very careful.

You have to make sure that numerical errors don't play a role, you have to make

sure that the statistical estimation errors don't play a role.

And that's something that we're going to come to in the modules that refer to the

practical Details of mean variance portfolio selection.

There's one thing else that I w-, I want to mark out here before we leave this

excel module. If you look at the sharp optimal

portfolios, some of them are positive components, 1.25 0.12, 0.2, 0.04.

But then they have negative components. Minus .11, minus .05, minus .1 and so on.

Now, I have argued to you if the markets are in equilibrium, if everybody's a mean

variance optimizer, then everybody holds a sharp optimal portfolio and therefore the

market portfolio is a Sharpe Optimum portfolio.

Now if everybody constructs their portfolio using the sharp optimal

portfolio then everybody is going to short in this particular case international

bonds and say US small cab growth. If everybody shorts these 2 assets the

market can never be in equilibrium. So here is an example of data.

Which gives you a Sharpe Optimum portfolio that cannot market in equilibrium and if

this data is representative of the market you could easily argue that one of the

basic assumptions besides capital asset pricing model which is that the market

should be in equilibrium is violated..