Transcript:
Hello, and welcome in this video. We’ll take a look at how to compute the efficient frontier for the returns, the portfolio returns for portfolio of stocks so in this example. I am going to assume two stocks one is Best. Buy and the other is AT&T. What I do is I go to the website. Yahoo Finance Road Yahoocom and I download the stock returns on the monthly stock returns for both these talks from January 1st, 2014 through January 1st, 2015 You can scroll all the way down and you can download to a spreadsheet. So here is the downloaded data. I’ve just retain the date and the adjusted close. I’ve deleted all the other stuff and you can see that. These are monthly returns, starting from 1st from from January or 2014 to December 2014 for both AT&T and Best. Buy the adjusted closed prices reflect the closing prices of these stocks at the end of the day or as on this date and they’re adjusted for stock splits and dividends and other corporate events. Since these dates are identical. I’m also going to delete these dates. So first we compute the returns for both these stock. The returns for AT&T are the present months. Stock price divided by the previous month. Stock price minus one. And you can just use the same formula for Best. Buy as well and you can copy this formula by clicking on the bottom right, fill, handle over here. When you hover your mouse on it, it’ll become up, you know, a dark plus sign and just select drag this formula all the way down and you don’t have to retain this value here because we don’t have the previous month’s prices. This will appear to be an error so you can delete that, and you can also format this as percentage if you want. I’ll just read in a couple of decimal places. We can now compute the average the variance, the standard deviation, the covariance and the correlation, so the mean or the variance is now the mean is nothing but the average return of AT&T over the 12-month. And you can copy this formula to the right. The variance is nothing, but the spread. It’s a measure of the spread of the returns over the 12 months, and the standard deviation is the square root of the variance or it has its own formula as well. Finally, the covariance, The Covariance is a measure of how the stock returns of AT&T and best by Covary with each other if the covariance is positive and high. Then that means there’s a lot of, you know, if one Rises the other Rises and so on if one Falls the other Falls, so let’s measure covariance of AT&T returns, comma, Best Buy returns and correlation is just a kind of a normalized covariance so correlation is covariance normalized to say between minus 1 and 1 You can think of it that way. So there is a negative correlation between AT&T invest, buy the most negative it could be is minus 1 but it’s minus 0.33 and the most positive it could be is 1 is positive 1 so that’s a range of correlation. Now if you have a portfolio of both these talks, so that means, let’s say you have 50% of AT&T and the rest of it as Best. Buy so basically. If you have $100 you allocate 50 dollars to buy AT&T and 50 dollars to buy Best. Buy so if that’s the case, then you have constructed a portfolio. The return of the portfolio is the weighted returns of AT&T and Best. Buy so the AT&T return times AT&T Weight plus Best. Buy return times best buy weight and you can change the E 1 to a dollar sign and the purpose of this. Is that if you want to copy this formula down as I will in a short while then you can copy it easily. I just want to change the format. Here, just decrease the decimal places. It should be fine, and now if you just click on the fill handle, you can copy this formula all the way down and you can see, for example at some random cell. Here you can see the same formula that was here has been applied. So you don’t have a cop? You don’t have to keep typing the formula again. And now if you want to find the mean of the portfolio return, you do the exact same thing, in fact. I can copy this formula over here. So this would be the average of all these returns here. This is a portfolio. Return the portfolio variance again. I can copy this here. And the portfolio standard deviation can also be copied like this. There is another way to compute the portfolio mean variance and standard deviation, and that is to basically take the proportion of these talks and multiply them by the mean of 8080 returns and add it up with the weighted mean of Best Buy when it comes to variance. There’s a slightly more complicated formula, so the portfolio variance is given by this formula. So let me just denote a T and T by T here and Des Pi by B So XT refers to this 50% here or whatever is a proportion of atat in the overall returns and XB refers to this proportion of the best buy in the overall portfolio RT refers to the return for 8080 and the variance of RT refers to this value here. RB refers to the return for Best Buy and the variance of RB refers to this value here and the covariance of RT and RB refers to this figure here, so we apply this formula here to compute the variance of the portfolio and that is the proportion of a 20 squared times The variance of AT&T, plus the proportion of the span squared times the variance of this buy, plus 2 times the proportion of 80 80 times the proportion of best buy times the covariance of the two stocks. If you press. Enter, you should get pretty much the same figure. In fact here. If I increase the number of decimal places, it will be identical, so these two are really the same figure computed in different ways and the standard deviation is just the square root of that, so we don’t have to focus on that now. The neat thing with this formula is that now we can actually construct a set of scenarios where we can’t be error, looking at different of different proportions of AT&T and Best Buy stock. So supposing you have 0% of AT&T stock? Then you will have 100% minus of 0 percentages, and if you have 10% or 20% and so on and if I were to just copy this all the way till 100% you would correspondingly have lesser and lesser of this buy stock. The portfolio mean can now be calculated as the weighted average, The weighted sum of the means that is AT&T is weight. H Times the mean for AT&T plus best buys weight. H times the mean for Best. Buy me in return for this By now before we copy this down. We also need to make a change to the values of D 16 + E 16 We will make it into dollar signs the way you do. That is just click on the that particular formula and press f4 and Litzer dollar signs. So you can copy it conveniently. Now we compute variance using this formula that we saw a little bit earlier, so the variance is nothing. But the proportion of ATT squared times variance of a DD + the proportion of BestBuy squared times the variance of S by plus 2 times proportion of a TNT Times proportional BestBuy times the covariance and again we have to make sure to put the dollar signs in front of D 17 + G 17 and D 19 the center. Double click on this fill handle, and you will get this here. Standard deviation is nothing but the square root of variance so raised to the power 1 by 2 and copy this all the way down. So now that you have this standard deviation and the mean you can draw a nice graph That shows the efficient frontier so to do that, go to insert scatter and you can choose one of these scatter with smooth lines and markers okay now. I am going to remove all the data already there and just select it afresh. Click on add and X values or all these standard deviation values. These are the risk. These are a measure of the risk riskiness of stock or the portfolio in this case and the Y values are the main returns and these represent the returns, obviously of the portfolio. And if you select those two, then you will get a nice curve like this. Just click. OK for now and okay, so what this curve represents is on the x-axi’s. It represents the standard deviation or the riskiness, the higher the X axis value, the higher the more risky, the portfolio of stock and the Y axis represents the return or the reward that you get for holding that stock so the higher the value of the Y axis, the better it is to hold the stock, so you really want a stock that has the lowest possible risk and the highest possible return, so you want a stock that is as close to this particular area as possible, So let’s take a look at this particular stock here. I would like to format. You know, add data labels here. So any point on this curve basically represents a certain fraction of AT&T and certain fraction of Best. Buy that you would hold in your portfolio of stocks. So now let’s take this return here of 0.5% which represents this one here, so this is pretty low return, and this also has some risk. Can you improve a little bit? Can you go to some other point on this line on this curve where you can improve your return and improve your risk? Yes, you can. And that is this point here, which has a lower risk and a higher return and this point is even better. It has an even lower risk, an even higher return, but this point is really the best of the of these four points. It has a lowest risk and the highest return now. If you go to these other points here as you go further and further towards this, your return is improving, but your risk is also increasing. So if you hold this portfolio here, basically, you’re taking, you’re getting a lot of return, potentially, but you’re also taking a lot of risk a lot of variability, so the efficient frontier here really is the set of all points where you could pick any one of these points and depending on your risk and return trade-off now any point below this should not be picked because you can actually improve upon it by picking some point on this curve here, so for example. If you wanted to choose this point, you could choose this other point here, which has the exact same riskiness, but has much higher returns the region above this line here. This curve here represents the efficient frontier. So that’s it for now. I hope you liked it. Thank you for watching.