Portfolio Optimization using five stocks in excel | FIN-ED
Вставка
- Опубліковано 7 лют 2025
- #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...
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
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!!!
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.
I think I love you, I can finally write my thesis
Thank you so much, this is my first ever project!
Thank you very helpful
This is most easy understanding teaching video...tq a lot
Thank you for your video. This is by far one of the most simple design layout that allows for east scaling. But i have a question: Why did you decide to use a 10yr risk free rate with 5 years of stock data? Would a 5yr risk free rate have been wrong? or maybe seven years? Thanks for your reply/
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?
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%
@@ianthompson31 there are many other YT videos out there on how to plot the Efficient Frontier graph.
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.
thanks for the explanation and the music !
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?
Thank You for the video! is it possible to make a graphic representation of the efficient portfolio in excel?
yes, this was the promise from the profile image, yet that point wasn't in the content...
Fantastic video, thankyou so much!
why do you multiply monthly variance by 12 to get the annual one? even if we assume we can just multiply by 12 to get annual (sufficient): Var(year) = Var(12 * Month) = 144 Var(Month) from prob theory
Thank you. This video is very helpful!
Thanks, this is an excellent tutorial. Mind if I ask you, if this is still used in optimal portfolio management?
Thank you very very much~
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
bro your profile picture just got me tricked -_-
superb....thank you
Add to your winners with a limited amount of money, for example, $5,000. If they are at an all-time high after 5 years, add to them again with another limited amount of money.
Sell the losers after six months.
Continue buying as many winners with excellent fundamentals as possible. Repeat the above processes.
This way, you'll acquire as many stocks with a competitive advantage as you can.
Great video! Is the file available for download?
Why do we not use Adj Closing?
Adjusted closing prices are adjusted for splits and dividends. So, you are free to use any of those. Just be mindful when explaining your analysis outcomes.
Fantastic. How would you integrate dividend yield + compound interest from that into this optimization problem in terms of total/expected returns?
Adjusted close takes into account dividends received and any stock splits
@@michaelmorris5758 Agree other YT videos by others use the ADJ close price instead of the Close prices
Would daily prices work? Thank you
daily prices should work. But, pay attention to how you annualize daily return and risk. it's also good to use an adequate time range such as one year or more.
@@FIN-Ed Thank you!
So it means that you only invest in 2 stocks?
thank you very much.
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.
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.
Why do you annualize by multipliying monthly return by 12 instead of using the compounf interest formula? Annual return=((1+monthly rate)^12)-1
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.
If I caculate Average daily return, can I calculate Annual Return by Multilying by 365
conventional wisdom is to multiply by 252 because there are about 252 stock trading days in a year.
How can i find risk free rate from the internet...its showing 7.1 today so i should mention same number ?
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
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 ?
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.
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.
Thank you
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
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).
@@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
why use excess returns calculation instead of just using the returns calculation table?
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
thanks for your great work kindly when divide by 58 for N-1 from where 58 comes can you explan please
Because there are 59 rows but he is doing n-1 so it’s 58.
THANKS
Hi, can you please help??
If i use the solver, nothing happens.
why do you divide by 58 for calculating the variance matrix???
Because there are 58 lines in the Excess returns list
number of observations
the total number of rows is 59. You lost one row in calculating the return series.
Add to your winners for a limited amount, sell the losers after six months. Buy as many stocks with a competitive advantage as you can.
Why does your variance-covariance matrix output differ from excel data analysis software output???
Why is the data for Pfizer diferent from your search on the video to today?
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.
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?
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?
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.
@@FIN-Ed Thank you for your answer. I will try it out.
@@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
how can i calculate portfolio variance if I already have each asset's expected standard deviation and the portfolio is leveraged up over 100%?
hello I just wanna ask how did you get the risk free rate?
use the 10 year treasury
following this guide using another datasets it's not equal
See the first comment where I explained why it's not equal and how to fix it. thanks.
where is the graph??
The chart can be drawn manually if the optimization is done using two stocks. However, drawing a chart for 5 stocks optimization is really difficult.
@@FIN-Ed but thats what you showed in the thumbnail?? and no, its not difficult!
very nice video nice explanation do u have any contact number i need help in assignment which is on same topic please reply thanks
The annual variance is wrong, it should be the sqrt of 12, times de variance. That is the correct formula
wrong, wrong TOTALLY WRONG... His calculation is correct. You multiply sqrt of 12 times STANDARD DEVIATION to annualize standard deviation, NOT VARIANCE...