fineract-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Zayyyad A. Said" <zay...@intrasofttechnologies.com>
Subject RE: [Mifos-developer] Portfolio at Risk
Date Fri, 10 Mar 2017 14:42:47 GMT
Hi Sampath,

 

In the script below, you have added “totaloverdue” only which is basically principal overdue
+ interest overdue + fees overdue + penalty overdue. That is not what am looking for.

 

If you look at this example that I had given in my previous reply to Sander:

 

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/=

 

What I basically need to in this report is the sum of Principal Outstanding for Loans in Arrears
which in the example above is the 200,000/-. We can name this column as “Portfolio at Risk”.

 

How do you think we can achieve that?

 

Regards;

 

 

*******

Zayyad A. Said | Chairman & C.E.O

 

Cell No.: +254 716 615274 | Skype: zsaid2011

Email: zayyad@intrasofttechnologies.com 

 

 

 

-----Original Message-----

From: Sampath Kumar G [mailto:sampath@confluxtechnologies.com] 

Sent: 10 March 2017 04:04 PM

To: Mifos software development <mifos-developer@lists.sourceforge.net>

Cc: dev@fineract.incubator.apache.org

Subject: Re: [Mifos-developer] Portfolio at Risk

 

Please refer following query.

 

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 %",

x.totaloverdue

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,

sum(laa.total_overdue_derived) as totaloverdue

 

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 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 6:15 PM, 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: zayyad@intrasofttechnologies.com

> 

> 

> 

> -----Original Message-----

> From: Sampath Kumar G [mailto:sampath@confluxtechnologies.com]

> Sent: 10 March 2017 01:44 PM

> To: dev@fineract.incubator.apache.org

> Cc: Mifos software development <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/> *

> 

> #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]

> >

> >

> >

> 

> 

> ------------------------------------------------------------

> ------------------

> 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

> Mifos-developer mailing list

> mifos-developer@lists.sourceforge.net

> Unsubscribe or change settings at:

> https://lists.sourceforge.net/lists/listinfo/mifos-developer


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