EMI is a calculated amount that is paid by user every term.
Type of EMI
- Fix EMI
- Equal Principal EMI
- Staggered EMI
Fix EMI: In this EMI ,Amount is calculated by formulla.
Formulla:
ROUND( ((pi_amount) * (pi_roi/(l_rate*100))) * (power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) / ((power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) -1))
Equal Principal EMI: In this EMI ,Loan Amount is divide by tenure of loan after that Interest can be add on that calculated amount.
Eg: Loan Amount =500000 , Tenure=12 (EMI)
So Equal Principal EMI= Loan Amount/Tenure + Interest
Formulla: ROUND((pi_amount)/pi_emi_tenure)
Staggered EMI: In this EMI ,EMI is calculated as Equal Principal EMI. But client give Principal according to their suitability and interest are same as paid by client.
Function:(In Oracle)
- CREATE OR REPLACE FUNCTION Galaxy_Cc_Calculator
- (
- pi_amount number DEFAULT NULL,
- pi_roi number DEFAULT NULL,
- pi_emi_tenure number DEFAULT NULL,
- pi_payment_type VARCHAR2 DEFAULT NULL,
- pi_emi_type VARCHAR2 DEFAULT NULL,
- pi_calc_type VARCHAR2 DEFAULT NULL,
- pi_find VARCHAR2 DEFAULT NULL
- )
- RETURN number
- AS
- l_rate number;
- l_principal number;
- l_returnString VARCHAR2(500);
- BEGIN
- l_principal:=0;
-
- select
- CASE pi_payment_type
- WHEN 'Half Yearly' THEN 2
- WHEN 'Quaterly' THEN 3
- ELSE 12 END into l_rate from dual;
-
-
- IF(pi_calc_type='IRR' OR pi_calc_type='ROI' OR pi_calc_type='UPFRONT') THEN
-
- IF(pi_emi_type='FIXED') THEN
- select
- CASE pi_find
- WHEN 'EMI' THEN
-
- ROUND( ((pi_amount) * (pi_roi/(l_rate*100))) * (power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) / ((power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) -1))
- WHEN 'INTEREST' THEN
- ROUND((pi_amount) * (pi_roi/(l_rate*100)))
- WHEN 'PRINCIPAL' THEN
-
- ((ROUND( ((pi_amount) * (pi_roi/(l_rate*100))) * (power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) / ((power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) -1)))- (ROUND((pi_amount) * (pi_roi/(l_rate*100)))))
- WHEN 'EVALUATED PRINCIPAL' THEN
-
- ROUND((pi_amount)-(((ROUND( ((pi_amount) * (pi_roi/(l_rate*100))) * (power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) / ((power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) -1)))- (ROUND((pi_amount) * (pi_roi/(l_rate*100)))))))
- ELSE
- 0
- END
- into l_principal from dual;
- END IF;
-
-
- IF(pi_emi_type='EQUAL PRINCIPAL') THEN
- select
- CASE pi_find
- WHEN 'EMI' THEN
- ROUND((pi_amount)/pi_emi_tenure)
- WHEN 'INTEREST' THEN
- ROUND((pi_amount) * (pi_roi/(l_rate*100)))
- WHEN 'CASH FLOW' THEN
- ROUND(((pi_amount)/pi_emi_tenure)+ ((pi_amount) * (pi_roi/(l_rate*100))))
- WHEN 'EVALUATED PRINCIPAL' THEN
- ROUND((pi_amount)-((pi_amount)/pi_emi_tenure))
- ELSE
- 0
- END
- into l_principal from dual;
- END IF;
-
-
- IF(pi_emi_type='STAGGERED EMI') THEN
- select
- CASE pi_find
- WHEN 'EMI' THEN
- ROUND((pi_amount) * (pi_roi/(l_rate*100)) +((pi_amount)/pi_emi_tenure) )
- WHEN 'INTEREST' THEN
- ROUND((pi_amount) * (pi_roi/(l_rate*100)))
- WHEN 'CASH FLOW' THEN
- ROUND((pi_amount)/pi_emi_tenure)
- WHEN 'EVALUATED PRINCIPAL' THEN
- ROUND((pi_amount) - ((pi_amount)/pi_emi_tenure))
- ELSE
- 0
- END
- into l_principal from dual;
- END IF;
-
- END IF;
-
- RETURN l_principal;
- EXCEPTION
- WHEN OTHERS THEN
- l_returnString := 'EXCEPTION';
- RETURN l_returnString;
- END;
- /
- ***********
- Procedure
- ***********
- CREATE OR REPLACE PROCEDURE Galaxy_Cc_Get_Fixed_Emi
- (
- pi_userid IN VARCHAR2,
- pi_principal IN NUMBER,
- pi_interest IN NUMBER,
- pi_tenure IN NUMBER,
- pi_emitype IN VARCHAR2,
- pi_charges IN NUMBER,
- pi_repaymentmethod IN VARCHAR2,
- pi_calctype IN VARCHAR2,
- pi_IRR IN NUMBER DEFAULT 0,
- po_cursor OUT Plms_Package.ref_cursor,
- po_message OUT VARCHAR2,
- po_upfrontcharges OUT VARCHAR2
- )
- AS
- l_principal NUMBER :=0;
- l_interest NUMBER :=0;
- l_emi NUMBER :=0;
- l_amnt NUMBER :=0;
- l_Actprncpl NUMBER :=0;
- p_amount_array XIRR_T_AMOUNT_ARRAY;
- p_date_array Xirr_p_date_array;
- l_guess NUMBER := 0.0;
- l_xirr_value NUMBER := 0.0;
- l_nominal_months INT := 12;
- l_nominal_value NUMBER := 0.0;
- creation_table plms_Table80;
- recordcount NUMBER:=1;
- l_count NUMBER:=1;
- l_date VARCHAR2(50);
- l_outflow NUMBER;
- l_counter NUMBER:=1;
- l_roi NUMBER :=1;
- BEGIN
- creation_table := NEW plms_Table80();
- p_amount_array := XIRR_T_AMOUNT_ARRAY();
- p_date_array := Xirr_p_date_array();
- OPEN po_cursor FOR SELECT * FROM DUAL;
- po_message :='SUCCESS';
-
- IF pi_calctype ='UPFRONT' THEN
- IF(pi_charges=0) THEN
- l_outflow:=pi_principal*.1;
- p_amount_array.extend(pi_tenure+1);
- p_date_array.extend(pi_tenure+1);
- p_amount_array(l_count):= -l_outflow;
- p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
- l_count:=2;
- ELSE
- l_outflow:=pi_principal-pi_charges;
- p_amount_array.extend(pi_tenure+1);
- p_amount_array(l_count):= -l_outflow;
- p_date_array.extend(pi_tenure+1);
- p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
- l_count:=2;
- END IF ;
- l_nominal_months:=pi_tenure;
- END IF;
- SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'mon'),1),'dd-Mon-rrrr') INTO l_date FROM dual;
-
-
- IF (pi_calctype='IRR') THEN
-
- IF (pi_emitype='FIXED EMI') THEN
-
- FOR i IN 1 .. pi_tenure
- LOOP
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','EMI') INTO l_emi FROM dual ;
- IF i=1 THEN
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','PRINCIPAL') INTO l_principal FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
- ELSE
-
- SELECT Galaxy_Cc_Calculator(l_Actprncpl,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT (ROUND(l_emi - l_interest))INTO l_principal FROM dual;
-
- SELECT (ROUND(l_Actprncpl - (l_emi - l_interest) ))INTO l_Actprncpl FROM dual;
- IF i=pi_tenure THEN
- l_principal:=l_principal+l_Actprncpl;
- l_Actprncpl:=0;
- END IF;
- END IF;
- creation_table.EXTEND;
- creation_table(recordcount) := Plms_Object80( i, l_emi,l_interest,l_principal,l_Actprncpl,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL);
- recordcount:=recordcount +1;
- END LOOP;
-
- OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
- T1.Param1 "EMI",
- T1.Param2 "INTEREST",
- T1.Param3 "PRINCIPAL",
- T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
- END IF;
-
-
- IF (pi_emitype='EQUAL PRINCIPAL EMI') THEN
-
- FOR i IN 1 .. pi_tenure LOOP
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EMI') INTO l_emi FROM dual ;
- IF i=1 THEN
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','CASH FLOW') INTO l_principal FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
- ELSE
-
- SELECT Galaxy_Cc_Calculator(l_Actprncpl,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT (ROUND(l_emi + l_interest))INTO l_principal FROM dual;
-
- SELECT (ROUND(l_Actprncpl - (l_emi) ))INTO l_Actprncpl FROM dual;
- IF i=pi_tenure THEN
- l_emi:=l_emi - (ABS(l_Actprncpl));
- l_principal:=l_principal+l_Actprncpl;
- l_Actprncpl:=0;
- END IF;
- END IF;
- creation_table.EXTEND;
- creation_table(recordcount) := Plms_Object80(i, l_emi,l_interest,l_principal,l_Actprncpl,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL);
- recordcount:=recordcount +1;
- END LOOP;
-
- OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
- T1.Param1 "PRINCIPAL",
- T1.Param2 "INTEREST",
- T1.Param3 "EMI",
- T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
- END IF;
-
-
- IF (pi_emitype='STAGGERED EMI') THEN
-
- FOR i IN 1 .. pi_tenure LOOP
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','CASH FLOW') INTO l_principal FROM dual;
- IF i=1 THEN
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','EMI') INTO l_emi FROM dual ;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
- ELSE
-
- SELECT Galaxy_Cc_Calculator(l_Actprncpl,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT ROUND(l_principal + l_interest )INTO l_emi FROM dual;
-
- SELECT ROUND(l_Actprncpl-l_principal)INTO l_Actprncpl FROM dual;
- IF i=pi_tenure THEN
- l_principal:=l_principal - (ABS(l_Actprncpl));
- l_Actprncpl:=l_Actprncpl+l_principal;
- l_Actprncpl:=0;
- END IF;
- END IF;
- creation_table.EXTEND;
- creation_table(recordcount) := Plms_Object80( i, l_emi,l_interest,l_principal,l_Actprncpl,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL);
- recordcount:=recordcount +1;
- END LOOP;
-
- OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
- T1.Param1 "EMI",
- T1.Param2 "INTEREST",
- T1.Param3 "PRINCIPAL",
- T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
- END IF;
-
- select TO_CHAR(0) into po_upfrontcharges from dual;
- END IF;
-
-
- IF (pi_calctype='ROI') THEN
-
- IF (pi_emitype='FIXED EMI') THEN
-
- while(l_roi!= 0)
- LOOP
- p_amount_array := XIRR_T_AMOUNT_ARRAY();
- p_date_array := Xirr_p_date_array();
- l_count:=1;
-
- IF pi_calctype ='ROI' THEN
- IF(pi_charges=0) THEN
- l_outflow:=pi_principal;
- p_amount_array.extend(pi_tenure+1);
- p_date_array.extend(pi_tenure+1);
- p_amount_array(l_count):= -l_outflow;
- p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
- l_count:=2;
- ELSE
- l_outflow:=pi_principal-pi_charges;
- p_amount_array.extend(pi_tenure+1);
- p_amount_array(l_count):= -l_outflow;
- p_date_array.extend(pi_tenure+1);
- p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
- l_count:=2;
- END IF ;
- l_nominal_months:=pi_tenure;
- END IF;
- SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'mon'),1),'dd-Mon-rrrr') INTO l_date FROM dual;
-
-
- FOR i IN 1 .. pi_tenure
- LOOP
-
- SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'FIXED','IRR','EMI') INTO l_emi FROM dual ;
- IF i=1 THEN
-
- SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'FIXED','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'FIXED','IRR','PRINCIPAL') INTO l_principal FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'FIXED','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
- ELSE
-
- SELECT Galaxy_Cc_Calculator(l_Actprncpl,l_roi,pi_tenure,pi_repaymentmethod,'FIXED','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT (ROUND(l_emi - l_interest))INTO l_principal FROM dual;
-
- SELECT (ROUND(l_Actprncpl - (l_emi - l_interest) ))INTO l_Actprncpl FROM dual;
- IF i=pi_tenure THEN
- l_principal:=l_principal+l_Actprncpl;
- l_Actprncpl:=0;
- END IF;
- END IF;
- creation_table.EXTEND;
- creation_table(recordcount) := Plms_Object80( i, l_emi,l_interest,l_principal,l_Actprncpl,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL);
- recordcount:=recordcount +1;
-
- p_date_array(l_count):= TO_DATE(l_date,'dd-Mon-rrrr');
- p_amount_array(l_count):= l_emi;
- l_count:=l_count+1;
- SELECT ADD_MONTHS(TRUNC(TO_DATE(l_date,'dd-Mon-rrrr'),'mon'),1) INTO l_date FROM dual;
-
- END LOOP;
-
-
- l_guess:=0.1;
- SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
- SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
- IF (pi_IRR = l_nominal_value) THEN
-
- select TO_CHAR(l_roi) into po_upfrontcharges from dual;
- OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
- T1.Param1 "EMI",
- T1.Param2 "INTEREST",
- T1.Param3 "PRINCIPAL",
- T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
- GOTO gotoEndLoop;
- ELSE
- FOR i IN 1 .. recordcount
- LOOP
- creation_table.DELETE(i);
- END LOOP;
- END IF;
- l_roi:=l_roi+1;
-
- END LOOP;
-
- END IF;
-
-
- IF (pi_emitype='EQUAL PRINCIPAL EMI') THEN
-
- WHILE(l_roi!= 0)
- LOOP
- p_amount_array := XIRR_T_AMOUNT_ARRAY();
- p_date_array := Xirr_p_date_array();
- l_count:=1;
-
- IF pi_calctype ='ROI' THEN
- IF(pi_charges=0) THEN
- l_outflow:=pi_principal;
- p_amount_array.extend(pi_tenure+1);
- p_date_array.extend(pi_tenure+1);
- p_amount_array(l_count):= -l_outflow;
- p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
- l_count:=2;
- ELSE
- l_outflow:=pi_principal-pi_charges;
- p_amount_array.extend(pi_tenure+1);
- p_amount_array(l_count):= -l_outflow;
- p_date_array.extend(pi_tenure+1);
- p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
- l_count:=2;
- END IF ;
- l_nominal_months:=pi_tenure;
- END IF;
- SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'mon'),1),'dd-Mon-rrrr') INTO l_date FROM dual;
-
-
-
- SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EMI') INTO l_emi FROM dual ;
- FOR i IN 1 .. pi_tenure LOOP
- IF i=1 THEN
-
- SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','CASH FLOW') INTO l_principal FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
- ELSE
-
- SELECT Galaxy_Cc_Calculator(l_Actprncpl,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT (ROUND(l_emi + l_interest))INTO l_principal FROM dual;
-
- SELECT (ROUND(l_Actprncpl - (l_emi) ))INTO l_Actprncpl FROM dual;
-
- IF i=pi_tenure THEN
- l_emi:=l_emi - (ABS(l_Actprncpl));
- l_principal:=l_principal+l_Actprncpl;
- l_Actprncpl:=0;
- END IF;
- END IF;
- creation_table.EXTEND;
- creation_table(recordcount) := Plms_Object80(i, l_emi,l_interest,l_principal,l_Actprncpl,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL);
- recordcount:=recordcount +1;
-
- p_date_array(l_count):= TO_DATE(l_date,'dd-Mon-rrrr');
- p_amount_array(l_count):= l_principal/*(EMI AMOUNT)*/;
- l_count:=l_count+1;
- SELECT ADD_MONTHS(TRUNC(TO_DATE(l_date,'dd-Mon-rrrr'),'mon'),1) INTO l_date FROM dual;
-
- END LOOP;
-
- l_guess:=0.1;
- SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
- SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
- IF (pi_IRR = l_nominal_value) THEN
- SELECT TO_CHAR(l_roi) INTO po_upfrontcharges FROM dual;
- OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
- T1.Param1 "PRINCIPAL",
- T1.Param2 "INTEREST",
- T1.Param3 "EMI",
- T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
- l_roi:=0;
- GOTO gotoEndLoop;
- ELSE
- FOR i IN 1 .. recordcount
- LOOP
- creation_table.DELETE(i);
- END LOOP;
- l_roi:=l_roi+1;
- END IF;
- END LOOP;
-
- END IF;
-
-
- IF (pi_emitype='STAGGERED EMI') THEN
-
- WHILE(l_roi!= 0)
- LOOP
- p_amount_array := XIRR_T_AMOUNT_ARRAY();
- p_date_array := Xirr_p_date_array();
- l_count:=1;
-
- IF pi_calctype ='ROI' THEN
- IF(pi_charges=0) THEN
- l_outflow:=pi_principal;
- p_amount_array.extend(pi_tenure+1);
- p_date_array.extend(pi_tenure+1);
- p_amount_array(l_count):= -l_outflow;
- p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
- l_count:=2;
- ELSE
- l_outflow:=pi_principal-pi_charges;
- p_amount_array.extend(pi_tenure+1);
- p_amount_array(l_count):= -l_outflow;
- p_date_array.extend(pi_tenure+1);
- p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
- l_count:=2;
- END IF ;
- l_nominal_months:=pi_tenure;
- END IF;
- SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'mon'),1),'dd-Mon-rrrr') INTO l_date FROM dual;
-
-
-
- SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EMI') INTO l_emi FROM dual ;
- FOR i IN 1 .. pi_tenure LOOP
- IF i=1 THEN
-
- SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','CASH FLOW') INTO l_principal FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
- ELSE
-
- SELECT Galaxy_Cc_Calculator(l_Actprncpl,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT (ROUND(l_emi + l_interest))INTO l_principal FROM dual;
-
- SELECT (ROUND(l_Actprncpl - (l_emi) ))INTO l_Actprncpl FROM dual;
-
- IF i=pi_tenure THEN
- l_principal:=l_principal - (ABS(l_Actprncpl));
- l_principal:=l_principal+l_Actprncpl;
- l_Actprncpl:=0;
- END IF;
- END IF;
- creation_table.EXTEND;
- creation_table(recordcount) := Plms_Object80(i, l_emi,l_interest,l_principal,l_Actprncpl,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL);
- recordcount:=recordcount +1;
-
- p_date_array(l_count):= TO_DATE(l_date,'dd-Mon-rrrr');
- p_amount_array(l_count):= l_principal/*(EMI AMOUNT)*/;
- l_count:=l_count+1;
- SELECT ADD_MONTHS(TRUNC(TO_DATE(l_date,'dd-Mon-rrrr'),'mon'),1) INTO l_date FROM dual;
-
- END LOOP;
-
-
- l_guess:=0.1;
- SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
- SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
- IF (pi_IRR = l_nominal_value) THEN
- SELECT TO_CHAR(l_roi) INTO po_upfrontcharges FROM dual;
- OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
- T1.Param1 "PRINCIPAL",
- T1.Param2 "INTEREST",
- T1.Param3 "EMI",
- T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
- l_roi:=0;
- GOTO gotoEndLoop;
- ELSE
- FOR i IN 1 .. recordcount
- LOOP
- creation_table.DELETE(i);
- END LOOP;
- l_roi:=l_roi+1;
- END IF;
-
- END LOOP;
-
- END IF;
-
- END IF;
-
-
- IF (pi_calctype='UPFRONT') THEN
-
- IF (pi_emitype='FIXED EMI') THEN
-
- FOR i IN 1 .. pi_tenure
- LOOP
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','EMI') INTO l_emi FROM dual ;
- IF i=1 THEN
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','PRINCIPAL') INTO l_principal FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
- ELSE
-
- SELECT Galaxy_Cc_Calculator(l_Actprncpl,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT (ROUND(l_emi - l_interest))INTO l_principal FROM dual;
-
- SELECT (ROUND(l_Actprncpl - (l_emi - l_interest) ))INTO l_Actprncpl FROM dual;
- IF i=pi_tenure THEN
- l_principal:=l_principal+l_Actprncpl;
- l_Actprncpl:=0;
- END IF;
- END IF;
- creation_table.EXTEND;
- creation_table(recordcount) := Plms_Object80( i, l_emi,l_interest,l_principal,l_Actprncpl,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL);
- recordcount:=recordcount +1;
-
- p_date_array(l_count):= TO_DATE(l_date,'dd-Mon-rrrr');
- p_amount_array(l_count):= l_emi;
- l_count:=l_count+1;
- SELECT ADD_MONTHS(TRUNC(TO_DATE(l_date,'dd-Mon-rrrr'),'mon'),1) INTO l_date FROM dual;
- END LOOP;
-
-
- l_guess:=0.1;
- SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
- SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
- l_outflow := 0;
- IF (pi_IRR=l_nominal_value) THEN
-
- po_message:='SUCCESS';
- ELSIF (pi_IRR > l_nominal_value) THEN
-
- l_outflow := l_outflow+pi_principal*.025;
- WHILE ROUND(pi_IRR,0)> ROUND(l_nominal_value,0)
- LOOP
- l_outflow := l_outflow+pi_principal*.025;
- p_amount_array(1):=-l_outflow;
- SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
- SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
- l_counter:=l_counter+1;
- IF l_counter = 100000 THEN
- GOTO gotoEndLoop;
- END IF;
- END LOOP;
- ELSIF (pi_IRR < l_nominal_value) THEN
-
- l_outflow:=0;
- l_outflow := l_outflow+(pi_principal-(pi_principal*.3));
- WHILE ROUND(pi_IRR,0)< ROUND(l_nominal_value,0)
- LOOP
- l_outflow := l_outflow+(pi_principal*.0005);
- p_amount_array(1):= l_outflow*(-1);
- l_xirr_value:=0;
- SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
- SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
- l_counter:=l_counter+1;
- IF l_counter = 100000 THEN
- GOTO gotoEndLoop;
- END IF;
- END LOOP;
- END IF;
-
-
- OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
- T1.Param1 "EMI",
- T1.Param2 "INTEREST",
- T1.Param3 "PRINCIPAL",
- T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
- select TO_CHAR(pi_principal-l_outflow) into po_upfrontcharges from dual;
- END IF;
-
-
- IF (pi_emitype='EQUAL PRINCIPAL EMI') THEN
-
- FOR i IN 1 .. pi_tenure LOOP
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EMI') INTO l_emi FROM dual ;
- IF i=1 THEN
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','CASH FLOW') INTO l_principal FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
- ELSE
-
- SELECT Galaxy_Cc_Calculator(l_Actprncpl,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT (ROUND(l_emi + l_interest))INTO l_principal FROM dual;
-
- SELECT (ROUND(l_Actprncpl - (l_emi) ))INTO l_Actprncpl FROM dual;
- IF i=pi_tenure THEN
- l_emi:=l_emi - (ABS(l_Actprncpl));
- l_principal:=l_principal+l_Actprncpl;
- l_Actprncpl:=0;
- END IF;
- END IF;
- creation_table.EXTEND;
- creation_table(recordcount) := Plms_Object80(i, l_emi,l_interest,l_principal,l_Actprncpl,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL);
- recordcount:=recordcount +1;
-
- p_date_array(l_count):= TO_DATE(l_date,'dd-Mon-rrrr');
- p_amount_array(l_count):= l_emi;
- l_count:=l_count+1;
- SELECT ADD_MONTHS(TRUNC(TO_DATE(l_date,'dd-Mon-rrrr'),'mon'),1) INTO l_date FROM dual;
- END LOOP;
-
-
- l_guess:=0.1;
- SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
- SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
- l_outflow := 0;
- IF (pi_IRR=l_nominal_value) THEN
-
- po_message:='SUCCESS';
- ELSIF (pi_IRR > l_nominal_value) THEN
-
- l_outflow := l_outflow+pi_principal*.025;
- WHILE ROUND(pi_IRR,0)>=ROUND(l_nominal_value,0)
- LOOP
- l_outflow := l_outflow+pi_principal*.025;
- p_amount_array(1):=-l_outflow;
- SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
- SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
- l_counter:=l_counter+1;
- IF l_counter = 100000 THEN
- GOTO gotoEndLoop;
- END IF;
- END LOOP;
- ELSIF (pi_IRR < l_nominal_value) THEN
-
- l_outflow:=0;
- l_outflow := l_outflow+(pi_principal-(pi_principal*.3));
- WHILE ROUND(pi_IRR,0)<=ROUND(l_nominal_value,0)
- LOOP
- l_outflow := l_outflow+(pi_principal*.0005);
- p_amount_array(1):= l_outflow*(-1);
- l_xirr_value:=0;
- SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
- SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
- l_counter:=l_counter+1;
- IF l_counter = 100000 THEN
- GOTO gotoEndLoop;
- END IF;
- END LOOP;
- END IF;
-
-
- OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
- T1.Param1 "PRINCIPAL",
- T1.Param2 "INTEREST",
- T1.Param3 "EMI",
- T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
- select TO_CHAR(pi_principal-l_outflow) into po_upfrontcharges from dual;
- END IF;
-
-
- IF (pi_emitype='STAGGERED EMI') THEN
-
- FOR i IN 1 .. pi_tenure LOOP
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','CASH FLOW') INTO l_principal FROM dual;
- IF i=1 THEN
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','EMI') INTO l_emi FROM dual ;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
- ELSE
- SELECT Galaxy_Cc_Calculator(l_Actprncpl,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','INTEREST') INTO l_interest FROM dual;
-
- SELECT ROUND(l_principal + l_interest )INTO l_emi FROM dual;
-
- SELECT ROUND(l_Actprncpl-l_principal)INTO l_Actprncpl FROM dual;
- IF i=pi_tenure THEN
- l_principal:=l_principal - (ABS(l_Actprncpl));
- l_Actprncpl:=l_Actprncpl+l_principal;
- l_Actprncpl:=0;
- END IF;
- END IF;
- creation_table.EXTEND;
- creation_table(recordcount) := Plms_Object80( i, l_emi,l_interest,l_principal,l_Actprncpl,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
- NULL,NULL,NULL,NULL);
- recordcount:=recordcount +1;
-
- p_date_array(l_count):= TO_DATE(l_date,'dd-Mon-rrrr');
- p_amount_array(l_count):= l_emi;
- l_count:=l_count+1;
- SELECT ADD_MONTHS(TRUNC(TO_DATE(l_date,'dd-Mon-rrrr'),'mon'),1) INTO l_date FROM dual;
- END LOOP;
-
-
- l_guess:=0.1;
- SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
- SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
- l_outflow := 0;
- IF (pi_IRR=l_nominal_value) THEN
-
- po_message:='SUCCESS';
- ELSIF (pi_IRR > l_nominal_value) THEN
-
- l_outflow := l_outflow+pi_principal*.025;
- WHILE ROUND(pi_IRR,0)>=ROUND(l_nominal_value,0)
- LOOP
- l_outflow := l_outflow+pi_principal*.025;
- p_amount_array(1):=-l_outflow;
- SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
- SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
- l_counter:=l_counter+1;
- IF l_counter = 100000 THEN
- GOTO gotoEndLoop;
- END IF;
- END LOOP;
- ELSIF (pi_IRR < l_nominal_value) THEN
-
- l_outflow:=0;
- l_outflow := l_outflow+(pi_principal-(pi_principal*.3));
- WHILE ROUND(pi_IRR,0)<=ROUND(l_nominal_value,0)
- LOOP
- l_outflow := l_outflow+(pi_principal*.0005);
- p_amount_array(1):= l_outflow*(-1);
- l_xirr_value:=0;
- SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
- SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
- l_counter:=l_counter+1;
- IF l_counter = 100000 THEN
- GOTO gotoEndLoop;
- END IF;
- END LOOP;
- END IF;
-
-
- OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
- T1.Param1 "EMI",
- T1.Param2 "INTEREST",
- T1.Param3 "PRINCIPAL",
- T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
- select TO_CHAR(pi_principal-l_outflow) into po_upfrontcharges from dual;
- END IF;
-
- END IF;
-
-
- <<gotoEndLoop>>
- po_message := 'SUCCESS';
- EXCEPTION
- WHEN OTHERS THEN
- po_message := 'Error' || SUBSTR(SQLERRM, 1, 100);
- ROLLBACK;
- Plms_Errorslog('Galaxy_Cc_Get_Fixed_Emi',po_message,pi_userid,NULL,NULL,po_message );
- IF(po_message <>'SUCCESS') THEN
- po_message :='Error in ErrorLog Proc and ' || po_message;
- ROLLBACK;
- RETURN;
- END IF;
- END;
And call the function in Procedure.Get all the detail in well way.