Hi Zayyad, Ah now I get you, you are completely right, that's indeed a bug then.
Sander 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> Postal address: Hillegomstraat 12-14, office 0.09, 1058 LS, Amsterdam, The Netherlands On 10 March 2017 at 14:20, Zayyyad A. Said <zay...@intrasofttechnologies.com > wrote: > 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 <zayyad@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-developer@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:support@ > 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> 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: <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-developer@lists. > sourceforge.net > > > Unsubscribe or change settings at: > > > <https://lists.sourceforge.net/lists/listinfo/mifos-developer> > https://lists.sourceforge.net/lists/listinfo/mifos-developer > >
