Black-Litterman model explained (Excel)

[@NEDLeducation]

You can find the spreadsheets for this video and some additional materials here: drive.google.com/drive/folders/1sP40IW0p0w5IETCgo464uhDFfdyR6rh7 Please consider supporting NEDL on Patreon: www.patreon.com/NEDLeducation

[@yousseflouraoui4330]

Incredible video! This sophisticated allocation model is so technical but also very topical in the portfolio management industry. You bring a lot of value with this amazing content, thank you very much for your dedication to making such tremendous content!

[@MrMahankumar]

Hey man great!!!! This is one of the lesser known model (lesser as compared to Markowitz or Fama-French or Black-Scholes) but very few folks do it on Excel. Thanks for uploading this!!

[@Amanda-c2h]

Thank you so much, Sir. I watched a lot of your videos. You have saved my life.

[@luigibombelli7577]

Thanks a lot indeed...as always extremely clear and effective my friend

[@MauricioRamirezMorales-x8e]

Amazing video! Could you please tell me if Black - Litterman model can be used to optimize an ETF's portfolio? If it can be used, my next two questions would be... 1.- What index can be used as benchmark in order to get Beta value? 2.- How can market cap be calculated?

[@DarrenDias-w8d]

Hi I know u have explained alpha, lambda, mu etc. but could you explain this with reference to the actual BLM model (what tau = e varaible) but for g,h variable? or a paper where you source your information from? -your google drive doesnt have any info on BL Model just the excel model thanks

[@syedizharbukhari3952]

Thank.you..NEDL.

[@FreaKJumPaa]

Thanks for the great explanation. One small question with regard to the conclusion and application of ones own views: If a stock is undervalued, shouldn't a stock's expected return be adjusted downwards and vice versa as implied by the CAPM?

[@NEDLeducation]

Hi, and thanks for the excellent question! If the stock is undervalued, we would expect it to "make up" for that and at least partially adjust towards its fair value, hence enjoy a greater return in the near future. The reverse is true for overvalued stocks.

[@MohitDamani-t3y]

I have read some of the papers on black litterman but the notation used here (alpha, beta, gamma etc) I am not able to find anywhere. Can you direct me to a paper or link where I can read the theory logics of the formulas we have used in excel?

[@mountaindrew_]

Hello! Could you please explain why the Black-Litterman model uses lambda to do reverse optimization? Why can’t we just use the excel solver to find the market neutral expected returns from the given allocation that corresponds (in the Markowitz optimization) to the market capitalization of the different asset classes / assets?

[@Leo-nj3ep]

Nice one dude thanks a million

[@diogoblanco9487]

Thanks, NEDL! This was definetly the best video I could find on this subject on KZbin. I would like to ask you two questions. 1. Where can I find the article that refers to these vectors that you show (e, r, h and g vectors?) 2. I live in Brazil, and here the risk-free rate is around 13% (more than equity index return). In this case, is it ok to use negative market risck premium returns or is there any solution for this? Thanks again for your help!

[@NEDLeducation]

Hi Diogo, and many thanks for such kind words and insightful questions! The article that derives the matrix model with all the vectors for the frontier is Merton (1972) "An Analytic Derivation of the Efficient Portfolio Frontier". In terms of the risk-free rate and equity returns, the only condition that needs to be fulfilled in order for the model to work properly is that the risk-free rate is below the return of the minimum-variance portfolio. If this is true, the securities market line slopes upward, the risk premium is positive, and CAPM works. If this is false, I would suggest playing around with your expected return assumptions - perhaps the stocks performed much worse than expected in the last year/two years/five years and this is why your "expected" stock returns are much lower than the risk-free rate?

[@yujunliu8581]

If you cannot understand this video, please watch previous videos on the author's "Mathematical Finance" topic. I could not understand this video previously, but it becomes easy after I watched other videos.

[@yujunliu8581]

Thank you so much!

[@toshiro6589]

Hi @NEDL. What do you think about using partial covariance matrix instead just covariance matrix?

[@user-wr4yl7tx3w]

I didn't quite understand your point that dividend yield doesn't matter, if I heard correctly?

[@inesjaafar4633]

Thanks, NEDL! Really interesting and clear!! Thank you very much! Really useful. Do you think it is possible to add the investors views as a constraint in the excel solver? We therefore have the optimization function which minimizes the difference in weights by varying the expected returns and adding the investor views as a constraints?

[@tianlu2937]

Hi is there an article or basis i can further understand with regards to how you derived this understanding of the black litterman model? i.e how does this relate back to the black litterman equation?

[@vivinaabayakoon8546]

In the covariance matrix using the INDEX function could you please tell me why you multiplied it by 252? How did you come up with 252?

[@jakubn9244]

252 is generally accepted as number of trading days in a year

[@leviSe7en]

Hi, I have applied the model on the universe of 50 stocks and around 20 of them returns negative weights. The total is still 100% but can you please tell how to interpret this result? Also, would Black Litterman model work for other asset classes like Fixed Income/Commodities or multi asset portfolio ?

[@NEDLeducation]

Hi Shravan, and many thanks for the question! Glad to see you are applying the model to your own data. The negative weights is a very common shortcoming of these types of models, especially if the number of securities is large. The interpretation is that, at equilibrium, these assets need to be short-sold. To avoid this, you can allow expected returns as per Black-Litterman to be negative and see whether this changes the results. As for other asset classes, yes, the model should work for them as well.

[@leviSe7en]

@@NEDLeducation Thanks for the informative video and the swift reply. 1. You mentioned allowing negative Expected Returns in BL, did you mean the returns of the individual securities that we pass as inputs in the BL ? 2. In my case, the goal is create a model portfolio of 20 securities with optimal weights based on CAPM. Suppose I want to pass CAPM returns of 20 securities as inputs to the BL and it returns negative weights for 5 of the securities. So can I go ahead ignore the 5 securities? Now the total weight of 15 is 100+ so will scale them to 100 and create the portfolio with these new weights. Is this approach correct ? I'm sorry for large replies and ambiguity of these questions.

[@mohammedmagdy9835]

I have daily closing prices of a given stock and I want to transform it into monthly where the closing price for the month should be the closing price of its last day. I tried to make it in Excel using Pivot tables but they don't summarize data by Last value. I know Python and EViews can do this easily. Is there a way in Excel to do it?

[@NEDLeducation]

Hi Mohammed, and thanks for the question! Yes, if you have got dates, you could use a simple IF function to highlight ends of months and retrieve these to another sheet, for example. Alternatively, you can use a filter, but this would not be automatic, i.e., you will have to do it manually every time you update your dataset.

[@mohammedmagdy9835]

@@NEDLeducation For example, the last trading day in September was Thursday (29 September not 30) and so on for the rest of months. How can I use the IF function so that it automatically captures the last trading day for each month?

[@cristianalcideshuarangagom1834]

Hi Nedl, how I can contact to you for some questions? Thanks for the valuable information.

[@Tabiano_FNT]

Why have you put that risk-free rate?

[@noldy90]

how to using bayesian analysis in excell?

[@sarthaksahu9440]

How did he calculate Market cap @14:48

[@user-wg7nw3mh2e]

Uh... does that actually work approximating calculating Beta using the SLOPE function, I just tried it briefly and it is nothing near the actual Beta

[@NEDLeducation]

Hi, and thanks for the question! Yes, the SLOPE function can be used to calculate the beta for a simple linear regression. I might be able to advise further if you expand on what you mean by the "actual beta".

[@user-wg7nw3mh2e]

@@NEDLeducation I mean the SLOPE function did not produce the R^2 ie: slope of the sum of the least squares line ETA: I just realized I use R^2 and Beta interchangeably and this may be a misconception on my part (I don't actually use Beta do anything so this mistaken definition may have gone unchallenged for many years) But is it's not the R^2 what relationship is beta demonstrating exactly? ETA 2: OK this was a good chance to refresh i calculated the Betas manually and SLOPE does work, now if you could just explain WHY it works...

[@kinggeek1960]

What happens when you have 200 positions across various sectors

[@jczamora_lpa]

👏👏👏

[@ThatsLifeDude]

What is your background? You appear to be extremely knowledgable

[@benjamindean9574]

Does anyone know how he got access to that data, how he was able to copy paste the data?

[@monkynutss711]

the covariance.s index formula you used returns #value for me. I have copied it exactly. Any way you know of that it can be fixed?

[@NEDLeducation]

Hi Timothy, and thanks for the question! This might be due to errors that occur when calculating returns with missing data. Try looking for missing price data and backfilling it.