Minimum Variance Portfolio in Excel: Multi-asset case

Поделиться
HTML-код
  • Опубликовано: 10 дек 2024

Комментарии • 49

  • @financemark
    @financemark  4 года назад

    If you have any thoughts about minimum variance portfolios, or portfolio construction and management more broadly, I'd love to hear about those in the comments below

    • @laithb3091
      @laithb3091 3 года назад

      I found a book "The Sharpe Ratio
      Statistics and Applications" By Steven E. Pav it goes into more details on the subject than my mind can handle but I thought I reference it here as it may help you those who are interested with min variance portfolio.

  • @christopher912
    @christopher912 Год назад +1

    never really worked with excel and never worked with share or the math behind, but was able to do all this for my own calculations. thanks alot man you saved my day!

    • @financemark
      @financemark  Год назад

      Thanks a lot! I'm happy it was helpful :)

  • @BhavanaPerabathini
    @BhavanaPerabathini 6 месяцев назад

    you just saved me sir, thanks a lot, nothing could have helped me more than this!!!!!!!

  • @just70hq
    @just70hq Год назад +1

    You are the man, Mark

  • @mitrarishabh96
    @mitrarishabh96 3 года назад +2

    you've made my dissertation's econometric calculations x100 easy!!

    • @financemark
      @financemark  3 года назад +2

      Amazing! super happy that it helped!

    • @summerspade5139
      @summerspade5139 3 года назад

      Hey I'm doing a similar dissertation on portfolio optimization using cryptocurrencies. would love to know more about your dissertation.

    • @mitrarishabh96
      @mitrarishabh96 3 года назад

      @@summerspade5139 IG @mitrarishabh

    • @NerreraFightStories
      @NerreraFightStories 2 года назад

      @@summerspade5139 I'm doing mine on the exact some topic, would love to see yours because I'm kinda stuck for the best way to test lol. Rn I'm doing correlation of cryptos with each other and with traditional assets then I'm doing portfolio scenarios of equal weighting, min variance and not sure what else

  • @bandana.7077
    @bandana.7077 2 года назад

    you just saved my life with this video, I can never thank you enough sir!! ♥♥♥

  • @williamhayden2492
    @williamhayden2492 3 года назад

    I watch full 3 mins ads just to show support for his work in YT.

  • @SaraMuseDoyle
    @SaraMuseDoyle 4 года назад +4

    thank you so much! this is amazing, you are a saint

    • @financemark
      @financemark  4 года назад

      Thanks - I'm glad it was helpful!

  • @thanhhuynhf9071
    @thanhhuynhf9071 4 года назад +3

    It's very helpful, thank you so much

    • @financemark
      @financemark  4 года назад

      Thanks! I'm happy it was helpful

  • @shivtayal9409
    @shivtayal9409 4 года назад +2

    hey ...I did exact all the steps you did with the exact same company and exact same time period, also I got all the values same untill i performed solver function, but after using solver it showed 0 in all the weight, please help

  • @MrSpellblade
    @MrSpellblade 3 года назад

    5:05 Excess return is the (firms return - a risk-free rate), not minus the firm's average. But good video!

    • @financemark
      @financemark  3 года назад

      That's accurate. But, the reason you subtract the firm's average is because variance is the (1/n) Sum (x-ave(X))^2

    • @philipsmith77
      @philipsmith77 2 года назад

      @@financemark Would you start with adjusted monthly close prices from Yahoo Finance then get returns using =LN(Month2/Month1)? I got returns using that method and then subtracted the risk free return of the 3 month T-Bill for the same period to get a real return. Is the next step to get the excess real return using the process that you mentioned in your video (Monthly Return / Average of Monthly Returns)?. To get the annualized real return I took the average of the monthly real return data *12 and then subtracted net expense ratio.

  • @way2worldoffinance436
    @way2worldoffinance436 3 года назад +1

    COVARIANCE MATIRIX -DO WE NEED TO DIVIED BY N-1? NO OF OBSERVATIONS -1?

  • @laithb3091
    @laithb3091 3 года назад +1

    You are awesome 👌 wow 👏 amazing 👍

    • @financemark
      @financemark  3 года назад

      Thanks so much - I very much appreciate it

  • @mome6889
    @mome6889 29 дней назад

    why does excel solver, despite everything exactly as you showed, always return zero and turns all my weights into zero

  • @skitdeggo
    @skitdeggo 3 года назад +1

    Can you use the same framework to optimize the Sharpe ratio?

  • @sonwabomciya8205
    @sonwabomciya8205 4 года назад +1

    Amazing Thank you

    • @financemark
      @financemark  4 года назад

      Thanks! Happy it was helpful

  • @PlumedeTTV
    @PlumedeTTV 2 года назад

    So would the standard deviation still be the same as in your other video of 2 assets? Just the sqrt of the minimum portfolio variance?

  • @marlonpla2430
    @marlonpla2430 2 года назад

    anybody has an idea how to do this same process but adding a risk free asset?

  • @Jose-fv7re
    @Jose-fv7re 4 года назад

    Thanks for your video. How would you minimize the P&L volatility of a portfolio using an index as a hedge?

    • @financemark
      @financemark  4 года назад

      I'm not sure that I totally follow what you mean by using the index as a hedge (i.e., what exactly are you hedging against). Do you mean that you are trying to minimize tracking error? Or, That you are trying to obtain pure alpha by off-setting teh firm's beta?

    • @Jose-fv7re
      @Jose-fv7re 4 года назад

      @@financemark Yes, minimize the tracking error. Suppose a portfolio with n shares belonging to various sectors, indexes and countries (no fx risk), and you want to hedge the PnL exposure of that portfolio using the best possible index that minimizes the PnL volatility of your portfolio (i.e. you don't want to have +$10MM one month and -$10MM the next month). How would you go about finding the best index hedge? would you consider using the optimal hedge ratio or is there any other theory that can be used?

  • @digdug008
    @digdug008 2 года назад

    I'm having trouble pulling in data from Yahoo Finance! now :( I'm guessing they changed something on their end. Is there still a way to pull in the necessary data for this calculation in excel? I know excel has a built-in command for STOCKEXCHANGE but not sure if that is what we need.

    • @lucassilveira4869
      @lucassilveira4869 2 года назад

      i know its too late, but you can try using python, R ou Power BI

  • @kianfayazbakhsh1862
    @kianfayazbakhsh1862 4 года назад

    Thanks for the video! Could you possibly tell me that how can I find standard deviation and sharp ration for the portfolio?
    Also, I was wondering if we can apply this method for portfolio with two assets?

    • @AvatardSwag
      @AvatardSwag 2 года назад

      This video covers standard deviation and sharpe ratio optimisation
      ruclips.net/video/OGhGz8trZtw/видео.html

  • @Fransman1624
    @Fransman1624 3 года назад

    How would you graph the minimum variance frontier using this information?

    • @financemark
      @financemark  3 года назад +1

      Good question. This would be laborious and a bit annoying to do in excel (unless you have a macro). You would need to maximize the return for each individual set value of variance. One would then create a table and plot it. It would probably be easier in something like R or Stata where you could automate it with a for loop.

    • @Fransman1624
      @Fransman1624 3 года назад

      @@financemark, Unfortunately, it's exactly what I have to do for my portfolio management project lol

    • @financemark
      @financemark  3 года назад

      That sounds painful. I think it might be a matter of tediously constructing the portfolios by using solver multiple times (unless you write a macro to do it, that is)

    • @Fransman1624
      @Fransman1624 3 года назад

      @@financemark I will look into it, thank you!

  • @ziksy6460
    @ziksy6460 3 года назад

    I remembered this to be much more complicated when my professor explained it lol

  • @alihaddadi5162
    @alihaddadi5162 3 года назад

    first of all I appreciate your excellent explanation, I did it with 3 stocks for monthly returns, but at the end it gives me weight "1" for one of them. I double checked all of the parts and everything is correct.Is there any problem or it's usuall to have just 1 for a stock?

  • @nitish31ful
    @nitish31ful 4 года назад +1

    what if I want select the portfolio as per my risk on efficient frontier how can I do that

    • @financemark
      @financemark  4 года назад

      I think you are looking or something like this: ruclips.net/video/3E5nE0fELnU/видео.html