Value-at-risk (VaR) - variance-covariance and historical simulation methods (Excel) (SUB)

Hello everyone!
In today's video, I'm going to explain the Value-at-Risk (VaR) measure of the risk of loss of investments. It estimates how much a set of investments might lose (with a given probability), given normal market conditions, in a set time period such as a day. I will also demonstrate how to calculate it in Excel, using the data on HSBC, Barclays, Lloyds, RBS and Standard Chartered.
Don't forget to subscribe to NEDL and give this video a thumbs up if you want more videos in Finance!
Please consider supporting NEDL on Patreon: / nedleducation

[@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

[@joeaoun6321]

Another extremely helpful video. You do an outstanding job making it look easy!

[@crentistDaDentist]

Most underrated channel for finance. Amazing content and God level excel skills.

[@jb_makesgames2264]

Great Video - as a former bank risk manager I was pleasantly surprised that you referred to Basel regs and VaR

[@NEDLeducation]

Hi, and thanks for the feedback! Always flattered when practitioners find my videos helpful :)

[@lostthe80s]

Absolutely first class work - fantastic step-by-step guide (which I have used for to model my Crypto portfolio of 15 coins). My most sincere thanks !

[@jakeu9623]

your videos have helped me a lot with my excel assignments for my investment management module. It was the best content I could find on youtube. You deserve more viewers and will surely get them soon enough

[@NEDLeducation]

Hi Jake, thank you for your kind words, really appreciate it, and glad you found the video helpful! :)

[@avinashmishra6783]

+1

[@rigtee]

Thanks a lot for this video! This was so helpful for a project of mine for my Derivatives class!

[@olegivanov4883]

Здорово, мне понравилось видео. Особенно наглядность и простота (в хорошем смысле) примера. Знаю английский, но русские субтитры - хорошая идея.

[@NEDLeducation]

Спасибо за фидбек, в ближайших планах - сделать русские субтитры для всех видео на канале.

[@fatimaalmadhi1727]

thank you sooooooooooooooooo much for the video, helped me a lot

[@NEDLeducation]

Hi Fatima, thank you very much for your feedback, glad it was helpful:)

[@fabiosanti7153]

It was extremely helpful. Thanks!

[@marf982b]

One of my favourite video....

[@CFATrainer]

this is just excellent stuff

[@way2worldoffinance436]

Suggest could have used Matrix Multiplication technique for calculating Covariance- Variance Matrix, creating Returns-Average Returns array and then MMULT(Excess Return Array), Transpose(Excess Return Array))/n-1. Simpler than using Index function! I think

[@NEDLeducation]

Hi Vaidyanathan, and thanks for the question! I actually cover this very method in this video: kzbin.info/www/bejne/bnixlHltrtZ-r7M

[@way2worldoffinance436]

I feel its better to use lognormal returns than discreet returns because lognormal returns can be additive but not discreet returns

[@jodyli9653]

Very helpful, thank you 👍

[@mohanbandaru06]

thank you very much....

[@Paparychter]

Fantastic content! Question for you. Since the VaR model measures the potential loss in value over a defined period, which is in this calculations over the span of 1 day, what would be the best way to adjust the defined period? Because the VCV VaR is norm. distributed, could we assume (over a 5 trading day period), that VaR weekly = VaRdaily * SQRT (5) ?

[@yanxiliu6818]

Thanks for your video!! Very clear and detailed. It really helps me a lot with my homework! Just one question, when we calculate the historical VaR, do we need to first reorganize the portfolio returns in order from worst to best, and then apply the PERCENTILE.EXC equation?

[@NEDLeducation]

Hi Yanxi, and many thanks for your feedback! Glad the video helped you study. To apply the PERCENTILE.EXC function, the returns do not need to be sorted. However, for some other purposes (graph plotting, distribution fitting) sorted returns are required.

[@yanxiliu6818]

@NEDL Got it! Thank you so much!

[@lukethomas5660]

Many thanks Savva, another great video. Just joined your Patreon too, best money I ever spent! With either VCV or HS VaR methods, is it possible to incorporate a decay / lambda to the time series so that recent returns are given a greater weighting?

[@NEDLeducation]

Hi Luke, and many thanks for your support, really appreciate it! Please send me a direct message on Patreon to claim your reward :) As for your question, I have examined the most common technique for incorporating decay in VaR - the so-called BRW VaR - in one of the later videos, check it out if you are interested: kzbin.info/www/bejne/eXLWmJ13bLCimrM

[@michaelcheung4524]

did we already assume the normality assumption when you did this example? Will I be wrong if I use log return instead

[@NEDLeducation]

Hi Michael, and thanks for the question! Normality is assumed as we use the normal distribution function in parametric (VCV) VaR. I have got a video that implements non-normal parametric VaR (kzbin.info/www/bejne/n5SmZo1re7KSidk). As for log-returns, you can use them as well, but apply the transformation only after you have constructed your portfolio (averaging over log-returns is incorrect when building a portfolio).

[@amirhoseinyaftian2510]

thank you 🙏😌

[@rajuchoudhari2409]

very nice 😊

[@lucapetruccioli4078]

Thanks for posting this, very helpful indeed. One question for you: how would you change this model in case you wanted to calculate 10 days VaR for the same portfolio of assets on certain dates, for instance on 04.07.2020 and 04.06.2020 ?

[@Valeria-qh9yj]

I have the same question, thank you!

[@NEDLeducation]

Hi Luca, and many thanks for the feedback! As for your question: to calculate a 10-day VaR you can just scale the mean and variance accordingly, i.e., multiply the mean by 10 (or preferably use the geometric formula) and the standard deviation by square root of 10. We have a separate video on volatility scaling that investigates the issue in greater depth: kzbin.info/www/bejne/lauQYWutj7qZeMU. When you have 10-day mean and standard deviation, you can apply the same VaR procedure. As for VaR on different dates, the only thing that would be different for a VaR on 4th June and 4th July is the available historical data you can use to calculate mean and variance (or use for historical simulation). Theoretically, you can apply a "rolling" VaR for different dates, using all observations available up to a certain time period. Hope it helps!

[@NEDLeducation]

@@Valeria-qh9yj Hi Valeria, and thanks for the question! Please check the reply above. Hope it helps.

[@Valeria-qh9yj]

@@NEDLeducation Many thanks! The video is extremely helpful! Could you please also suggest whether you have a video / or advise on how to use the SMA and EMA returns in the VCV matrix?

[@NEDLeducation]

@@Valeria-qh9yj Hi Valeria and glad the video helped! As for SMA and EMA for value-at-risk, the SMA return is actually applied in this video, as all historical observations are given exactly the same weight both when calculating average return and variance for the VCV VaR and when considering the historical simulation VaR. As a matter of fact, we have already got the video that applies the logic of exponential moving averages (assigning a higher informational weight to more recent observations) to historical simulation VaR (it is also known as BRW VaR, check it out if you are interested: kzbin.info/www/bejne/eXLWmJ13bLCimrM). As for EMA for VCV VaR, here the trick is even easier: you can just use the same weighting factors as in BRW VaR to calculate the average return as well as the standard deviation of a portfolio, and then use the standard parametric VaR formula. Perhaps I can record a video on that around next week if that would be helpful?

[@qu44ck65]

I totally understood the video! but just one thing I would like to ask: How should I go about calculating returns on the portoflio if i am allocating my capital according to different weighing schemes?

[@NEDLeducation]

Hi, and thanks for the question! For VaR simulations, simply use the SUMPRODUCT function and apply it to your returns and respective portfolio weights. I have got several videos in the portfolio management tutorial on weighting schemes and their Excel applications, so check these out if you are interested, for example: kzbin.info/www/bejne/nHjSp2ycq9qabtE

[@What_ever2024]

hey can you please show me how to calculate it using a rolling window analysis that moves 1 day at a time but mantains a 1 year length, im struggling to derive a covariance matrix for all the rolling window periods at the same time

[@JorgeOporto]

I saw your video of Value at Risk, and my doubt is, how do you make the VaR measure if you allow short sellings in your portfolio?

[@А_если_так_подумать]

Что-то на гениальном 😩

[@MG-yt4om]

Hi Matt in many other examples found online the formula to compute the VCV VaR don not include the addition of the portfolio mean return. do you have any idea why. Since adding the mean results in significally differente VaR I would like to better understand the reasoning behind including or excluding the mean. Thanks

[@NEDLeducation]

Hi, and thanks for the question! Adding or not adding the mean does depend ultimately on the nature of the assets you calculate VaR for (some assets such as commodities can be expected to have theoretically zero drift) and on the time horizon as well (the shorter it is, the more negligible is the effect of the mean onto the calculations).

[@Newgoldnuggets]

in Covarienece matrix, why your diagnoal elements are not 1.

[@figuredoutfitness8154]

How to make t+1 day line in excel? Like the one which some software shows in simulation

[@saadeddinerafiky7743]

great video !!!! thanks for the upload just one question can i use the same method for more than 5 securities or is there a variation in the calculation ??

[@NEDLeducation]

Hi Saad, many thanks for the feedback and for the question! Yes, the same method is applicable to any number of securities.

[@saadeddinerafiky7743]

@@NEDLeducation thank you very much !!! keep up the great work !!!

[@ageiro]

For long/short portfolio do you just take the returns of the stock you wanna go short * -1 or is it calculated in a different way?

[@NEDLeducation]

In essence, that is correct. If you want to build a self-financing (zero-investment) long-minus-short portfolio, your return will be just the difference of your long and short portfolio returns. If you have a portfolio with arbitrary weights and you want to short some assets in it, they will just go into it with negative weights (longs go in with positive weights as usual). For example, if you have invested $100,000 of your equity and your weights are 120% in stock A and -20% in stock B that means you have short-sold $20,000 worth of stock B to fund purchasing $120,000 of stock A. Overall, sometimes it is easier to think of short-selling as your personal "leverage" in portfolio management. Hope it helps!

[@ageiro]

@@NEDLeducation Thanks a lot!

[@mastertigr]

Спасибо большое за ваши ролики и ваши труды, очень доходчиво все описано) Единственное я нигде не нашел информации про масштабирование исторического VaR на несколько дней. С вариационно-ковариационным я разобрался по вашим ответам в комментариях, а вот про исторический так и не понял, в нем видь не используется не среднее значение, не отклонение. Извините, кажется я тупенький

[@NEDLeducation]

Привет, Илья, и рад что видео оказались полезными! Исторический VaR действительно масштабировать менее очевидно и чуть труднее. Самый простой и очевидный подход: рассчитать кумулятивные доходности по интервалу продолжительности, которая интересует (например, кумулятивные доходности по пятидневным периодам), и пятидневный VaR тогда будет считаться как соответствующий перцентиль этих кумулятивных доходностей.

[@mastertigr]

@@NEDLeducation спасибо большое за ответ, и за ваш прекрасный канал)

[@vebe7109]

How to calculate VaR for cupon bond portfolio? Any suggestions?

[@NEDLeducation]

Hi Vebe, and thanks for the question! Generally, VaR is estimated the same way for any assets that are continuously traded. So simply plug your bond prices and proceed with exactly the same steps.

[@user-yx5vf8ep5m]

Thank you, but I have a question. How to measure VaR by semi-parametric methods. I want a practical example.

[@NEDLeducation]

Hi, and thanks for the question! It depends on what methods you consider semi-parametric and which you are interested in. The simplest method among the ones I covered you could call semi-parametric is perhaps BRW-VaR: kzbin.info/www/bejne/eXLWmJ13bLCimrM. Hope this helps!

[@user-zn7dt9bd4x]

Can I calculate the Portfolio VaR using your same method or how Can I find the portfolio VaR?

[@LUICT15]

Does the final value mean you are 99% confident that you will not lose more than $27,370 in a single day or in a year or two years?

[@NEDLeducation]

Hi, and thanks for the question! It is a daily VaR, so in one day.

[@khanjack4758]

Hi I am confused by a basic question - when you are taking the average of return numbers which have negative returns - is it simply because you are adding them all and if the addition leads to a negative total - would up with a negative average otherwise it will be positive - hence the average of a return of -5% and say +6% would simply be +0.5%. Very basic question :)

[@NEDLeducation]

Hi Khan and many thanks for your question! Yes, that is exactly the reason why the average has been negative in this example - it turned out to be that the portfolio has lost in value over the sample period :) Obviously, a more accurate representation would be the geometric mean, but the arithmetic mean (simple average) most of the time leads to decent approximations. If you are interested, please check out our channel and playlists for even more videos on VaR :)

[@alexhongnhungvu3564]

Thank you for your great tutorial. Could you please kindly explain why the function percentile.exc is used instead of percentile.inc? Thanks in advance!

[@NEDLeducation]

Hi Alex, and many thanks for your feedback! Exclusive percentile is used instead of the inclusive percentile as returns are treated as continuous. Inclusive percentiles can be more useful when the sample size is meaningful (e.g., we want to find an observation with a particular integer rank). From a risk management perspective, losses calculated using exclusive percentiles are always larger, so it can also be treated as a more conservative approach. Hope it helps!

[@alexhongnhungvu3564]

NEDL that’s a very helpful answer! Thanks a lot and stay safe!

[@rmdfra]

What would this work for a FX Forward contract? Would you take the returns for the interest rates as well?

[@NEDLeducation]

Hi Rafael, and thanks for the question! VaR can be applied to foreign exchange as well, where returns can be calculated as daily changes in the exchange rate. Interest rates, if charged on the contract, can be accommodated daily as well using a total return index for a currency, with daily interest rate capitalised onto the exchange rate.

[@renalnunes1692]

Hello, this is a nice explanation. How can I use the historical stimulation approach and the model building approach to calculate the VaR of a single stock?

[@NEDLeducation]

Hi Renal, and glad you liked the video! For a single stock, you can simply treat its portfolio weight as 100% in this template, or use historical mean and standard deviation for VCV. For historical simulation, just apply the percentile function to the array of individual stock returns rather than portfolio returns. Hope it helps!

[@techlover555]

Thanks for posting. I have a question. How do you calculate the annual historical VaR from the daily historical VaR? I know how to do it for the covariance-variance method by scaling the mean and std dev but the same cannot be done with the historical method. Also why can't you use the built in stddev function in excel instead of using the covariance matrix to find the portfolio stddev?

[@NEDLeducation]

Hi and many thanks for the questions! To calculate annual historical VaR, you can either apply bootstrapping (random subsamples from a larger sample. however this would assume return independence), or retrieve a large dataset (5-10 years, for example), and calculate cumulative returns for overlapping consecutive 1-year periods, estimating HS VaR based on these. As for the stdev function for the portfolio, you are correct, you can just calculate portfolio returns by weighting the returns day-by-day, and then apply stdev directly. Hope it helps!

[@AG-ow3oe]

Hi and thank you for the content! Ran some VaR calculations and VCV VaR is much greater than HS VaR for all confidence intervals. What could explain this?

[@NEDLeducation]

Hi Alex, and thanks for the question! This may imply that your returns have negative kurtosis, and therefore normal distribution tails are thicker than the historical distribution. This is quite unusual for financial data but possible.

[@AG-ow3oe]

@@NEDLeducation Thank you for the response! Wanted to also ask you why you do not compute logarithmic returns instead? I noticed that there are very slight differences when doing so. Should I consider them negligible?

[@AG-ow3oe]

Hi again! I caclulated Kurtosis but it isn't negative. So what would this imply for the distribution returns given that VCV VaR is much greater than HS VaR for all confidence intervals? I cannot seem to find an answer for this online. Thank you.

[@alejadroigoyanes]

could you do the montecarlo simulation method aswell?

[@Maftunaish]

Привет! Огромное спасибо за видео, очень информативно и ясно. Можно ли применить данные шаги рассчета VAR для рисков обменного курса, кредитных рисков и рисков ликвидности, т.е. банковских рисков? Если да, то какие переменные для ликвидности банков необходимо брать? Заранее благодарю!

[@NEDLeducation]

Привет! Рад, что видео оказались полезными, и спасибо за вопрос! Для рисков обменного курса - конечно, просто вместо цен акций можно взять обменные курсы по отношению к некоторой базовой валюте, и тогда можно смоделировать риск валютного портфеля. С кредитным риском все чуть сложнее, потому что профили доходностей кредитных портфелей асимметричны слева (есть очень много способов получить от должника меньше денег, чем договаривались, но очень мало способов получить больше). Поэтому кредитный риск лучше считать с помощью каких-нибудь дискретных распределений типа Лапласа и Пуассона, либо асимметричных непрерывных распределений. Но исторический VaR на доходностях кредитных портфелей прогнать можно, хуже не будет. Моделирование риска ликвидности в зачаточном состоянии, пока что он все еще измеряется всякими тяжеловесными коэффициентами (LCR, NSFR), а не теоретически обоснованными моделями, поэтому скорее нет. Про функцию INDEX - аргументы в квадратных скобочках необязательны, и их можно не вписывать, все будет работать (например, если выделен только один столбец, номер столбца можно не указывать, хватит только номера строки). Надеюсь, удалось ответить на все вопросы :)

[@ck-gl7jd]

thank you for the video! it helped me a lot!! I have some more questions. I have calculated the VaR using historical simulation and variance-covariance method. Now I have to determine the mean relative bias, the root mean squared relative bias, annualized percentage volatility and fraction of outcomes covered. Do you have any idea how to calculate this?

[@NEDLeducation]

Hi and glad you liked the video! As for your question, these techniques mainly revolve around comparing different VaR models and evaluating their forward-looking performance. I might make a video on it in the near future. Long story short, mean relative bias shows how much the particular VaR model under- or over-predicts exposures compared to other models, root mean squared relative bias shows how far on average it deviates from them, and fraction of outcomes covered can be used to compare parametric methods to historical methods or to test out-of-sample performance. As for annualised volatility, if you assume return independence, you can just multiply daily volatility by the square root of the number of trading days in a year (most commonly around 252). Under return dependence, volatility scaling is more complicated, you might want to check this video: kzbin.info/www/bejne/lauQYWutj7qZeMU Hope it helps!

[@hosseintayefi-d7x]

how can i access the excel file?

[@kelz5732]

Will using log return here be different to simple return?

[@NEDLeducation]

Hi Kel, and thanks for the question! Yes, it will be different, yet in case of daily returns only marginally. For variance-covariance VaR you would also apply log-returns instead of holding period returns if you were to assume that the distribution is not normal but log-normal. But again, the differences in this case are negligible.

[@monour7907]

@@NEDLeducation is there any problem if i will use the LN return because our professorworks with the LN return to calculate the VaR ??

[@monour7907]

thanks , please i have question on how to lock a row with the keyboard ??

[@NEDLeducation]

Hi Omar, and thanks for your question! Just put a dollar sign ($) in front of the row number, alternatively you can use F4. Hope it helps.

[@archanap107]

Wt if we do it for monthly data

[@NEDLeducation]

Hi Archana, and thanks for the question! The model is applicable to monthly returns as well, it is just it is more common to apply it for daily frequency (banks are required to do so, for example). Hope it helps!

[@archanap107]

How to lock rows?

[@NEDLeducation]

Just put the "dollar sign" ($) in front of the row number. You can also use the F4 hotkey when having a formula selected.

[@user-yx5vf8ep5m]

Please, I wanna the exel file.

[@hotpost4067]

нихуя не понял, но очень интересно

[@hotpost4067]

ставлю лайк на всякий случай

[@NEDLeducation]

Добавили русские субтитры, чтобы было немного понятнее :) Спасибо за лайк!