fineract-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sampath Kumar G <samp...@confluxtechnologies.com>
Subject Re: Portfolio at Risk
Date Fri, 10 Mar 2017 10:43:37 GMT
Hi Zayyad,

For loan balance arrears, do you need total arrears or only the principal
arrears amount?

Thanks and regards,
Sampath


​
*Conflux Technologies Pvt Ltd <http://www.confluxtechnologies.com/> *

#304, 2nd Floor, 7th Main Road

HRBR Layout 1st Block

Bengaluru, Karnataka, 560043 INDIA


Disclaimer: The information contained in this e-mail message and any
files/attachment transmitted with it is confidential and for the sole use
of the intended recipient(s) or entity identified. If you are not the
intended recipient, please email: support@confluxtechnologies.com and
destroy/delete all copies and attachment thereto along with the original
message. Any unauthorised review, use, disclosure, dissemination,
forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and is unlawful. The
recipient acknowledges that Conflux Technologies Private Limited or its
subsidiaries and associated companies are unable to exercise control or
ensure or guarantee the integrity of/over the contents of the information
contained in e-mail transmissions. Before opening any attachments, please
check.

On Fri, Mar 10, 2017 at 2:59 PM, Zayyyad A. Said <
zayyad@intrasofttechnologies.com> wrote:

>
>
> 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: zayyad@intrasofttechnologies.com
>
>
>
> [image: Email banner]
>
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message