Calculating Unexpected Losses (UL) & Economic Capital Buffer (ECAP) under Basel with Excel example

[@mosesmukambojr8707]

Thanks Andre. What software do you use to make the simulations?

[@StachanovSolutionsServices]

Dear Moses, I've used the Excel plug-in Crystal Ball to create these simulations. Crystal Ball is easy to use and intuitive. However, it is a bit outdated and very expensive. For these reasons, I try not to use it anymore in the more recent videos. Best, André Koch

[@mosesmukambojr8707]

@@StachanovSolutionsServices Thanks Andre. I want you to know that for all the good and knowledge you've done God will do a great thing in your life. Your videos have really helped and given me the confidence to do my masters. Thank you Andre. May God bless you. On behalf of everyone whose gotten something from your videos may God bless you and the works of your hands.

[@StachanovSolutionsServices]

Thank you every much Moses for your kind words. Much appreciated and you are very welcome!

[@harishca9365]

thank you

[@aminahoosainmia]

Hi Andrè , in the excel simulation there is a column sigma pd. What does it refer to?

[@StachanovSolutionsServices]

Hello Amina, sigma or "σ" is the lower case Greek letter for "s". In financial mathematics it used for the standard deviation, so the risk. PD is the probability of default. Best, André

[@Manca990]

@@StachanovSolutionsServices How do we calculate standard diviation od PD . So how did we get the results 0,5%, 0,3% and 0,6%.

[@StachanovSolutionsServices]

@@Manca990 Hello these standard deviations are not calculated but given. They are just assumptions Best, André Koch

[@jimchen8882]

@@StachanovSolutionsServices how can there be a standard deviation for a probability, does pd means probability distribution or something else?

[@StachanovSolutionsServices]

@@jimchen8882 PD stands for probabiliity of default. Say, on average 50 out of cohort of 1.000 loans default. Than the PD is 5%. Since this PD is not the same for all cohorts, groups, or periods there is a standard deviation surrounding this PD. This indicates that the PD can vary. The more it varies, the higher the standard deviation and the more buffer capital the bank needs. Best, André Koch

[@elnuralim2951]

Dear Ander thank you for explanation.IF we have more than thousand costumers in our portfolio it will very complexs to write it with hand to crate sigma and correlation matrix.Do you have any solution for it ?

[@StachanovSolutionsServices]

Hello, for very large data sets Excel might not be the right platform. You might want to look at a mathematical language such as "R". However, in Excel the problem you probably encountered is filling the diagonal axis is the standard deviation matrix. In the following video it is explained how to automate this : kzbin.info/www/bejne/mXLFnXWdrqadqMU

[@StachanovSolutionsServices]

Dear Elnur Alim, I take it that the problem you refer to is filling in the data along the diagonal axis. In this video kzbin.info/www/bejne/mXLFnXWdrqadqMU I show you an Excel trick to solve this issue. Best, André Koch

[@fernandezdecabral]

there is a mistake in the presentation at the beginning of the clip, when it is explaining the formula of Expected Loss, that in the title says Unexpected Loss

[@StachanovSolutionsServices]

Yes, you're right. The label in the left-corner says Unexpected Losses. This should be Expected Losses. I will see whether we can fix this. From the context it is clear what is meant. Thanks for your comment. Best, André Koch