Hi Sander,
The definition given by CGAP and what I had actually put across means the same thing. Here is your definition of PAR: Portfolio at risk. The value of all loans outstanding that have one or more installments of principal past due more than a certain number of days. Here is my explanation of PAR: 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. By using the statement how much the MFI stand to lose, am referring to the actual principal amount at risk of being lost if the loans with any instalment past due is defaulted and not any income. Lets look at the below example: Disbursed Amount of MFI: 10,000,000/= Principal in Arrears: 50,000/= No. of Loans in Arrears: 25 Outstanding Principal for the 25 Loans in Arrears: 200,000/= Outstanding Principal for all Loans: 5,000,000/= The PAR here will be calculated by taking 200,000/= divide it by 5,000,000/= which should give you 4%. The calculation of PAR in the reports currently is done as Principal in Arrears / Outstanding Principal which if you use the example above the calculation will be like 50,000 / 5,000,000 which will give you 1%. I believe this helps you understand the issue I was trying to raise. Regards; ******* Zayyad A. Said | Chairman & C.E.O Cell No.: +254 716 615274 | Skype: zsaid2011 Email: zay...@intrasofttechnologies.com -----Original Message----- From: Sander van der Heyden [mailto:sandervanderhey...@musonisystem.com] Sent: 10 March 2017 04:00 PM To: Mifos software development <mifos-develo...@lists.sourceforge.net> Cc: dev@fineract.incubator.apache.org Subject: Re: [Mifos-developer] Portfolio at Risk Hi Zayyad, Sorry to jump in here, but this is not a bug or problem, or indeed a definition mismatch. The definition used by CGAP (and every MFI and funder we've worked with so far): Portfolio at risk. The value of all loans outstanding that have one or more installments of principal past due more than a certain number of days. *This item includes the entire unpaid principal balance, including both past-due and future install- ments, but not accrued interest*. It also does not include loans that have been restructured or rescheduled. Source: B3 in this document: <https://www.cgap.org/sites/default/files/CGAP-Consensus-Guidelines-Definitions-of-Selected-Financial-Terms-Ratios-and-Adjustments-for-Microfinance-Sep-2003.pdf> https://www.cgap.org/sites/default/files/CGAP-Consensus-Guidelines-Definitions-of-Selected-Financial-Terms-Ratios-and-Adjustments-for-Microfinance-Sep-2003.pdf This is the most widely accepted definition of PAR with just principal overdue as percentage of principal outstanding, as the MFI doesn't "lose" any income it has not actually earned yet. So it is also in line with what you've stated above:"how much does the MFI stand to lose if all delinquent clients completely default". Thanks, Sandfer Sander van der Heyden CTO Musoni Services Mobile (NL): +31 (0)6 14239505 Skype: s.vdheyden Website: musonisystem.com Follow us on Twitter! < <https://twitter.com/musonimfi> https://twitter.com/musonimfi> Postal address: Hillegomstraat 12-14, office 0.09, 1058 LS, Amsterdam, The Netherlands On 10 March 2017 at 13:45, Zayyyad A. Said <zay...@intrasofttechnologies.com > wrote: > I need Total Outstanding Balance for Loans in Arrears. > > > > > ******* > Zayyad A. Said | Chairman & C.E.O > > Cell No.: +254 716 615274 | Skype: zsaid2011 > Email: <mailto:zay...@intrasofttechnologies.com> > zay...@intrasofttechnologies.com > > > > -----Original Message----- > From: Sampath Kumar G [ <mailto:samp...@confluxtechnologies.com> > mailto:samp...@confluxtechnologies.com] > Sent: 10 March 2017 01:44 PM > To: <mailto:dev@fineract.incubator.apache.org> > dev@fineract.incubator.apache.org > Cc: Mifos software development < > <mailto:mifos-develo...@lists.sourceforge.net> > mifos-develo...@lists.sourceforge.net> > Subject: Re: Portfolio at Risk > > 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/> > 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: > <mailto:supp...@confluxtechnologies.com> supp...@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 < > <mailto:zay...@intrasofttechnologies.com> zay...@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: <mailto:zay...@intrasofttechnologies.com> > > zay...@intrasofttechnologies.com > > > > > > > > [image: Email banner] > > > > > > > > > ------------------------------------------------------------ > ------------------ > Announcing the Oxford Dictionaries API! The API offers world-renowned > dictionary content that is easy and intuitive to access. Sign up for > an account today to start using our lexical data to power your apps > and projects. Get started today and enter our developer competition. > <http://sdm.link/oxford> http://sdm.link/oxford > Mifos-developer mailing list > <mailto:mifos-develo...@lists.sourceforge.net> > mifos-develo...@lists.sourceforge.net > Unsubscribe or change settings at: > <https://lists.sourceforge.net/lists/listinfo/mifos-developer> > https://lists.sourceforge.net/lists/listinfo/mifos-developer
