## Tuesday, October 14, 2008

### Mortgage calculator using SQL Model clause

Recently we had local Latvian Oracle day conference. This year it was quite big event with 5 parallel sessions and more than 400 participants. I also was one of the presenters telling about analytic functions and SQL Model clause. During the process of understanding deeper and better Oracle SQL Model clause I've tried make also some a bit complex examples myself. Nowadays the actual problem is mortgage and of course the possibility to pay monthly payments (fortunately not for me :) ) on the background of world wide financial crisis.
So here is fully functional mortgage calculator using SQL Model clause. There are two variants:
I've used this site to get formula for fixed monthly payment required to fully amortize a loan over a term of fixed months at a fixed monthly interest rate.
OK and now the scripts.
`set ver off                                                     set lines 120                                                   undefine rem_loan year_int_rate term                            SELECT m+1 month,                                                           to_char(rem_loan, '99999999.00') rem_loan,                      to_char(loan_paid_tot, '99999999.00') loan_paid_tot,            to_char(mon_int, '999999.00') mon_int,                          to_char(tot_int, '99999999.00') tot_int,                        to_char(mon_paym, '99999999.00') mon_paym,                      to_char(mon_paym_tot, '99999999.00') mon_paym_tot,              to_char(grand_tot, '99999999.00') grand_tot         FROM dualMODEL    DIMENSION BY (-1 m)                                                                         MEASURES (&&rem_loan rem_loan,                                                                round(&&rem_loan*&&year_int_rate/100/12,2) mon_int,                                         ceil(&&rem_loan/&&term*100)/100 mon_paym,                                                   (&&rem_loan/&&term*100)/100 loan_paid_tot,                                                  round(&&rem_loan*&&year_int_rate/12/100,2) tot_int,                                         ceil(&&rem_loan/&&term*100)/100 + round(&&rem_loan*&&year_int_rate/100/12,2) mon_paym_tot,  ceil(&&rem_loan/&&term*100)/100 + round(&&rem_loan*&&year_int_rate/100/12,2) grand_tot    )RULES ITERATE (&&term) UNTIL (round(loan_paid_tot[iteration_number], 2) = &&rem_loan) (  rem_loan[iteration_number] = rem_loan[iteration_number -1] - mon_paym[iteration_number - 1],         mon_int[iteration_number] = round(rem_loan[iteration_number]*&&year_int_rate/100/12,2),              mon_paym[iteration_number] = least(ceil(&&rem_loan/&&term*100)/100, rem_loan[iteration_number]),     loan_paid_tot[iteration_number] = loan_paid_tot[iteration_number - 1] + mon_paym[iteration_number],  tot_int[iteration_number] = tot_int[iteration_number - 1] + mon_int[iteration_number],               mon_paym_tot[iteration_number] = mon_paym[iteration_number] + mon_int[iteration_number],             grand_tot[iteration_number] = grand_tot[iteration_number - 1] + mon_paym_tot[iteration_number]);   `

Result for loan of 1000 money units (lats, euros, dollars, pounds whatever) for 10 % year rate with term of 7 months looks as follows:
`Enter value for rem_loan: 1000Enter value for year_int_rate: 10Enter value for term: 7     MONTH REM_LOAN     LOAN_PAID_TO MON_INT    TOT_INT      MON_PAYM     MON_PAYM_TOT GRAND_TOT---------- ------------ ------------ ---------- ------------ ------------ ------------ ------------         0      1000.00       142.86       8.33         8.33       142.86       151.19       151.19         1       857.14       285.72       7.14        15.47       142.86       150.00       301.19         2       714.28       428.58       5.95        21.42       142.86       148.81       450.00         3       571.42       571.44       4.76        26.18       142.86       147.62       597.62         4       428.56       714.30       3.57        29.75       142.86       146.43       744.05         5       285.70       857.16       2.38        32.13       142.86       145.24       889.29         6       142.84      1000.00       1.19        33.32       142.84       144.03      1033.32`
For fixed payment each month the script is as follows:
`SELECT m+1 month,                                                           to_char(rem_loan, '99999999.00') rem_loan,                      to_char(loan_paid_tot, '99999999.00') loan_paid_tot,            to_char(mon_int, '999999.00') mon_int,                          to_char(tot_int, '99999999.00') tot_int,                        to_char(mon_paym, '99999999.00') mon_paym,                      to_char(mon_paym_tot, '99999999.00') mon_paym_tot,              to_char(grand_tot, '99999999.00') grand_tot         FROM dualMODEL    DIMENSION BY (-1 m)                                                                 MEASURES (&&rem_loan rem_loan,                                                        round(&&rem_loan*&&year_int_rate/100/12,2) mon_int,                                 ceil(&&rem_loan*(&&year_int_rate/100/12*POWER((1+&&year_int_rate/100/12),&&term))/    (POWER((1+&&year_int_rate/100/12),&&term)-1)*100)/100 -                             round(&&rem_loan*&&year_int_rate/100/12,2) mon_paym,                              ceil(&&rem_loan*(&&year_int_rate/100/12*POWER((1+&&year_int_rate/100/12),&&term))/    (POWER((1+&&year_int_rate/100/12),&&term)-1)*100)/100 -                             round(&&rem_loan*&&year_int_rate/100/12,2) loan_paid_tot,                         round(&&rem_loan*&&year_int_rate/12/100,2) tot_int,                                 ceil(&&rem_loan*(&&year_int_rate/100/12*POWER((1+&&year_int_rate/100/12),&&term))/    (POWER((1+&&year_int_rate/100/12),&&term)-1)*100)/100 mon_paym_tot,               ceil(&&rem_loan*(&&year_int_rate/100/12*POWER((1+&&year_int_rate/100/12),&&term))/    (POWER((1+&&year_int_rate/100/12),&&term)-1)*100)/100 grand_tot                 )RULES ITERATE (&&term) UNTIL (round(loan_paid_tot[iteration_number], 2) = &&rem_loan) (                rem_loan[iteration_number] = rem_loan[iteration_number -1] - mon_paym[iteration_number - 1],         mon_int[iteration_number] = round(rem_loan[iteration_number]*&&year_int_rate/100/12,2),              mon_paym_tot[iteration_number] = least(                                                                ceil(&&rem_loan*(&&year_int_rate/100/12*POWER((1+&&year_int_rate/100/12),&&term))/                     (POWER((1+&&year_int_rate/100/12),&&term)-1)*100)/100,                                             rem_loan[iteration_number] + mon_int[iteration_number]),                                           mon_paym[iteration_number] = mon_paym_tot[iteration_number] - mon_int[iteration_number],             loan_paid_tot[iteration_number] = loan_paid_tot[iteration_number - 1] + mon_paym[iteration_number],  tot_int[iteration_number] = tot_int[iteration_number - 1] + mon_int[iteration_number],               grand_tot[iteration_number] = grand_tot[iteration_number - 1] + mon_paym_tot[iteration_number]);   `
Result for the same loan of 1000 money units (lats, euros, dollars, pounds whatever) for 10 % year rate with term of 7 months looks as follows:
`     MONTH REM_LOAN     LOAN_PAID_TO MON_INT    TOT_INT      MON_PAYM     MON_PAYM_TOT GRAND_TOT---------- ------------ ------------ ---------- ------------ ------------ ------------ ------------         0      1000.00       139.33       8.33         8.33       139.33       147.66       147.66         1       860.67       279.82       7.17        15.50       140.49       147.66       295.32         2       720.18       421.48       6.00        21.50       141.66       147.66       442.98         3       578.52       564.32       4.82        26.32       142.84       147.66       590.64         4       435.68       708.35       3.63        29.95       144.03       147.66       738.30         5       291.65       853.58       2.43        32.38       145.23       147.66       885.96         6       146.42      1000.00       1.22        33.60       146.42       147.64      1033.60`
During my research I've found invaluable these resources: