In this workout, we're asked to calculate the weights for each asset that would result in a optimal or tangent portfolio given the data below.

We're also told to assume that we have a long only portfolio and no leverage or Lending.

For Excel to be able to perform this for us. We need to have the Excel solver add in enabled and to do that. You need to go to your Excel options and then the add-ins section and enable the solver.

To check if you have it enabled already or not. We need to go to the data tab of the ribbon. And then on the top right hand side you should be able to see.

We solver function.

So once we have the solver add-in enabled we can then go down and look at our data.

The key thing to note about this workout where we're looking for the optimal or tangent portfolio is that the tangent portfolio is the one which has the highest sharp ratio.

So what we're going to do to begin with is the calculate the sharp ratio using some dummy weights and then use the solver function to find the weights that give us the optimal or highest sharp ratio and therefore the optimal tangent portfolio.

To calculate the shop ratio. What we need is the expected portfolio return and we need to deduct from that the risk-free return and then to divide that by the portfolio risk or standard deviation.

Given the expected returns for assets a b and c the only three assets and our portfolio individually here and we're giving the standard deviations for those three assets as well.

And we've got a correlation Matrix as well.

The final bit of data were given is the risk free rate of 2% So we've got one of the inputs there for the shop ratio the risk free rate of return of 2% The first thing we're going to calculate is the expected Return of the portfolio.

Now to calculate the expected Return of the portfolio. I'm going to need to enter some variables for the weights that we have and I'm just going to go with 25% in a 25% in B. And then first see what I'm going to need to calculate here. He weight being one minus the other weights that we already have so that we end up with an overall portfolio that is made up through investing in these three assets only.

I can then calculate the expected return by multiplying together. So using the sum product function.

the portfolio weights And then comma and the returns of each asset individually, so the 8.8 13.2 and 16.5 for a b and c respective.

This will calculate for us the expected return of my portfolio that has 25% and a 25% B and 50% incident.

Before we have a calculating the portfolio standard deviation is a bit more complicated to calculate portfolio risk. You need to look at the risk of each asset on its own. So standard deviation squared times weight squared for each asset a b and c.

But then we also need an individual term for the interrelationship between every pair of assets.

You can only calculate the correlation between two assets at a time. So we need to calculate the interaction between asset A and B. So two times the weight of a times the weight of B times the correlation between A and B times the risk of 8 times the risk of B the same calculation for the interrelationship between assets B and C. And then finally the same calculation for the interrelationship between assets A and C. We've done this calculation for you already.

in cell C30 here which will give the portfolio variance of 0.13 079 given the weights that we have at the moment.

To calculate the portfolio standard deviation. We then need to take these square root of the portfolio variance.

And then finally we can calculate these sharp ratio.

By taking the expected return on the portfolio minus the risk free rate of return of 2% and divide that by our portfolio standard deviation.

This gives us a sharp ratio of 0.325, which is maybe a nice number. We can't really tell we don't know what other portfolio sharp ratios. We might get with different weights of those three assets.

And this is where we're going to use the solver function. So if we go into the solver function, so using the shortcuts, it's alt a for data and then Y2.

That will then take you into the solver function. We should be able to see a screen that looks some what like this now. I've got the variables input already for us. So what we need to do is to set the objective the sharp ratio in c34.

To be as big as possible to its maximum now, we're going to change just the weightings of A and B.

That's in c24 and 25, but the constraints that we're going to implement here. Are there each waiting has to be greater than or equal to zero.

If we then run this solver function, we will see that we'll get new weights for assets A and B and hopefully a higher shop ratio.

So the moment with the weights that we have we're getting a shot ratio of 0.25. But then if we run the solver function We now get new weights and these new weights show that which should have 62.1% in USA a 34.1 in B and only 3.9 in C. And that will give us a higher sharp ratio of 0.373.

You're more than welcome to try any different combinations of weights for a b and c but you won't be able to find a higher shop ratio than this 0.373. This is the strength of the solver function.