Devs,
I have noted that the reports showing Portfolio at Risk % are not really
reporting the right PAR but Arrears Rate.
There is a difference between the two:
PAR is how much does the MFI stand to lose if all delinquent clients
completely default and thus its calculated by taking sum of all unpaid
balance for loan with past due repayments divided by total outstanding
balance.
Arrears rate determine what percentage of the portfolio is overdue and this
is simple principal overdue divided by principal outstanding (what the
reports are currently reporting as PAR now).
I would like to add "Loan Balance in Arrears" in the below code, could
someone please guide me on how I can do that?
select concat(repeat("..",
((LENGTH(mo.`hierarchy`) - LENGTH(REPLACE(mo.`hierarchy`, '.', '')) - 1))),
mo.`name`) as "Office/Branch", x.currency as Currency,
x.client_count as "No. of Clients", x.active_loan_count as "No. Active
Loans", x. loans_in_arrears_count as "No. of Loans in Arrears",
x.principal as "Total Loans Disbursed", x.principal_repaid as "Principal
Repaid", x.principal_outstanding as "Principal Outstanding",
x.principal_overdue as "Principal Overdue",
x.interest as "Total Interest", x.interest_repaid as "Interest Repaid",
x.interest_outstanding as "Interest Outstanding", x.interest_overdue as
"Interest Overdue",
x.fees as "Total Fees", x.fees_repaid as "Fees Repaid", x.fees_outstanding
as "Fees Outstanding", x.fees_overdue as "Fees Overdue",
x.penalties as "Total Penalties", x.penalties_repaid as "Penalties Repaid",
x.penalties_outstanding as "Penalties Outstanding", x.penalties_overdue as
"Penalties Overdue",
(case
when ${parType} = 1 then
cast(round((x.principal_overdue * 100) / x.principal_outstanding, 2) as
char)
when ${parType} = 2 then
cast(round(((x.principal_overdue + x.interest_overdue) * 100) /
(x.principal_outstanding + x.interest_outstanding), 2) as char)
when ${parType} = 3 then
cast(round(((x.principal_overdue + x.interest_overdue + x.fees_overdue) *
100) / (x.principal_outstanding + x.interest_outstanding +
x.fees_outstanding), 2) as char)
when ${parType} = 4 then
cast(round(((x.principal_overdue + x.interest_overdue + x.fees_overdue +
x.penalties_overdue) * 100) / (x.principal_outstanding +
x.interest_outstanding + x.fees_outstanding + x.penalties_overdue), 2) as
char)
else "invalid PAR Type"
end) as "Portfolio at Risk %"
from m_office mo
join
(select ounder.id as branch,
ifnull(cur.display_symbol, l.currency_code) as currency,
count(distinct(c.id)) as client_count,
count(distinct(l.id)) as active_loan_count,
count(distinct(if(laa.loan_id is not null, l.id, null) )) as
loans_in_arrears_count,
sum(l.principal_disbursed_derived) as principal,
sum(l.principal_repaid_derived) as principal_repaid,
sum(l.principal_outstanding_derived) as principal_outstanding,
sum(laa.principal_overdue_derived) as principal_overdue,
sum(l.interest_charged_derived) as interest,
sum(l.interest_repaid_derived) as interest_repaid,
sum(l.interest_outstanding_derived) as interest_outstanding,
sum(laa.interest_overdue_derived) as interest_overdue,
sum(l.fee_charges_charged_derived) as fees,
sum(l.fee_charges_repaid_derived) as fees_repaid,
sum(l.fee_charges_outstanding_derived) as fees_outstanding,
sum(laa.fee_charges_overdue_derived) as fees_overdue,
sum(l.penalty_charges_charged_derived) as penalties,
sum(l.penalty_charges_repaid_derived) as penalties_repaid,
sum(l.penalty_charges_outstanding_derived) as penalties_outstanding,
sum(laa.penalty_charges_overdue_derived) as penalties_overdue
from m_office o
join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%')
and ounder.hierarchy like concat('${currentUserHierarchy}', '%')
join m_client c on c.office_id = ounder.id
join m_loan l on l.client_id = c.id
left join m_loan_arrears_aging laa on laa.loan_id = l.id
left join m_currency cur on cur.code = l.currency_code
where o.id = ${officeId}
and (l.currency_code = "${currencyId}" or "-1" = "${currencyId}")
and (l.product_id = "${loanProductId}" or "-1" = "${loanProductId}")
and (ifnull(l.loan_officer_id, -10) = "${loanOfficerId}" or "-1" =
"${loanOfficerId}")
and (ifnull(l.fund_id, -10) = ${fundId} or -1 = ${fundId})
and (ifnull(l.loanpurpose_cv_id, -10) = ${loanPurposeId} or -1 =
${loanPurposeId})
and l.loan_status_id = 300
group by ounder.id, l.currency_code) x on x.branch = mo.id
order by mo.hierarchy, x.Currency
Thanks & Regards;
*******
Zayyad A. Said | Chairman & C.E.O
Cell No.: +254 716 615274 | Skype: zsaid2011
Email: <mailto:zay...@intrasofttechnologies.com>
zay...@intrasofttechnologies.com
