1. Introduction
IFRS 9 is an International Financial Reporting Standard (IFRS) promulgated by the International Accounting Standards Board (IASB). It addresses the accounting for financial instruments. It contains three main topics: classification and measurement of financial instruments, impairment of financial assets and hedge accounting. It will replace the earlier IFRS for financial instruments, IAS 39, when it becomes effective in 2018.
Both IFRS9(staging 2) and CECL will requre the lifetime expected credit: loss. Here is to introduce an online lifetime credit loss calculation engine.
Please click ifrs9 calc engine to run with this sample data.
2. Calculaiton Engine usage
To do the calculation, we need these items:
1, loan information: outstanding(EAD), maturity time, interest rate, and the current time of the loan (to link to the correct time predicted pd as explained below). To be simile, this online engine assumes the LGD/Prepay are constant for ech month. So the input loan informaiton should include these columns:
2, predicted lifetime pd file (this engine is developed to calculate IFRS9/CECL ECL for any time loan, including historical time for backtesting. so it needs these 4 columns: 3 columns of combined levels(year/quarter, month order to indicate the pd, pd risk rating) and 1 column gives the pd values). The most simple file should have the loan snapshot time and the PD risk rating of the borrower. A sample data is from here
3. Calculation
3.1. Upload the PD file
The predicted PD file is necessary for calculation. Since different kind of PD and different value of PD will be used for test, the option is provided to upload the customed PD values for the test.
3.2. Input the loan information
As explained before, the information about the loan is needed. An example of the loan information is given as below:
3.3. Output
The lifetime ECL/PD and 1 year ECL/PD will pop out.
At the bottom it prints out the monthly cash flow for ECL calculation. It starts from Month 1 with full outstanding balance. Every month there is amoortization payment, interest payment, prepay, credit loss and new drawn amout if there is. Finally there is the month end balance and the corresponding ECL in that month. It will starts from Month 1 until the balance down to 0.
A snapshot of the detailed calculation is
4. Web Development
This part will introduce about the development of the online calculation engine website. It is more like a notes for myself to summarize the logic of the development.
Since the calcualtion is by python, flask is selected as the framework. CSS is Bootstrap which is very easy to use to build a moden look webpage. The ouload/submit/forms are all Bootstrap widgets.
4.1. html file
html file has six functions:
1. get the uploaded pd file
2. post the uploaded pd file
3. get the input loan informaiton
4. post the input loan information
5. get the submition for calculation
6. post the submition result
The full page is a Bootstrap container with two main forms.
First, The upload widget is fieldset inside the form. A <form> tag is marked with enctype=multipart/form-data and an <input type=file> is placed in that form. If PD file is submitted to upload, the python code will GET the POST information and will check if it is csv or not. It will only accept csv file. If there is no PD file or the POST method is not activated in the html page, it will return to the upload page. <form> tag has the post method to active the route. The route will call the ptyhon upload file function and upload the file to the server.
{% if request.args.get('filename') is none %}
<form action="" method=post enctype=multipart/form-data>
<fieldset>
<legend>Upload PD CSV File</legend>
.........
</form>
{% else %}
PD file {{request.args.get('filename')}} has been uploaded; Please fill in the loan information.
{% endif %}
If the file is successfully uploaded and successfully POST, it will redirect to ifrs9 with the filename as the parameter like below
The html file and the python upload file function is linked by "request".
{{filename}}
Second is to input the loan information in the form. It also has the post method
<form action = "" method="post" role="form">
<fieldset>
<legend>Input Loan/Borrower Information</legend>
.....
</form>
After all the input forms are filled and submited to calculate, the route will call the flask ifrs9 function as introduced below.
if calculaion data is returned then it will be displayed on the page in div of the centered class.
{% if data is not none %}
<br>
<div class="centered">
{{data | safe}}
</div>
{% else%}
NONE
{% endif %}
4.2. python file
There are two piece of python code.
The first function is to upload the PD file with enabled GET and POST method. It will use @app.route(url, methods=[]) to tell what url will activate the python function. The route() decorator is used to bind a function to a URL. If it excutes successfully, it will redirect to the ifrs9 url.
return redirect(url_for('ifrs9', filename = filename))
The second function is ifrs9 which is to get html information and do the calculation.
After all the input forms are filled and submited to calculate, the route will call the flask ifrs9 function:
1, it will check the filename first, if no file uploaded, it will pop out message to ask for upload the pd file.
2, if there is uploaded file, then it will request the filled in form values from html
3, it will call the function to do ECL calculation
4, it will flash out the calculation result
flash('Total IFRS9 Lifetime EL is: ' + str(output[1]) + ', Total IFRS9 Lifetime PD is: ' + str(output[2]))
flash('Total IFRS9 1 year EL is: ' + str(output[3]) + ', Total IFRS9 1 year PD is: ' + str(output[4]))
5, it will return to render_template with the output data(the detailed monthly calculation) as parameters to html file for display
return render_template("ifrs9_mockup.html", data = style + output[0].to_html(index = False, col_space = 5, float_format = lambda x: '%10.2f' % x, classes='centered'))
5. Batch Run
The above is an example for one single loan calculation. To calculate all the loan at one time, it needs batch run. The basic idea is very simple:
1, get the input file(loan file, pd file and so on)
2, loop through each loan, get the corresponding pd/lgd/prepay information. Then do the calculation.
3, Since step 2 is idpendent for each loan, it can be done parallely.
4, with multiprocessing in python for parallel calculation, the calculation speed will increase dramatically