Created
July 30, 2021 04:25
-
-
Save si294r/e42f9817f3404ef874a393834dfa161d to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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$ | |
; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment