Portfolio Optimization using five stocks in excel

No video

Portfolio Optimization using five stocks in excel | FIN-ED

Рет қаралды 72,124

Күн бұрын

#fin-ed
Portfolio Optimization using five stocks in excel | Calculating the Variance Covariance Matrix using stock Prices | FIN-ED
In this video, using five stocks, I will show how to find out the optimal portfolio, which will maximize the return and minimize the risk. We are going to use Microsoft Excel, excel solver, and matrix multiplication to compute the variance and covariance matrix. If you are interested in only 2 stocks portfolio optimization, please check out my other video, the link of which is also given in the description below.
In this demonstration, we are going to use 5 years of monthly stock price data for 5 companies such as MCD, SBUX, PFE, AMGN, and AXP. If you understand the process, you can do the same exercise using whatever many companies you like and whatever date range you want to use in your computation.
Portfolio optimization using 2 stocks: • Markowitz Portfolio Op...

Пікірлер: 72

@gulcinlarsen1328 2 жыл бұрын

After seeing many videos i just can say that this is one of the best videos, which there are actually very few of them. THANKS

@susan88888888able 2 жыл бұрын

thank you so much!!! This was so simple and no one could explain this except for show the first chart and not how to actually do it. I actually paid a tutor service and got the same nonsense answer. Thank you!!!

@FortuneCookieLies 7 ай бұрын

The value is the most stable path for returns. The two are the least correlated with each other. That is why. Analyzing the why behind asset correlations is key.

@janorhanheptek5024 2 жыл бұрын

I think I love you, I can finally write my thesis

@faridfouad9638 Жыл бұрын

Thank you so much, this is my first ever project!

@louis5313 3 жыл бұрын

This is most easy understanding teaching video...tq a lot

@profarvind 2 ай бұрын

Thanks, this is an excellent tutorial. Mind if I ask you, if this is still used in optimal portfolio management?

@liampayne-ritchie8506 3 жыл бұрын

Fantastic video, thankyou so much!

@sjsphotog 2 ай бұрын

Great job. You did it a little different than the others I saw about this topic in that you used the Overage Returns instead of just Raw return rate. Why is that?

@dominodelivers 2 жыл бұрын

I was hoping this video would go into detail on how to actually graph the efficient frontier curve/in particular the non-efficient portfolios as well. Do you have an video on graphing the curve? Or can anyone recommend a suitable one?

@ianthompson31 Жыл бұрын

all you have to do is use portfolio weights from 1-100 such that the assets add up to 100 and plot the return and standard deviation for each of those portfolios. If you want to include short selling include form -100% to 200%

@sjsphotog 2 ай бұрын

@@ianthompson31 there are many other YT videos out there on how to plot the Efficient Frontier graph.

@danieledeleo1677 3 жыл бұрын

Thank You for the video! is it possible to make a graphic representation of the efficient portfolio in excel?

@samirelzein1978 3 жыл бұрын

yes, this was the promise from the profile image, yet that point wasn't in the content...

@FIN-Ed 2 жыл бұрын

In my video, the diagonal elements of the variance-covariance matrix are not equal to the variances by a few points after the decimal. This is wrong in the sense that we must have these numbers exactly equal. If you have the same issue, it's because the excess return matrix has the wrong formula. Specifically, cell U62 is referring to an empty cell H63. It happened when we copied the formula down Just fix the formula manually (e.g., maybe delete the last row (U62 to Y62) of excess return), your problem will be solved.

@untitledchannel234 3 жыл бұрын

Thank you. This video is very helpful!

@amazingjuan001 3 жыл бұрын

Why do you annualize by multipliying monthly return by 12 instead of using the compounf interest formula? Annual return=((1+monthly rate)^12)-1

@FIN-Ed 3 жыл бұрын

I just wanted to simplify the annual return calculation. If you want, you can use compound return formula as well. It's your choice. In fact, compound return, more often than not, is better than simple annual return.

@thuytientran6248 9 ай бұрын

Thank you very very much~

@YazminAbat 2 жыл бұрын

thanks for the explanation and the music !

@virat82 3 жыл бұрын

superb....thank you

@thereishaironyourscreen2991 Жыл бұрын

Hey great video. But I need to use all the stocks. In this example it only invests into 2 of your stocks. How would you add constraints to ensure use of all stocks. Even at the expense of maximisation

@IamThe_Reaper 9 ай бұрын

bro your profile picture just got me tricked -_-

@Bob3D2000 Жыл бұрын

Thanks. What do you do if one (or more) business was started during the period of your data? This would mean one or more of the data columns has missing values down to a certain row. How do you deal with this in the variance covariance matrix in particular? In this scenario, this business would have the same value (0 - average return) for many rows in the Excess Returns section. This means this business has a very low variance, so the solver will put a big weight on this business, which is not correct.

@FIN-Ed Жыл бұрын

Sorry, in that case, you may need to drop that company from your analysis. Alternatively, you can trim the observations (for all companies) to delete missed values and equal number of observations for all firms. Least favorite, you can use as short as one month daily data in your analysis, but the results may be biased.

@6toolbaseball 3 жыл бұрын

Fantastic. How would you integrate dividend yield + compound interest from that into this optimization problem in terms of total/expected returns?

@michaelmorris5758 3 жыл бұрын

Adjusted close takes into account dividends received and any stock splits

@sjsphotog 2 ай бұрын

@@michaelmorris5758 Agree other YT videos by others use the ADJ close price instead of the Close prices

@monikarompoti5594 2 жыл бұрын

Hello, and thank you for your video ! In the variance covariance matrix, why do you divide by 58 (you say it is N-1, but you have 60 months). So, shouldn't it be divided by 59 ?

@FIN-Ed 2 жыл бұрын

Last row of excess return should be deleted as it's refereing to a blank cells. I am sorry that my video doesn't fix it, but I made a comment on how to fix it. Basically, when you calculate returns, you loose one row. In short, when you delete the last row of excess return, the total number of row is 59. Therefore, N-1 is 58. I hope it helps.

@SuperSergiobueno 4 ай бұрын

The annual variance is wrong, it should be the sqrt of 12, times de variance. That is the correct formula

@joeyiskandar8171 2 жыл бұрын

So it means that you only invest in 2 stocks?

@jawadakbar_ 2 жыл бұрын

Thank you

@areebabutt8624 5 ай бұрын

Sir if we want to make efficient frontier which values we will use?

@FIN-Ed 5 ай бұрын

I am going to make a video about creating efficient frontier soon. thanks!

@hughvahia866 3 жыл бұрын

If there is a zero value in the data, how do you calculate the average return formula in excel cos when i run it, it gives an error.

@andrewrender Жыл бұрын

why use excess returns calculation instead of just using the returns calculation table?

@andycheung4417 Жыл бұрын

because the formula for covariance is SIGMA[X-E(X)][Y-E(Y)]/n, so you need the excess return of x and y to be multiplied together

@adviteeyashrav1357 2 жыл бұрын

THANKS

@ana_8696 3 жыл бұрын

Great video!! I have one question: Great video!! I have 12 assets and their prices of 29 different weeks. I want to do portfolio optimization by minimizing the Mean Absolute Deviation. I have calculated r, E[r] , E[r-E[r]] and |E[r-E[r]]| using Excel . What do I have to do next?

@Daniel-ck8ii 2 жыл бұрын

Hi, can you please help?? If i use the solver, nothing happens.

@hakankosebas2085 Жыл бұрын

why don't you use adjusted close price?? also I don't quite understand why you did it montly, some people on youtube makes it daily

@FIN-Ed Жыл бұрын

yes, using adjusted close price is better. The result will not change much unless there is a stock split. you can also use daily price. Daily price is subject to much more fluctuation than monthly price. There is no set rule as to what to use. it's always about the preference and logic of analyst (i. e., you as analyst).

@hakankosebas2085 Жыл бұрын

@@FIN-Ed unfortunatly I am not quite analyst, and I am strugling to figure out which number should I multiply to make things annualized, they all multiply by 252 which is american stocks trade days in a year and we have holidays that are changing in every year

@khoapham7029 9 ай бұрын

If I caculate Average daily return, can I calculate Annual Return by Multilying by 365

@FIN-Ed 9 ай бұрын

conventional wisdom is to multiply by 252 because there are about 252 stock trading days in a year.

@Mangekyou1100 2 жыл бұрын

Why does your variance-covariance matrix output differ from excel data analysis software output???

@darisaram2610 Жыл бұрын

How can i find risk free rate from the internet...its showing 7.1 today so i should mention same number ?

@FIN-Ed Жыл бұрын

If you use 3 month treasury as a risk free rate, you can visit: ycharts.com/indicators/3_month_t_bill Those who want to use 10 years treasury rate as risk free rate can visit: ycharts.com/indicators/10_year_treasury_rate

@na10pa 3 жыл бұрын

why do you divide by 58 for calculating the variance matrix???

@AgeOfWulinTV 3 жыл бұрын

Because there are 58 lines in the Excess returns list

@michaelmorris5758 3 жыл бұрын

number of observations

@FIN-Ed 2 жыл бұрын

the total number of rows is 59. You lost one row in calculating the return series.

@roberthuff3122 7 ай бұрын

Great video! Is the file available for download?

@naderalotaibi7575 2 жыл бұрын

thanks for your great work kindly when divide by 58 for N-1 from where 58 comes can you explan please

@ethandanish8142 2 жыл бұрын

Because there are 59 rows but he is doing n-1 so it’s 58.

@jonathancreative5779 3 жыл бұрын

how can i calculate portfolio variance if I already have each asset's expected standard deviation and the portfolio is leveraged up over 100%?

@OscarNavarroLeano Жыл бұрын

Why is the data for Pfizer diferent from your search on the video to today?

@FIN-Ed Жыл бұрын

maybe because of a price adjustment in yahoo's system that happened after I made the video. Even after the price adjustment, it should be significantly different.

@AstroidegitaTech 2 ай бұрын

following this guide using another datasets it's not equal

@FIN-Ed 2 ай бұрын

See the first comment where I explained why it's not equal and how to fix it. thanks.

@bodyspirit 3 жыл бұрын

Hello and thank you for the video. I have a question. I would like to optimise my portfolio every month. What period for the historical data would you recommend?

@FIN-Ed 3 жыл бұрын

optimizing portfolio every month could be considered too frequent to many people. Although there is no transaction costs to rebalance a portfolio now a days, there could be some tax consequences (e.g., short term capital gain tax or wash sale issues). It all depends on investor's style of management. As for your question, although there is no one answer to this, you can try past 30 days data. thanks.

@bodyspirit 3 жыл бұрын

@@FIN-Ed Thank you for your answer. I will try it out.

@sjsphotog 2 ай бұрын

@@FIN-Ed if you plan to do a lot of Buy / Sell then do it in your ROTH or Rollover IRAs instead of your taxable brokerage accounts so that you dont incur short term capital gains taxes. The Brokerage acct should be used for LONG TERM holding stocks/etfs that you dont touch (sell) until much after 1 year if ever. I learned this is the best way to reduce taxes as the Taxable acct is only taxed at 15% if the sevurity is held for >1 yr whereas taxed at your standard rate (22-32%) if sold before 1 year. There is no penalty if you buy and sell all day every day in your non-brokerage IRA accounts (Traditional or Roth) so have at it in those. The taxes penalty really adds up over time