financial_plsql.sql

CREATE OR REPLACE FUNCTION get_financial_pmt(p_rate numeric, p_periods integer, p_present numeric, p_future numeric, p_type integer)
 RETURNS numeric
 LANGUAGE plpgsql
AS $function$
    DECLARE
		v_result numeric = 0;
		v_term numeric = 0;
    BEGIN   
	    p_type = COALESCE(p_type, 0);
		p_future = COALESCE(p_future, 0);

		IF p_rate=0 THEN
			v_result = (p_present+p_future)/p_periods;
		ELSE
			v_term = POWER(1+p_rate, p_periods);
			
			IF p_type=1 THEN
				v_result = (p_future*p_rate / (v_term-1) + p_present*p_rate / (1-1/v_term)) / (1+p_rate);
			
			ELSE
				v_result = p_future*p_rate/(v_term-1)+p_present*p_rate / (1-1/v_term);
			END IF;

		END IF; 

        RETURN -v_result;  
    END;
$function$
;

CREATE OR REPLACE FUNCTION get_financial_fv(p_rate numeric, p_periods integer, p_payment numeric, p_value numeric, p_type integer)
 RETURNS numeric
 LANGUAGE plpgsql
AS $function$
    declare 
    	v_result numeric = 0;
	    v_term numeric = 0;
    BEGIN   
	    p_type = COALESCE(p_type,0);
		p_value = COALESCE(p_value,0);

		IF p_rate=0 THEN
			v_result = (p_value+p_payment)*p_periods;
		ELSE
			v_term = POWER(1+p_rate,p_periods);
			
			IF p_type=1 THEN
				v_result = p_value*v_term+p_payment*(1+p_rate)*(v_term-1.0)/p_rate;
			ELSE
				v_result = p_value*v_term+p_payment*(v_term-1)/p_rate;	
			END IF;
		END IF; 

        RETURN -v_result;  
    END;
$function$
;

CREATE OR REPLACE FUNCTION get_financial_ipmt(p_rate numeric, p_period integer, p_periods integer, p_present numeric, p_future numeric, p_type integer)
 RETURNS numeric
 LANGUAGE plpgsql
AS $function$
    declare
    	v_payment numeric;
	    v_interest numeric = 0.0;
    BEGIN   
	    p_type = COALESCE(p_type,0);
		v_payment = get_financial_pmt(p_rate,p_periods,p_present,p_future,p_type);
		
		IF p_period=1 THEN
			IF p_type=1 THEN
				v_interest = 0;
			ELSE
				v_interest = -p_present;
			END IF; 
		ELSE
			IF p_type=1 THEN
				v_interest = get_financial_fv(p_rate,p_period-2,v_payment,p_present,1)-v_payment;
			ELSE 
				v_interest = get_financial_fv(p_rate,p_period-1,v_payment,p_present,0);
			END IF; 
		END IF; 
		
        RETURN v_interest * p_rate;
    END;
$function$
;

CREATE OR REPLACE FUNCTION get_financial_ppmt(p_rate numeric, p_period integer, p_periods integer, p_present numeric, p_future numeric, p_type integer)
 RETURNS numeric
 LANGUAGE plpgsql
AS $function$
    BEGIN   
        RETURN get_financial_pmt(p_rate,p_periods,p_present,p_future,p_type) - get_financial_ipmt(p_rate,p_period,p_periods,p_present,p_future,p_type);
    END;
$function$
;