Optimal portfolios with Excel Solver

[@deadshr00m54]

My idiot of a professor expected me to know this off the bat with no training so thanks for actually giving me this information.

[@chicoez4669]

on god

[@shett5350]

Auke Je bent de best!!! dit oplossing is belangrijk voor mijn MBA thesis Portfolio analysis Kent Buss School!!! Up Holland!!

[@matthewvovk3545]

Yes, I found this useful. I've been wanting to do this type of analysis for quite some time. Thanks so much.

[@jean-pierrelobbes585]

You‘re the best!! Safed my project!

[@simfinso858]

Very Useful.Thanks sir for posting. Expecting more on this topic + Equity valuation + Analyst's must know excel shortcuts

[@arfrisco]

How do you use solver to get the weights at 2:45 seconds in the video?

[@chancesmith4326]

Thank you very much for this extremely informational video! It has helped so much with my Finance homework!

[@zudsjen]

Great video. This really helped me a lot. Thanks for uploading a quality video in HD so I can actually see what you're doing.

[@isabelcaldeira]

Thanks for the help, keep up the good work. Best of luck!

[@howardchen2101]

This is a good lesson, thanks for your video. hahhhh. it helps me a lot, I am working on my assignment using this method. Thanks again.Love it

[@MissPrealLili]

thank you for this video, it's clear and organized, and easy to follow.

[@JRicciFootball]

Wish my school had this guy as a professor

[@MrDavidleitch]

Thanks. This was a simple recipe even I could follow.

[@stephenhobbs948]

Very clear, well presented. Thank you!

[@sixiongcai5524]

So useful, thank you so much!

[@ljzhou329]

Helped me a lot for my class! thank you!

[@user-ow3be9te5o]

thanks for makin this video，really helpful

[@andrewreames3414]

Why do you hit shift, ctrl, enter for the portfolio expected return formula?

[@connorgioiafigliu]

Thank you! This saved me!

[@harrisondelfino3405]

MarketXLS works for me just fine for this. It's great.

[@ArjunGuptaNOW]

extremely useful. thanks for the tip. hope I would be able to make better portfolios :)

[@psychohx]

Help me out here. I used the same formula to try to get Er and var and st dev. I kept getting "#value" I don't know what's wrong .

[@brentjeannetta4110]

When you use an Array function i believe you need to hit Ctrl + Shift + Enter when entering the formula. If you just hit enter it will give you that value error.

[@johannhofer3961]

Saved my Ass, can you explain why you need to press this combination?

[@lapgandhi]

@@johannhofer3961 Just extend the cell to the right. There is less space for the numbers to line up.

[@bigjayfitness]

thank you very much Mr. Hollandaise sauce

[@aniruddhaghosh9823]

Revered Mr. Plantinga, thanks for sharing the video, however I have one question the variance comes to be 0.73% but how can the value of SD be higher than that of the variance i.e., 8.54% in this case. Please explain the same.

[@philipnelander9577]

+Aniruddha Ghosh This is because the variance (sigma-squared) is just the squared standard deviation. So to get from variance to std.dev. you take the square root :) As you take the square root of a number lower than 1 the result will be higher as the square root is the opposite of multiplying the number with itself :) In the example below taking the sqr root of s^2 will return it to s :) Example: s = 0.5 s^2 = 0.5 * 0.5 = 0.25

[@lapgandhi]

@@philipnelander9577 Right.

[@andrewreames3414]

How would the sharpe ratio calculation change if you were using daily return data?

[@Александра-о2ф4к]

Why is there a missing part from 2:42 to 2:44?

[@CylinderPaladin]

why not use sumproduct function for portfolio's expected return?

[@junxichen1668]

thx a lot, this really help me with my assignment

[@dannyjin9494]

Can someone tell me how I can solve a similar problem where I for example constraint the number of investment opportunities? Say that instead of being allowed to invest in all five at one time, I am only allowed to invest in four projects at one time. I know using an if function on the design variables are wrong - how can I go about this problem? Many thanks

[@RajVaswaniRox]

How practical is this model to be applied in real world as in these portfolio weights are based solely on expected returns which tends to differ from the company's fundamentals in the short run. So would you suggest this model to be theoretical or practical also please share your rationale. Thanks.

[@krystynabudna4099]

Hi there, I was wondering how did you calculate your alpha and beta and the covariance. Would it be possible to download this form you somewhere? Thank you

[@nikkinik020]

i'm trying to find the optimal weight for a portfolio of 4 assets (country indices) using the solver in Excel and maximizing the sharpe ratio. However, it always returns me the following percentages: 0,0,0,100%. Moreover, when I enter as constraint that the weights must all be different from each other, a message says that an optimal solution containing all constraints couldn't be found, and I get a solutin where the sum of the weights is above 1. How can I solve this?

[@Jas0n940]

How did you calculate the risk-free rate to be 1.5%?

[@tatien7929]

thanks for your lecture. can you help my problem? I want to apply LASSO into my portfolio. but I don't know how to make its algorithm on excel. I hope you can suggest or comment a positive direction to me

[@michaelliu6929]

Do you need to divide by n-1 when you calculated stdev, where n is the # of observations. In this case, n is 5.

[@freelancer9955]

) Ух, ты ! Стиль заметен. Прекрасно изложено. Но, в другой обсуждаемой модели таких нюансов совершенно и не нужно.

[@andrewreames3414]

Also, what is the formula for cell B17?

[@rainacho9554]

this is helpful. thanks!

[@VarsityFX]

Hello, I was wondering how would you go about creating the StDev for the portfolio if you are given three different covariances for three assets. I don't have a covariance matrix

[@MosesTheExplorer]

why do I get crazy numbers when I allow Short Selling (SS) ??

[@alejadroigoyanes]

how do you get the expected returns of each asset?

[@ahmed007Jaber]

How to do the calc for each variable?

[@lfbaraujo]

Auke, I'm needing to find the minimum variance portfolio with over to 70 assets, but I'm having problems with Solver. Is that possible to do this with such a big data? Thank you ( sorry for my english, I'm Brazilian )

[@cartoonomist]

Thanks buddy!!

[@paulmoreau5023]

Thanks ! very clear :)

[@bokkenknuser]

How did you calculate the Cov Matrix?

[@lapgandhi]

it is given

[@julesepstone8679]

Can someone please help me :( .. I have followed everything and the solver does not calculate it, but displays an error message: "Error value for solver in the target cell or a secondary control cell". How can I solve the problem?

[@xxdmoney3x]

Is this the same as optimizing a portfolio based on mean -variance?

[@emensonjean7424]

How do you calulate the covriance metrics?

[@himatilda]

How to calculate Rf?

[@jucaalco]

I did not understand either. Can somebody explain me? please

[@scoobymc7]

@@jucaalco risk free is dependent on the investor. Lots of people calculate risk free by taking the expected return of one month treasury bills over X years. An index like SPX or QQQ can be used as well.

[@scoobymc7]

To clarify I am talking about US treasury bills

[@adad-ec6ht]

The formula for variance of portfolio is x transpose * covariance matrix * x where x is the matrix of weights so that we get Weights squared. But you just multiplied the transpose of weight matrix (x) with the covariance matrix. That is not the formula. The formula is to further multiply with the weight matrix.

[@aukeplantinga]

What I do in the video is exactly what you suggest, calculate the portfolio variance as x' COV x

[@adad-ec6ht]

Oh, yeah, my bad. I did not notice. I was rushing to solve my homework.

[@fulca4389]

could've shown how to build the matrix RIGHT?

[@sajedaladadwah7988]

hello ; i wanna ask if u could help me in using the solver to find optimal portfolio when weights are unknown i am really want ???it in my assignment how to do that

[@xianxinzeng8488]

Did you figure it out? I got the same problem :(

[@michalisgiorgaki8860]

how did you calculate the s.d coloumn?

[@synergyuniversity2721]

Guten Tag! Is it possible to download your spreadsheet?

[@aukeplantinga]

Using the historical sample covariance.

[@尘世之际]

I’m lost... could someone tell me how to get the covariance matrix?

[@mcjgg-]

Does this apply to bonds as well?

[@vanesagrigoryan9355]

Thank you!

[@liliyah_aaa]

The first change by solver is max return, and the second one is mim risk? Pls someone else helps me!!!

[@vanderdossantos6676]

Can you upload this file here? It would very helpful

[@liuting5856]

why the covariance between A and A is not equal to 1?could anybody explain this? much thx¬

[@jordanfuent31]

LIU Ting diagonal numbers are variance, others are covariance, It's a variance-covariance matrix.

[@刘挺-u3i]

Much thx, I got the answer.

[@liuting5856]

jordanfuent31 thx bro~

[@oscarmhiko8431]

what is Resstd ?

[@shester8649]

thank you.

[@MrXianyang]

How do you get the RF 1.50% ?

[@ComicalChannelName]

That's a given rate. You can't solve for it

[@ruim8590]

great!

[@tailunzhang487]

should the cov of the same factor be 1?

[@johnnguyen3115]

thank you!

[@canigou333]

Merci beaucoup!

[@lapgandhi]

Tu comprends anglias? Bon!

[@clarajimenez9169]

Is the interest paid by the treasure or your country.

[@蔡高扬]

thx so much!!!!!

[@laraspratiwi2282]

can u make some videos about optimal portofolio using constant correlation model? or if u find some video about constant correlation model please tell me , reply my comment :"")) *sorry for bad english

[@torressung6411]

thank you for useful video, it shows no value# when I using the formula you teach, why was that could be,, I exactly insert the formula in the correct way..

[@DannyhEvolution]

How did he get RF =1.50% ??

[@christopherdedecko]

US 10 yr rate

[@janeqian1321]

why m i getting all 0 ?

[@abhishekdate4962]

Sir, all of us don't know German, so it is advisable to change the language of your excel to German. Secondly, you haven't mentioned from and how did you get the co variance in the beginning.

[@adad-ec6ht]

Where is your utility function?

[@aukeplantinga]

The example illustrates how to calculate the tangency portfolio. The compositioon of the tangency portfolio can be calculated without the need to specify a utility function. It is not difficult to adjust the spreadsheet to accomodate a utility function and maximize utility.

[@adad-ec6ht]

I am trying to solve my work. They gave the utility function as a function of expected return and variance. So should I choose optimum portfolio, add weights to a risk-free asset and compare the weight composition that has the highest utility?

[@aukeplantinga]

What is the utility function that they gave you?

[@adad-ec6ht]

Utility function is E(R) - variance ( I assume that of the portfolio). There are 2 stocks. Plus a risk-free asset. Asking me to get the optimum portfolio mix.

[@safaasalim1236]

please .I am need the file

[@TamyCL]

Good night Auke, I have to do an exercice. I have to define the value of the weights to minimize the risk of a portfolio. If you want to help me, please, give me you e-mail and I will send you the exercice. Thank you

[@tawlguy123]

I was lost 30 seconds in