fineract-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sander van der Heyden <sandervanderhey...@musonisystem.com>
Subject Re: [Mifos-developer] Portfolio at Risk
Date Fri, 10 Mar 2017 13:22:58 GMT
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 <zayyad@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: zayyad@intrasofttechnologies.com
>
>
>
>
>
>
>
> -----Original Message-----
> From: Sander van der Heyden [mailto:sandervanderheyden@musonisystem.com]
> Sent: 10 March 2017 04:00 PM
> To: Mifos software development <mifos-developer@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:zayyad@intrasofttechnologies.com>
> zayyad@intrasofttechnologies.com
>
> >
>
> >
>
> >
>
> > -----Original Message-----
>
> > From: Sampath Kumar G [ <mailto:sampath@confluxtechnologies.com> mailto:
> sampath@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-developer@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> 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 <
>
> >  <mailto:zayyad@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:zayyad@intrasofttechnologies.com>
> zayyad@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-developer@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
>
>

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