Portfolio Optimization Seven Security Example with Excel Solver

[@jimgrant1776]

Matt, fine video. Excellent explanation of the models. In regard to the 7 security model, the solution is near-obvious. That is, maximize the allocation to the securities with the highest returns and minimize the allocations to the securities with low Sharpe Ratios, subject to the mins and maxs pre-established by the user.

[@MattMacarty]

Yes that's right. You could set constraints in Solver to make sure some minimum and/or maximum allocation is met. I more or less just wanted to show how to use portfolio theory with more than 2 or 3 securities.

[@Leo-ld3do]

Never found something that helpful like your video!!

[@MattMacarty]

Thanks. Glad it helped.

[@TempterPS]

You are a God of Excel. ThNk you

[@MattMacarty]

Glad it helped

[@MaxArkey]

A great refresher, thanks Matt!

[@MattMacarty]

Glad it helped

[@BimanSMondal]

Great video. Neat use of array functions. It will probably go over the head of excel beginners.

[@MattMacarty]

Thanks for the feedback.

[@heeteeshadevibaulah9117]

Interesting video really helped me a lot, but I did not understand the volatility formula used for the portfolio. what is the purpose of using indirect formula?

[@MattMacarty]

The indirect function returns a cell reference rather than a value in the cell. I used it in conjunction with named ranges to facilitate calculating the variances. If I hadn't I have have ended writing the same formula multiple times.

[@williamrivera162]

Very instructive videos, thank you so much

[@MattMacarty]

Thank you. Glad it helped

[@mickydisney2084]

very clear explanation, appreciated!

[@MattMacarty]

Glad it helped

[@cedricnkuna9561]

Very great video and everything is explained so well.

[@MattMacarty]

Thank you. Glad it helped

[@LifelongStudentBelgium]

Thanks for the video Matt!! You've got a new subscriber. Only noticed a small mistake in minute 12:47 but no big problem. Took the transpose of the GILD instead of KPTI

[@MattMacarty]

Glad it helped. Thanks for catching.

[@nikhilchowdhary8919]

too good, keep posting , we will make you famous. haha Keep up the unique content

[@MattMacarty]

Thank you. Glad it helped.

[@way2worldoffinance436]

One small issue, Covariance-Variance Matrix calculated is based on population, you should convert it to Sample Covariance-Variance Matrix by multiplying by N/N-1

[@MattMacarty]

Yes can correct for sample bias, but correction is quite small.

[@paoloalbero2017]

Hello Matt, thanks for the great video! I have 2 questions: 1) you multiply by 252 to convert from daily to annual. If the dataset is for instance covering 2 years, should you multiply by 252*2, or is it always 252? 2) when calculating the return of the portfolio, should we use the average return for each security, or is it better to use the CAGR for each security over the time span represented in the dataset? Thanks!!!

[@MattMacarty]

You could do this but then you will have to do further adjustments for the std dev. It's probably easier to just use 1-year of daily observations.

[@rkjessop]

Excellent presentation! Specific references that show formula development would be useful. Thanks!

[@MattMacarty]

Thanks. Sorry about that. Pretty much any college-level finance text will have something accessible. A lot of portfolio theory can be attributed to work by Markowitz.

[@bethanmunden-langley4241]

sorry quick question Matt this video was so helpful how do you calculate the Sharpe value thank you

[@MattMacarty]

Traditional Sharpe is (E(r) - rfr) /volatility. I left off rfr since it was and is still close to 0.

[@nhitruong9019]

Hi Matt. Thank you for your video. It's very helpful for my exercise. However, I have a question for you: how can you calculate the yearly Expected return for each stock from the daily historical data. If I take the average of rate of return and multiply it by 252 is this formula correctly?

[@MattMacarty]

Yes you can do it that way. This will give you total return over the reference period provided you use LN (instantaneous return). This assumes you will have the same return in future though. Any expected return is highly subjective.

[@majinnhec]

Thank you - that was great and really well explained!

[@MattMacarty]

Glad it helped.

[@cindyetame3857]

Hey great video , please how can I make that all my stock are used In the solver ?

[@MattMacarty]

You mean you want to make sure there is some minimum investment in each security? You can add a constraint to make investment % >= some minimum.

[@gtrdotone3735]

@ 11:27 should the square of cell I5 equal to cell D5 since SPY to SPY is variance of SPY? Square of .3204=.1017 which is not what is shown in cell D5!!!

[@MattMacarty]

These are daily values and need to get adjusted to annualize ( x 252)

[@kazimrajani44]

Hi Matt, Thank you for the above video, could you please shed some light on the following scenario, i have collected daily prices for 10 assets for last 3 months and doing the portfolio optimzation. I am a bit confused on why how to convert the returns as monthly and annual returns? also, why have you used instantaneous rate in the above video? Thanks

[@MattMacarty]

You have to be careful extrapolating returns. I would just use more data. You can use the percent return and get to the same ending place, but the convention is to use continuously compounding returns.

[@danieldavis6034]

Hey Matt, how would you calculate the volatility? I'm putting together a spreadsheet for a case study to find the best weighted portfolio for Us Equity, Govt bonds, HY bonds, Int Equity, Reits and Commodities. So not individual stocks but a combination of all the above.

[@MattMacarty]

See this video: kzbin.info/www/bejne/a6jVlqaafdSifrM. You will also need data to calculate volatility for the other securities. Preferably this is daily data so that you can get a reasonable estimate of covariance.

[@danieldavis6034]

@@MattMacarty Awesome thanks Matt. I was doing the volatility of the whole portfolio instead of each individual one so that link was very insightful

[@saulhayim3065]

Hey Matt, Unreal video. I am doing a finance assignment and this video has been of great use so thank you very much. Just one question however. Are you referring to volatility as standard deviation or variance. At 3:44 you referred to it as variance so i just wanted to make sure as my observed volatility data is considerably less than yours. Thanks heaps

[@MattMacarty]

Volatility is the standard deviation, but I may have inadvertently said variance at some point. We need the variance to get at the standard deviation of the portfolio

[@Bostonjd]

I thought Sharpe Ratio was EXCESS return (return above the risk free rate) divided by standard deviation. Have your returns above already subtracted the risk free from the return?

[@MattMacarty]

Yes. It was 0 at the time I made this Here I am just using it to find the best risk to reward proportion. Even if you include rfr as it currently stands, within the domain of this model the optimal portfolio won't change unless the secruities volatilities change.

[@jc.nogueira]

Thank you for the video! Very interesting. May I ask a question? In the second part (when you optimize the portfolio for seven assets), you calculate the Sharpe Ratio = Return / Volatility. Usually the Sharpe ratio formulas is: Sharpe = (Return - freeRate) / Volatility My question is: Why don't you subtract T-bill rate from the Return? Was it was already subtracted in the returns for each asset? Many thanks again. Best regards, jc

[@MattMacarty]

Thanks. So, yes I should have included rfr, however when I made the video it was 0 so I ignored it. Also I am not using the ratio traditionally, but instead as a quantity to be maximized. Including a non-zero rfr will not change the optimization result.

[@jc.nogueira]

@@MattMacarty Many thanks Matt! Best regards from Uruguay

[@edwardchau5818]

Why are the numbers on the datasheet so small? SPY for example has a NAV of >300 (as of the date this video was posted). How come you didn't use CAPM model to calculate expected returns? Thanks Matt

[@MattMacarty]

The values here are daily price change, not price. You can use CAPM if you like or any other number. Any value used is just a guess.

[@edwardchau5818]

Would it be OK to use daily prices? I was also wondering about the time horizon. Is it necessary for the time horizon of the expected return and SD to be calculated over the same period (e.g. daily)? What if my CAPM model for expected return is calculated by taking the average return of the S&P over 5 years, but the volatility is daily?@@MattMacarty

[@MattMacarty]

@@edwardchau5818 You can change the time horizon, but yes the expected return and sd time should mathc

[@francescopanico8672]

Thank you Matt for this video and tool! One question: how do they usually calculate the return values? Statistical analysis?

[@MattMacarty]

It depends on the application. If it's daily then I would use an "instantaneous" rate of return. If it's an annual expectation then just a simple expected growth rate.

[@MattMacarty]

Thanks Do you mean the expected return or the a actual price change?

[@DL-vg9np]

Thank you for the informative video, anyway just to check, shouldn't the Sharpe Ratio include the risk free rate in the numerator? Or is the return here already the excess return?

[@MattMacarty]

Thanks. Yes, rfr was about 0.13% for the one year at the time if this video. I think the bigger concern is how reliable is a prospective expected return.

[@SFW7]

Thank you so much!!

[@MattMacarty]

Glad it helped

[@StockSpotlightPodcast]

Fantastic! Can you use the correlation matrix instead of the covariance matrix? I had already done this, so just want to make sure that I don't need to go back and use the covariance matrix instead of the correlation matrix.

[@MattMacarty]

Yes you can alternatively use correlation

[@suthadasrisung3639]

Thank you for this teaching video. Would you mind to explain about how you build CML with efficient frontier curve ?

[@MattMacarty]

That is a scatter plot of risk vs return at the various portfolio weights.

[@wctyna]

Great vid, it is very helpful. I want to try it out but there's no link to download the spreadsheet...

[@MattMacarty]

Don't know how I missed that. Here you go: alphabench.com/data/excel-portfolio-optimization.html

[@ahana1600]

Thank you for this! How did you get the existing volatility of the security (highlighted in orange)? Trying to find the figure for ICLN (iShares Global Clean Energy ETF)

[@MattMacarty]

Glad it helped. Volatility was calculated from historical data. You can download the file I used and see how I did it here: alphabench.com/data/excel-portfolio-variance.html

[@timpikaaungnoi8316]

your video such a good ,but i have a question about Expected return How do they calculate?

[@MattMacarty]

Expected return is a forecast value and fairly subjective. So you might look at collective price targets from the various analysts and determine expected return from there.

[@FaizanAhmad-eo3oe]

Hi Matt. Thanks a lot for such amazing video. There are some minor issues that I noticed. First one is that while taking transpose to fill up covariance table, the values of KPTI to GILD and MPC are not correctly transposed which slightly change our outputs. Second is that to calculate sharpe ratio, don't we have to subtract risk free rate from our returns and then divide it by the volatility? Thanks again.

[@MattMacarty]

Hi, thanks. You can download the file used here: alphabench.com/data/excel-portfolio-variance.html I took a quick look and it look correct there. I am ignoring rfr since it is so close to zero , and when comparing various portfolios since rfr is constant different portfolio sharpe ratios may look negligibly higher but they are all higher by the same almost zero rfr. You only run into problems of bias when comparing sharpe ratios from some outside source. In that case our ratios will appear relatively higher. Probably should have explained this in the video, but I am only using "sharpe" as a shortcut to optimize security mix as opposed to how it is typically used to compare risk/return profiles

[@FaizanAhmad-eo3oe]

@@MattMacarty thanks a lot Matt. Cheers

[@andycheung7739]

Great Video! Thanks!

[@MattMacarty]

Glad it helped

[@jiajunxiong7399]

Thank you for sharing the video, btw why do you need the instantaneous rate of return for each day?

[@MattMacarty]

Thanks. It's used to calculate volatility.

[@quynhtram2920]

1:48 Is that ok if I replace number of trading days in a year(252) with number of trading months in a year(12)? Is there any difference?

[@MattMacarty]

So the 252 is based on trading days a in a year, which is how much data I have giving us a annual volatility when you take the square root of the variance. If you want a monthly volatility you can divide the annual by the sqrt(12)

[@krishnaiyer2556]

how did you draw the tangent line from 2% to efficient frontier? request pls explain, or else excellent video

[@MattMacarty]

Hi, it's probably easiest to download the spreadsheet I used here: alphabench.com/data/excel-portfolio-optimization.html

[@amedeoserva1080]

Hy Matt, very instructive video! One question: how do you calculate the cell portfolio expected return? thanks in advance!

[@MattMacarty]

Expected return is really a subjective value since we don't really know what it will be. You can get estimates from a number of financial websites for this or you can enter what you think it will be.

[@ColorfulLifee]

@@MattMacarty i did not get that. plz explain more

[@lukaborna]

Hi, great explanation. Is there any way to graphically show the second model?

[@MattMacarty]

Thanks. Yes it can be done, but it is quite complex. The result is essentially the same as shown with two securities.

[@myroutinework6444]

Hello Matt, this is an amazing video I have come across so far in this field. I've been struggling to really understand how did you calculate the Volatility in the second method. I tried all your three method and failed any help will be genuinely appreciated as it would save me a lot of time.

[@MattMacarty]

Thanks. Have you tried downloading the file I used: alphabench.com/data/excel-portfolio-variance.html

[@myroutinework6444]

@@MattMacarty Yes I did, but when I tried applying the formula by myself it didn't work!

[@jasonjett7385]

Great instruction Matt. Love your excel demonstrations. On the mmult array calculation. If I'm using 5 years of monthly returns to calculate the volatility on portfolio, how would I modify the mmult calc to account for the 60 periods and get back to an annualized volatility? Thank you.

[@MattMacarty]

Thanks. I discuss just the variance here: kzbin.info/www/bejne/a6jVlqaafdSifrM. Essentially for monthly data, calculate variance and then multiply by 12 or sqrt of 12 if working with st dev. It's easier if you just work with the variance though since evenutally you will want a covariance matrix. However, it is possible to calculate portfolio volatility with a correlation matrix as well if you absolutely have to start with st dev.

[@vickyst8000]

Thank you Matt for this useful video and I do have a question want to ask, why the shape ratio here did not minus the risk-free rate?

[@MattMacarty]

Yes it should be less risk free rate, but it won't change the allocation. I probably should have said "modified" Sharpe ratio. In any case it's pretty close to zero at the moment.

[@vickyst8000]

@@MattMacarty ok, thank you Matt!

How do add constraints for the short sales? For example, if you don't want total amount of shorts to be not more than 20% of the portfolio

[@MattMacarty]

Short sales are implicitly included in the table for two securities. If you want to allow shorts for the more diversified case you can allow negative investing and add a column and constraint that the negative amount not exceed 20% using the sum function or I guess you can also make sure the total percentages sum to at least 80%.

@@MattMacarty I have 10 assets. The sum of all assets in excel can only be constrained to to their individual range but not aggregate constraint.

[@idemenegauthier8623]

you did even show how you set up the chart. the graph just drew itself how are we supposed to set it up?

[@MattMacarty]

Hi. You can download the spreadsheet from the link in the description. The chart is just a scatter plot though

[@meredithgrey8081]

Thanks ! I just have one question, after using Excel Solver, what would be the explanation as to why we get 0 as the weights for some stocks?

[@MattMacarty]

The solver will seek to maximize Sharpe, so it will preferentially choose stocks with higher returns relative to volatility. So if you have two stocks with the same return but one has higher volatility, solver will put everything in the lower volatility stock. You can set a constraint that investment must be >= some threshold if you want all stocks represented to some degree.

[@juliapeppard9221]

Hi! Quick question - how come the covariance between two of the same stocks is not 1? For example, SPY and SPY in cell I5. Thanks!

[@MattMacarty]

Correlation will be 1, covariance is not measuring the same thing. Basically, covariance of the stock with itself is its variance.

[@ana_8696]

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?

[@MattMacarty]

Hi, you can download the spreadsheet I used for this model and probably adapt it to your situation: alphabench.com/data/excel-portfolio-optimization.html

[@huanchungkhoan]

Hi Matt, super helpful video. I wonder how this seven security porfolio would be optimized given you invest with T-bond? Like what you did with two securities how would you expand to 7?

[@MattMacarty]

If you can find the bond volatility data, you can incorporate the same way I did with equities.

[@noelisxarez2887]

is there a way to calculate the “T-bill” if it is not given to you?

[@MattMacarty]

Sort of, but it is dependent on assumptions and too complicated to cover in a comment. I don't have a video covering this, but I am sure there are some out there.

[@faisalhossain5958]

Can anyone tell me if I want to find some stock derivatives data suppose I want to find derivatives of Amazon which I want to add in my portfolio how to find the data and where to find it.

[@MattMacarty]

Try EODHistoricalData. They have option data and I think you can get it free with a the free acount

[@Leo-ld3do]

I downloaded your file but im still struggling how to calculate volatility in the 7 security example. And also im struggling how to create EFL now using the last example? Best Leo

[@MattMacarty]

Hi yes it's not all that straight forward to graph the efficient frontier with more than 2 securities.

[@bg-fl5kr]

I'm trying add in a condition whereby only x out of 7 funds can be invested in. How can we do this? I tried but solver doesn't like it!

[@MattMacarty]

You could try adding a 0,1 column to indicate investment, and then require the sum to be

[@chopper081]

Hi Matt, love your videos. I was wondering if it is possible to do a variance-covariance matrix and efficient frontier on a long-short portfolio? I'm trying to do one with 5 longs and 5 shorts? any advice would be greatly appreciated

[@MattMacarty]

Sure you can. The only difference is going to be the weights for some securities will be negative.

[@chopper081]

@@MattMacarty, To be honest, it's my first time doing this so I'm not 100% sure of the formula etc, would it be possible to get your opinion on it via email and id pay you for your time?

[@chopper081]

@@MattMacarty also, if the weights are in the portfolio have to add up to 1.00, if I'm going negative on some on 5 of them, won't the sum be incorrect?

[@prasetio2461]

Thanks Matt nice video, btw can u explain to me on excel formula data capital market in return same as formula CML ?

[@MattMacarty]

So what we are saying is for this portfolio, the curve represents the efficient frontier and the point where the line intersects the curve you have the best allocation of capital in terms of risk vs reward. Of course the expected return is a forecast and not necessarily what will happen.

[@prasetio2461]

@@MattMacarty why dont u choose return market as the basis of reference for capital line ? but u choose return from optimal combination as the basis of reference

[@mikeylejan8849]

I need to find the optimal ETFs listings in the New York Stock Exchange, what should I do to do this? I have 1250, ETF listings

[@MattMacarty]

I am not sure what you mean. You mean you want an optimal mix of several ETF's ?

[@mikeylejan8849]

@@MattMacarty finding the best portfolio of my ETF listings, I need to use cumulative abnormal returns.

[@kulknira1]

How do I calculate "instantaneous" rate of return? Sorry, I am kind of novice and when I look up yahoo finance, I do not see that historical data anywhere. When I google search I did not get any help. Hoping to learn from you.

[@MattMacarty]

Yes, this is calculated in Excel with the LN function: LN(close today/close yesterday)

[@aparnabhat2548]

Hi, what is the decision variable in this? expected return and volatility of each etf.

[@MattMacarty]

So it's not the typical optimization problem you see for say management science, but the weights are the decision variables. We are including both volatility and expected return in the maximization (objective function) via the sharp ratio.

[@randomreviews5693]

I would have appreciated you more had you shown the formulae in cell C13, you deliberately did not show that

[@MattMacarty]

Since it's a random review I am not sure if I am supposed to respond, but you can download the spreadsheet from a link in the description, and the formlua used there is weighted average with the SUMPRODUCT function.

[@randomreviews5693]

@@MattMacarty thanks for replying but I figured this out ...and I am sorry for questioning your intention. its just that one generally dosent find such good stuff so easily...ppl don't share these ..... thanks for your effort

[@randomreviews5693]

@@MattMacarty I am really sorry... felt really bad

[@sayednab]

Have you done multiple portfolio optimization?

[@MattMacarty]

No, sorry

[@sayednab]

@@MattMacarty its ok.thx

[@erhan4123]

If $10,000 was invested on January 1 2020 with this strategy, it would be worth $16,546 as of December 31, 2024, representing a cumulative return of 65.46%. Over the same period, the SPDR S&P 500 ETF Trust would be worth $19,653, representing a cumulative return of 96.53%.

[@MattMacarty]

Sure, but a couple things to keep in mind. 1. The purpose of this video is to show you how to optimize; 2. Having an optimally allocated portfolio based on past metrics won't guarantee some market beating return; 3. Finance professors have been telling us for years "you can't beat the market"

[@erhan4123]

@@MattMacarty It's just fun to watch from KZbin after 5 years with all the data available. Thanks for the video, btw.