Oracle SQL - Model Clause for Calculating Loan Future Balance-CodePudding

I am trying to calculate the future balances of a loan. I don't have write privileges to the Oracle database I am working on so I can't use create table. I will have access to account information that includes the account ID, current balance, interest rate, payment amount, payment frequency and remaining amortization. Based on this information, I'd like to calculate what the balance of the loan will be by a certain payment. The following is what I have working right now:

WITH mortgage_details (account_id, start_date, balance, annual_interest, pmt, pmt_freq, remain_amort) AS (
  SELECT 1, TRUNC(SYSDATE, 'MM'), 34798, 2, 670, 'BW', 5 FROM DUAL UNION ALL
  SELECT 2, TRUNC(SYSDATE, 'MM'), 50000, 10, 660, 'M', 6 FROM DUAL
),
mtg_final as
(
    select mtg.*,
    case when pmt_freq = 'BW' then 26
         when pmt_freq = 'M' then 12
    end as pmt_freq_updated
    from mortgage_details mtg
)
select * from mtg_final
where account_id = 1  
MODEL   
  DIMENSION BY (rownum rownumber)  
  -- Change Amount, Payment and Interest Rate here!  
  MEASURES (balance, 0 b, 0 c, pmt d, annual_interest e)  
  RULES SEQUENTIAL ORDER ITERATE (500) UNTIL (balance[ITERATION_NUMBER   1] <= 0) (  
    b[ITERATION_NUMBER   1] = balance[cv(rownumber)] * e[cv(rownumber)] / 1200,   
    d[ITERATION_NUMBER   1] = least(d[cv(rownumber)], balance[cv(rownumber)]   b[cv(rownumber)]),  
    c[ITERATION_NUMBER   1] = d[cv(rownumber)] - b[cv(rownumber)],  
    balance[ITERATION_NUMBER   2] = balance[cv(rownumber)-1] - c[cv(rownumber) - 1],  
    e[ITERATION_NUMBER   2] = e[cv(rownumber)-1],  
    d[ITERATION_NUMBER   2] = d[cv(rownumber)-1]  
  )  
order by rownumber

However, the issue is that I am not using the pmt_freq and remain_amort fields from the table and have instead hardcoded a 1200. I would like to replace this 1200 with something like pmt_freq* remain_amort but when I tried this I got an error "column not allowed here". Here is what I tried when I got that error:

WITH mortgage_details (account_id, start_date, balance, annual_interest, pmt, pmt_freq, remain_amort) AS (
  SELECT 1, TRUNC(SYSDATE, 'MM'), 34798, 2, 670, 'BW', 5 FROM DUAL UNION ALL
  SELECT 2, TRUNC(SYSDATE, 'MM'), 50000, 10, 660, 'M', 6 FROM DUAL
),
mtg_final as
(
    select mtg.*,
    case when pmt_freq = 'BW' then 26
         when pmt_freq = 'M' then 12
    end as pmt_freq_updated
    from mortgage_details mtg
)
select * from mtg_final
where account_id = 1  
MODEL   
  DIMENSION BY (rownum rownumber)  
  -- Change Amount, Payment and Interest Rate here!  
  MEASURES (balance, 0 b, 0 c, pmt d, annual_interest e)  
  RULES SEQUENTIAL ORDER ITERATE (500) UNTIL (balance[ITERATION_NUMBER   1] <= 0) (  
    b[ITERATION_NUMBER   1] = balance[cv(rownumber)] * e[cv(rownumber)] / (100*remain_amort*pmt_freq_updated),  
    d[ITERATION_NUMBER   1] = least(d[cv(rownumber)], balance[cv(rownumber)]   b[cv(rownumber)]),  
    c[ITERATION_NUMBER   1] = d[cv(rownumber)] - b[cv(rownumber)],  
    balance[ITERATION_NUMBER   2] = balance[cv(rownumber)-1] - c[cv(rownumber) - 1],  
    e[ITERATION_NUMBER   2] = e[cv(rownumber)-1],  
    d[ITERATION_NUMBER   2] = d[cv(rownumber)-1]  
  )  
order by rownumber

CodePudding user response：

Without reviewing your whole approach, one way to do this that is very close to what you already have is to add pmt_freq and remain_amort to a PARTITION clause in your MODEL.

(You'll probably want to PARTITION BY account_id anyway, if you ever run this query for multiple accounts at once).

Here is what that would look like:

WITH mortgage_details (account_id, start_date, balance, annual_interest, pmt, pmt_freq, remain_amort) AS (
  SELECT 1, TRUNC(SYSDATE, 'MM'), 34798, 2, 670, 'BW', 5 FROM DUAL UNION ALL
  SELECT 2, TRUNC(SYSDATE, 'MM'), 50000, 10, 660, 'M', 6 FROM DUAL
)
select *
from mortgage_details
where account_id = 1  
MODEL   
  -- Add the partition to keep results in your model separated by account_id
  -- Since account_id uniquely identifies pmt_freq and remain_amort, we can
  -- safely put them here too
  PARTITION BY (account_id, pmt_freq, remain_amort)
  DIMENSION BY (rownum rownumber)  
  -- Change Amount, Payment and Interest Rate here!  
  MEASURES (balance, 0 b, 0 c, pmt d, annual_interest e)  
  RULES SEQUENTIAL ORDER ITERATE (500) UNTIL (balance[ITERATION_NUMBER   1] <= 0) (  
    b[ITERATION_NUMBER   1] = balance[cv(rownumber)] * e[cv(rownumber)] / (100 * DECODE(cv(pmt_freq),'BW',26,'M',12,0)*cv(remain_amort)),   
    d[ITERATION_NUMBER   1] = least(d[cv(rownumber)], balance[cv(rownumber)]   b[cv(rownumber)]),  
    c[ITERATION_NUMBER   1] = d[cv(rownumber)] - b[cv(rownumber)],  
    balance[ITERATION_NUMBER   2] = balance[cv(rownumber)-1] - c[cv(rownumber) - 1],  
    e[ITERATION_NUMBER   2] = e[cv(rownumber)-1],  
    d[ITERATION_NUMBER   2] = d[cv(rownumber)-1]  
  )  
order by rownumber

CodePudding user response：

You can add pmt_freq_updated and remain_amort to the MEASURES clause and then, if you need to, refer to them using pmt_freq_updated[1] and remain_amort[1] in the rules (since there is no need to propagate the static values through all the iterations of the model when you can just refer to them in the first row).

It also helps if you use meaningful names for your columns.

WITH mortgage_details (
  account_id,
  start_date,
  balance,
  annual_interest,
  pmt,
  pmt_freq,
  remain_amort
) AS (
  SELECT 1, TRUNC(SYSDATE, 'MM'), 34798, 2, 670, 'BW', 5 FROM DUAL UNION ALL
  SELECT 2, TRUNC(SYSDATE, 'MM'), 50000, 10, 660, 'M', 6 FROM DUAL
),
mtg_final as
(
  select mtg.*,
         case
         when pmt_freq = 'BW' then 26
         when pmt_freq = 'M' then 12
         end as pmt_freq_updated
  from   mortgage_details mtg
)
SELECT account_id,
       key,
       ROUND(balance, 2) AS balance,
       dt,
       ROUND(interest_amt, 2) AS interest_amt,
       payment,
       pmt_freq_updated,
       remain_amort
FROM   mtg_final
--where account_id = 1  
MODEL
  PARTITION BY (account_id)
  DIMENSION BY (1 AS key)
  MEASURES (
    balance,
    start_date dt,
    pmt_freq,
    pmt,
    1   annual_interest/100 AS annual_interest,
    0 AS interest_amt,
    0 AS payment,
    pmt_freq_updated,
    remain_amort
  )  
  RULES SEQUENTIAL ORDER
    ITERATE (100) UNTIL (balance[ITERATION_NUMBER] <= 0)
  (
    dt[ITERATION_NUMBER 2] = dt[ITERATION_NUMBER 1]   INTERVAL '1' DAY,
    annual_interest[key>1] = annual_interest[1],
    interest_amt[key]      = balance[cv(key)]
                             * (
                               POWER(
                                 annual_interest[1],
                                 1/(ADD_MONTHS(TRUNC(dt[cv(key)], 'YYYY'), 12)
                                   - TRUNC(dt[cv(key)], 'YYYY'))
                               ) - 1
                             ),
    payment[key]
      = CASE
        WHEN (pmt_freq[1] = 'M'  AND  dt[cv(key)] = LAST_DAY(dt[cv(key)]))
        OR   (pmt_freq[1] = 'BW' AND  MOD(cv(key), 14) = 0)
        OR   (pmt_freq[1] = 'W'  AND  MOD(cv(key), 7) = 0)
        THEN pmt[1]
        END,
    balance[key>1]         = balance[cv(key)-1]
                               interest_amt[cv(key) - 1]
                             - COALESCE(payment[cv(key)], 0)
     
  )  
order by account_id, dt

db<>fiddle here