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.
Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.
Wednesday, June 18, 2008
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.
way better than mine! you've used MODELing!
ReplyDeletei have yet to attain that level of "nerdiness." congratulations!
Wow that's great. congrats for your good work.
ReplyDeleteHope it will be helpful in my future.
---------
Cheap loans
The post, "SQL for Loan Payment" is very nice........
ReplyDelete