RANI calculation

[Sartaj Hussain]

Using the appended sample dataset, I need to calculate the RANI for each company = i in each year = t using below methodology:

Step 1: Calculation of Default Rate or DR for each year t for each company i
DR_t = gnadd_t / (la_t + la_t-1 + la_t-2)/3
Step 2: Using DR for each company i at the end of year t calculated in step 1, calculate EDF for each year end t for each company i as five year average.
EDF_t = (DR_t + DR_t-1 + DR_t-2 + DR_t-3 + DR_t-4)/5
Step 3: Calculate expected credit loss rate for each year end t and for each company i
exp_cr_loss_rate_t = EDF_t * (1-0.70)
Step 4: For each company i and year end t combination, multiply expected credit loss rate obtained in step 3 to la of the company i at year end t to obtain expected loss for company i in the year t
Where e_loss_t = exp_cr_loss_rate_t * la_t
Step 5: Plug the expected loss at each year end t for each company i to get RANI of the company i for the year end t
RANI_t = ti_t – te_t – e_losst

* Example generated by -dataex-. For more info, type help dataex
clear
input float com_id str47 company_name int date double(la gnpadd te)
1 "Bank Of America N A"          18687   5859.14         .   579.55
1 "Bank Of America N A"          19053   6205.37         .   693.99
1 "Bank Of America N A"          19418      7623         .   908.67
1 "Bank Of America N A"          19783   8515.08         .  1109.03
1 "Bank Of America N A"          20148   9263.57         .  1160.23
1 "Bank Of America N A"          20514  12346.38         .  1304.34
1 "Bank Of America N A"          20879  13650.78         .   1428.5
1 "Bank Of America N A"          21244  15346.25         .  1270.26
1 "Bank Of America N A"          21609  19451.75         .  1633.05
1 "Bank Of America N A"          21975  23589.06         .  2749.46
1 "Bank Of America N A"          22340  18185.96         .  1723.44
1 "Bank Of America N A"          22705  21912.24         .  1547.62
1 "Bank Of America N A"          23070  20716.12         .  2041.42
2 "Bank Of Bahrain & Kuwait Bsc" 18687    382.91      -.46    30.28
2 "Bank Of Bahrain & Kuwait Bsc" 19053    643.55     17.27    48.43
2 "Bank Of Bahrain & Kuwait Bsc" 19418     692.6      24.6    68.57
2 "Bank Of Bahrain & Kuwait Bsc" 19783    737.91     27.94    73.16
2 "Bank Of Bahrain & Kuwait Bsc" 20148     952.5    -40.34    75.79
2 "Bank Of Bahrain & Kuwait Bsc" 20514    904.18     31.69    104.1
2 "Bank Of Bahrain & Kuwait Bsc" 20879   1157.02    -13.09   101.69
2 "Bank Of Bahrain & Kuwait Bsc" 21244   1620.18     -39.9   112.12
2 "Bank Of Bahrain & Kuwait Bsc" 21609    1697.5     60.93   140.26
2 "Bank Of Bahrain & Kuwait Bsc" 21975    1579.8       -.9   173.72
2 "Bank Of Bahrain & Kuwait Bsc" 22340    1482.8    -64.49   136.08
2 "Bank Of Bahrain & Kuwait Bsc" 22705    1623.4      3.31   116.88
2 "Bank Of Bahrain & Kuwait Bsc" 23070   1728.86       .89    112.4
3 "Bank Of Baroda"               18687 228676.36    751.81 16162.22
3 "Bank Of Baroda"               19053 287377.29   1312.25 23207.87
3 "Bank Of Baroda"               19418 328185.77   3517.83 28638.31
3 "Bank Of Baroda"               19783 397005.81   3893.32  31953.4
3 "Bank Of Baroda"               20148 428065.14   4385.54 36535.68
3 "Bank Of Baroda"               20514 383770.18   24259.6 45901.91
3 "Bank Of Baroda"               20879 383259.22   2197.66 38371.32
3 "Bank Of Baroda"               21244 427431.83  13761.68  29870.4
3 "Bank Of Baroda"               21609 468818.74  -8247.62  32465.4
3 "Bank Of Baroda"               21975 690120.73  21148.67 67988.07
3 "Bank Of Baroda"               22340 706300.51  -2710.44 63139.34
3 "Bank Of Baroda"               22705 777155.18 -12611.59 54029.46
3 "Bank Of Baroda"               23070 940998.27  -17295.7 64408.03
4 "Bank Of Ceylon"               18687     66.68      -.39     7.65
4 "Bank Of Ceylon"               19053     80.88      -.31    11.08
4 "Bank Of Ceylon"               19418     99.89      -.01    10.18
4 "Bank Of Ceylon"               19783    161.26      -.06    15.57
4 "Bank Of Ceylon"               20148    218.19      -.04    19.88
4 "Bank Of Ceylon"               20514    243.88      -.07    20.54
4 "Bank Of Ceylon"               20879    296.18      -.28    28.69
4 "Bank Of Ceylon"               21244    345.86         0    28.75
4 "Bank Of Ceylon"               21609    302.35      2.48    26.79
4 "Bank Of Ceylon"               21975    341.36     16.68    22.09
4 "Bank Of Ceylon"               22340         .         .        .
4 "Bank Of Ceylon"               22705         .         .        .
4 "Bank Of Ceylon"               23070         .         .        .
5 "Bank Of India"                18687 213096.18     -71.1 16973.46
5 "Bank Of India"                19053 248833.35   1082.42 25910.77
5 "Bank Of India"                19418  289367.5   2871.28 29078.99
5 "Bank Of India"                19783 370733.54   2559.08 35284.34
5 "Bank Of India"                20148 402025.55  10489.06 37994.78
5 "Bank Of India"                20514 359188.96  27471.11 43342.46
5 "Bank Of India"                20879 366481.67   2472.53 38904.12
5 "Bank Of India"                21244 341380.19  11811.55  42267.6
5 "Bank Of India"                21609 341005.94  -1793.82  42257.3
5 "Bank Of India"                21975 368883.31    689.29 41913.75
5 "Bank Of India"                22340 365686.52  -4307.97 35660.78
5 "Bank Of India"                22705 420841.79 -11053.99 31116.01
5 "Bank Of India"                23070 485899.64  -6690.84 36744.19
end
format %td date

[Clyde Schechter]

This is pretty straightforward. I note, however, that the variable ti_t referenced in the final RANI equation is not included in the data. Nor can I treat it as a typographical error intended as te_t since the latter does appear in the formula already and using it as the intended ti_t would lead to cancellation. I assume your full data set does not suffer from this limitation and this code will perform properly therein.

gen int year = year(date), before(date)
xtset com_id year

gen DR = (gnpadd)/((la + L1.la + l2.la)/3)
gen EDF = (DR + L1.DR + L2.DR + L3.DR + L4.DR)/5
gen exp_cr_loss_rate = EDF*(1-0.70)
gen e_loss = exp_cr_loss_rate*la
gen RANI = ti - te - e_loss