Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create or replace 
function monthly_loan_payment 
       ( amount  numeric
       , apr     numeric
       , term    numeric
       )
  returns numeric
  language sql 
  immutable strict    
/*  Given a loan amount, the Annual Percent (Interest) Rate term (in years) 
 *  compute the monthly payment to service the loan. 
 *  Note. Total payment calculates correctly, but due to the exact terms of loan
 *        and date of payment receipt adjustments for end of loan payment may be 
 *        required.  
 */ 
as $$   
  with monthly (mrate) as (values ( (apr/100.00) / 12.00 ) )
  select round((amount * mrate)  /(1.0 - (1.0/ ((1.0 +mrate)^( term * 12.00)) ))::numeric,2) 
     from monthly;
$$;  
-- v12 using generated column
create table loan
    ( loanid              integer generated always as identity
    , annualinterestrate  integer not null
    , numberofyears       integer not null
    , loanamount          numeric not null
    , monthlypayment      numeric generated always as 
                                  ( monthly_loan_payment
                                        (amount => loanamount
                                        ,apr    => annualinterestrate::numeric
                                        ,term   => numberofyears::numeric
                                        )
                                   ) stored
    );                                 

insert into loan(loanamount ,annualinterestrate, numberofyears )
     values (5000,10,3), (500,10,1) ;
2 rows affected

select * from loan;


loanid
annualinterestrate
numberofyears
loanamount
monthlypayment


1
10
3
5000
161.34

2
10
1
500
43.96


-- prior to V12 (use trigger)
create table loan_p12
    ( loanid              serial
    , annualinterestrate  integer not null
    , numberofyears       integer not null
    , loanamount          numeric not null
    , monthlypayment      numeric not null
    );                                 

create or replace 
function get_p12_monthly_loan_payment_biur() 
  returns  trigger
  language plpgsql  
as $$   
begin
  new.monthlypayment = monthly_loan_payment
                        (amount => new.loanamount
                        ,apr    => new.annualinterestrate::numeric
                        ,term   => new.numberofyears::numeric
                        ) ;
  return new;
end;
$$;
 
create trigger monthly_loan_payment_p12_biur 
before insert or update  
    on loan_p12
for each row 
execute procedure  get_p12_monthly_loan_payment_biur(); 

insert into loan_p12(loanamount ,annualinterestrate, numberofyears )
     values (5000,10,3), (500,10,1) ;
2 rows affected

select * from loan_p12;


loanid
annualinterestrate
numberofyears
loanamount
monthlypayment


1
10
3
5000
161.34

2
10
1
500
43.96