SQL for Loan Payment

Dear all,

Inspired by "SQL for Buying a New Car", I thought of converting a very old excel sheet of mine into an SQL query. This excel sheet lists equated monthly installment (EMI), principal amount and the interest amount for duration of loan payment. I feed Rate of Interest, Loan Amount and Period of Loan (in years) to the excel sheet. This was very convenient for me to analyze what part of my EMI is going towards interest payment and what portion goes as principal repayment. It is extremely important to understand what goes for interest and what goes for principal repayment when you are planning for a loan.

Output from Excel sheet:

This can easily be converted into an SQL query using MODEL clause introduced in Oracle 10g.

The output of this query includes a summary of total interest to be paid, principal to be paid and the net payment to be made. Ofcourse, we need to ignore the Summary on "Remaining" column as this is meaningless.

SQL> set line 10000
SQL> set pages 500
SQL> set verify off
SQL>
SQL> column interest  format 999,999,999.99
SQL> column principal format 999,999,999.99
SQL> column remaining format 999,999,999.99
SQL>
SQL> define roi = 10          -- Rate of Interest
SQL> define years = 2         -- Payment Period
SQL> define amt = 100000      -- Loan Amount
SQL> define period = 12       -- Mode of payment.
SQL>                          --     Monthly=12, Bi-monthly=48, Quarterly=4, Half-yearly=2, Yearly=1, Adhoc=n
SQL>
SQL>
SQL> Select inst_no,
  2         sum(new_emi) emi,
  3         sum(new_interest) interest,
  4         sum(principal) principal,
  5         sum(remaining) remaining
  6    From ( Select inst_no + 1 inst_no,
  7                  new_emi,
  8                  interest,
  9                  decode( inst_no + 1, &period*&years, interest - remaining, interest)  new_interest,
 10                  decode( inst_no + 1, &period*&years, principal + remaining, principal)  principal,
 11                  decode( inst_no + 1, &period*&years, 0, remaining) remaining
 12             From  (select  rownum inst_no, roi, yrs, amt, period,
 13                            round( (roi * amt/(period * 100)) /
 14                                   (1 - power((1 + roi/(period * 100)), -yrs * period)), 2) emi
 15                      From (select &roi roi, &years yrs, &amt amt, &period period
 16                              From Dual) )
 17    MODEL
 18    DIMENSION BY ( inst_no )
 19    MEASURES     ( roi, yrs, amt, emi , period, 0 as new_emi, 0 as interest, 0 as principal, 0 as remaining)
 20    RULES
 21      UPSERT
 22      ITERATE (1000)
 23      (
 24        new_emi[ITERATION_NUMBER] = emi[1],
 25        interest[ITERATION_NUMBER]  = round(&roi/(period[1] * 100) *
 26                                      nvl(remaining[ITERATION_NUMBER-1], amt[1]), 2),
 27        principal[ITERATION_NUMBER] = emi[1] - interest[cv()],
 28        remaining[ITERATION_NUMBER] = nvl(remaining[ITERATION_NUMBER-1], amt[1]) - principal[cv()]))
 29  Where interest > 0
 30  Group by rollup( inst_no)
 31  order by  inst_no;

   INST_NO        EMI        INTEREST       PRINCIPAL       REMAINING
---------- ---------- --------------- --------------- ---------------
         1    4614.49          833.33        3,781.16       96,218.84
         2    4614.49          801.82        3,812.67       92,406.17
         3    4614.49          770.05        3,844.44       88,561.73
         4    4614.49          738.01        3,876.48       84,685.25
         5    4614.49          705.71        3,908.78       80,776.47
         6    4614.49          673.14        3,941.35       76,835.12
         7    4614.49          640.29        3,974.20       72,860.92
         8    4614.49          607.17        4,007.32       68,853.60
         9    4614.49          573.78        4,040.71       64,812.89
        10    4614.49          540.11        4,074.38       60,738.51
        11    4614.49          506.15        4,108.34       56,630.17
        12    4614.49          471.92        4,142.57       52,487.60
        13    4614.49          437.40        4,177.09       48,310.51
        14    4614.49          402.59        4,211.90       44,098.61
        15    4614.49          367.49        4,247.00       39,851.61
        16    4614.49          332.10        4,282.39       35,569.22
        17    4614.49          296.41        4,318.08       31,251.14
        18    4614.49          260.43        4,354.06       26,897.08
        19    4614.49          224.14        4,390.35       22,506.73
        20    4614.49          187.56        4,426.93       18,079.80
        21    4614.49          150.67        4,463.82       13,615.98
        22    4614.49          113.47        4,501.02        9,114.96
        23    4614.49           75.96        4,538.53        4,576.43
        24    4614.49           38.06        4,576.43             .00
            110747.76       10,747.76      100,000.00    1,189,739.34

25 rows selected.

SQL>

Results of this query can be verified here.

References:

1. Loan Calculator.

2. How to calculate EMI.

Happy reading.