hello everyone welcome to the session in this video I'll be discussing how to determine expected credit loss or ecl provision for trade receivables in an Excel worksheet okay so before we move on to this let's familiarize ourselves with a few PPD PD means the probability of default so what is probability of default probability of default means the likelihood of default over a given time Horizon so we can calculate this PD based on flow rate which I will explain you shortly then lgd so lgd means lost given default plus given default so lgd is the estimate of loss Rising form so this is a calculated basis basically by taking the difference between the contractual cash flows due and the expected amount to be received including any collaterals okay so that is the lgd the first one is the probability of D4 and the second one is the loss given default thirdly we should know about the flow rate so what is the meaning of flow rate the flow rate is calculated by tracing the movement of receivable balances from one bucket to the other on a monthly basis okay so here we calculate the flow rate to identify the probability of default by comparing the movement in the receivable balances from one bucket to the other bucket okay this I will show you when I explain the Excel calculation to calculate the ECS promption then we need to Define our default definition or default rule so default rule or the default definition so this is determined by the company's credit policy and specifies the number of days of credit period allowed to customers for example uh if the company policy provides a 90-day credit period Then the balance is exceeding 90 days will be treated as 100 default okay so when you uh when you calculate the flow rate so when you calculate the flow rate by tracing the movement from one bucket to the other bucket you need to pay attention to this default rate because once the market moves to default rate it will be 3 now let's see how to calculate the flow rate first of all we need to set the default definition and then we need to gather historical receivable balances now in this case now in this calculation I have selected from January 2018 till 2020 for three years historical balances have been selected so these balances should be categorized into monthly buckets based on their respective receivable Aging for example 0 to 30 days 31 to 60 days 61 to 90 days and so on and so forth remember we set the default definition as 60 days so anything beyond 60 days will be considered as 100 default then the flow rate is then calculated by tracking the movement of receivable balances from one bucket to the another bucket on a monthly basis until they reach the default definition so we can observe the percentage of receivable balances moving from one bucket to another such as 0 to 30 days bucket in January 2018 to the 31 to 60 days bucket in February 2018. as you can see the Aging balances of 0 to 30 days in January 2018 was 761 188 reduced to 624 768 in February 2018 in the 31 to 60 day bucket this shows a flow rate of 82 percent of flow rate or the default rate of 82 percent when you compare with January 2018 balance of 0 to 30 day bucket with February 2018 balance 624 768 of 31 to 60 days bucket so after calculating all the respective default rates or the flow rates next step is to calculate the average default rate in the respective buckets for example 0 to 30 days bucket we we have the default rate of 82 in February until uh December 2020 54.74 so when you take the average flow rate so that will be 75.09 for 0 to 30 days bucket as you can see so I have taken from February 2018 until December 2020 so that average default rate is 75.09 and 31 to 60 days Market as you can see which is 58.85 however 61 to 90 days the default rate is going to be 100 91 plus days is going to be 100 because our default definition says uh more than 60 days the outstanding balance or the default rate is going to be 100 so therefore more than 61 days represents 100 default rate then this calculated average uh default rates will be applied to the outstanding balance we call it exposure at the end of the year for trade receivable to calculate the ecl provision as you can see from this uh these are the probability of default the default rates uh under each buckets so this is the exposure the exposure is the the balance outstanding at the uh at the urine which is 4.5 million which is nothing but the balances outstanding as of 31st December 2020 as you can see this balance is taken from the outstanding balance of December 2020 which is going to be 4.5 million four million 592 2017 which is exactly the that amount so to this exposure we need to apply the probability of default which is calculated through this historical trade receivable by calculating the flow rate having calculated the probability of default next step is to identify the loss given default to arrive at the adjusted provision so basically unregistered provision will be the probability default probability of default times the exposure will be the provisions unagisted for the loss given default once the loss given default is factored into this calculation we can identify the final ecl portion which is going to be 392 970 which is 47 of the unregistered provision unregistered provision is the uh on the exposure uh when you apply 75.1 percent so that will be the unadjusted provision to this if you apply the loss given default so that will be the adjusted provision this lgd is calculated based on the company's recovery experience for trade receivables if the customer has made any deposits or advances those can also be considered as recovery by calculating the recovery rates using the flow rate we can determine the average recovery rate so which will be the uh then we need to calculate the loss given default by using the average recovery rate now next step is to calculate the recovery rate to calculate the recovery rate we can use the average flow rate that we have calculated in this calculation the average default rate will be when you take this total from 0 to 30 days until 91 plus uh days so it's going to be 83.48 so the flow rate is 83 point let's assume 83 percent okay to calculate the recovery recovery rate so recovery rate will be 17 if the default is 83 percent as calculated based on the flow rate average flow rate 83 and the recovery rate is 17 percent so therefore if you want to calculate the uh then the lgd the lgd will be calculated like we need to use this Formula 1 minus recovery recovery rate times you need to take the net exposure divided by gross exposure okay so this net exposure should be divided by this should be divided by the gross exposure to calculate the lgd so we can use this particular formula which is 1 minus recovery rate times the net exposure divided by gross exposure our gross exposure is as per this question as per this example 4.5 million let's assume our gross exposure as 4.6 million and let's assume there is an advance payment of advance payment of 2 million so therefore our net exposure will be 4.6 million minus 2 million which is going to be 2.6 so when you apply this Formula 1 minus 1 minus the recovery rate is 17 okay which is 83 again times the net exposure is how much net exposure is 2.6 million divided by 4.6 million which is going to be 83 percent times so then we have to take so we need to take 83 percent okay so 2.6 divided by 46 4.6 percent which is going to be roughly 57 percent so when you take these two rates which is going to be 83 times 57 47 so this 47 will be the lgd so that lgd has to be applied here to calculate the adjusted provision as you can see uh the provision the ecl provision unadjusted is calculated based on the probability of default times the exposure however the adjusted provision ecl provision is calculated based on the unregistered ecl provision times the loss given default which is the uh the final ecl calculation now if you want to calculate the the bucket in provision so what you can do is this uh calculated provision can be divided based on the gross exposure so that it will give you the bucketing a provision so 0 to 30 days we need to apply 35 31 to 60 days we need to apply 28 61 to 90 days 47 and 91 onwards 47 percent these are 47 because our probability of default is treated as 100 more than 60 days uh more than 60 days which is our default definition so this is a basic method of calculating this ecl provision so it is important to mention that there are other complex methods to identify the probability probability of default and loss given default by using different models so I plan to discuss those models in my future videos in the meantime if you have any queries or suggestions please comment below so if you are using a different method in your company please share it so that others can benefit from your insights so that's all for today I'll see you soon in another video until then take care and goodbye