Mortgage repayments calculator in Oracle SQL - power of the MODEL clause

The post was inspired by late-night discussions about the sense of using mortgages. In the first approach, it was supposed to be a PL/SQL package, but I decided to make my life easier and wrote a query displaying the loan repayment plan for fixed and decreasing installments.

The query can be easily parameterized (parameters are dark green) by changing:

• loan amount 
• repayment period 
• annual interest rate 

The following assumptions were made: 

• the interest rate during loan repayment period is fixed
•  installments are payable in advance from the current day every month 
• we assume that the interest rate is the same within each month 

The query below, with a bit of imagination, can be expanded in an interesting way, e.g. by assuming different interest rates in subsequent years.

SELECT d + 1 AS "Repayments number",
          REPAYMENT_DATE AS "Repayments date",        
          ROUND (DECL_REPAYMENTS_DEBT_PART, 2) AS "Declining repayments debt part ",
          ROUND (DECL_REPAYMENTS_INTEREST_PART, 2) AS "Declining repayments interest part",
          ROUND (DECL_REPAYMENTS_DEBT_PART + DECL_REPAYMENTS_INTEREST_PART, 2) AS "Declining repayment amount",
          ROUND (DECL_REPAYMENTS_BALANCE, 2) AS "Declining repayment balance",
          ROUND (FIXED_REPAYMENTS_DEBT_PART, 2) AS "Fixed payments debt part",
          ROUND (FIXED_REPAYMENTS_INTEREST_PART, 2) AS "Fixed repayments interest part",
          ROUND (FIXED_REPAYMENTS_AMOUNT, 2) AS "Fixed repayments amount",
          ROUND (FIXED_REPAYMENTS_BALANCE, 2) AS "Fixed repayments balance"
     FROM (SELECT 1 FROM DUAL)
   MODEL
      DIMENSION BY (0 d)
      MEASURES (600000 AMOUNT_OF_CREDIT, 6 BANK_RATE, 240 NUMBER_REPAYMENTS,
      TRUNC (SYSDATE) REPAYMENT_DATE,
             0 DECL_REPAYMENTS_BALANCE,
             0 DECL_REPAYMENTS_DEBT_PART,
             0 DECL_REPAYMENTS_INTEREST_PART,            
             0 FIXED_REPAYMENTS_AMOUNT,
             0 FIXED_REPAYMENTS_DEBT_PART,
             0 FIXED_REPAYMENTS_INTEREST_PART,
             0 FIXED_REPAYMENTS_BALANCE
             )
      RULES
         ITERATE (10000) UNTIL (ITERATION_NUMBER = NUMBER_REPAYMENTS[0] -1)
         (              
         NUMBER_REPAYMENTS [ITERATION_NUMBER ] =  
               NVL (NUMBER_REPAYMENTS[ITERATION_NUMBER - 1],                                     NUMBER_REPAYMENTS[0]),
          BANK_RATE [ITERATION_NUMBER ] =
               NVL (BANK_RATE[ITERATION_NUMBER - 1], BANK_RATE[0]/1200),
         DECL_REPAYMENTS_DEBT_PART [ITERATION_NUMBER ] =
               AMOUNT_OF_CREDIT[0] / NUMBER_REPAYMENTS[0],
         REPAYMENT_DATE [ITERATION_NUMBER ] =
                ADD_MONTHS (REPAYMENT_DATE[0], ITERATION_NUMBER ),
         DECL_REPAYMENTS_BALANCE [ITERATION_NUMBER ] =
                 NVL (DECL_REPAYMENTS_BALANCE[ITERATION_NUMBER - 1],
                      AMOUNT_OF_CREDIT[0])
               - NVL (DECL_REPAYMENTS_DEBT_PART[ITERATION_NUMBER ], 0),
         DECL_REPAYMENTS_INTEREST_PART [ITERATION_NUMBER ] =
                 DECL_REPAYMENTS_BALANCE[ITERATION_NUMBER ]
               * BANK_RATE[ITERATION_NUMBER ]  ,                                       
         FIXED_REPAYMENTS_DEBT_PART [ITERATION_NUMBER ]              
         = AMOUNT_OF_CREDIT[0] *BANK_RATE [ITERATION_NUMBER ] *POWER( 1+BANK_RATE [ITERATION_NUMBER ], ITERATION_NUMBER )/
          ( POWER( 1+BANK_RATE [ITERATION_NUMBER ],          NUMBER_REPAYMENTS[ITERATION_NUMBER ]) -1  ),
           FIXED_REPAYMENTS_BALANCE [ITERATION_NUMBER ] =
                 NVL (FIXED_REPAYMENTS_BALANCE[ITERATION_NUMBER - 1],
                      AMOUNT_OF_CREDIT[0])
               - FIXED_REPAYMENTS_DEBT_PART[ITERATION_NUMBER ],
           FIXED_REPAYMENTS_INTEREST_PART [ITERATION_NUMBER ] =
                 FIXED_REPAYMENTS_BALANCE[ITERATION_NUMBER ]
               * BANK_RATE[ITERATION_NUMBER ],
           FIXED_REPAYMENTS_AMOUNT[ITERATION_NUMBER ] = FIXED_REPAYMENTS_DEBT_PART[ITERATION_NUMBER ] + FIXED_REPAYMENTS_INTEREST_PART[ITERATION_NUMBER ]       
          )