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 dual
MODEL
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: 1000
Enter value for year_int_rate: 10
Enter 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 dual
MODEL
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:

13 comments:

Asif Momen said...

Good one.

Have a look at my version http://momendba.blogspot.com/2008/06/sql-for-loan-payment.html

Gints Plivna said...

Khekhe this is an example how to become to similar solutions independently :)

Unknown said...

The best part of lending and borrowing these days is the ease with which one can make application for such loans. There is no need of visiting offices of each and every lender Borrowers can go online to search lenders and compare terms and conditions of different loan providers. Borrowers can research a bad credit cash loan online and not spend hours driving to banks and lenders only to find that they do not fulfill those lenders terms. Once you are online, you can access many lenders who specialize in cash loans to borrowers with bad credit. Once approved, you will soon have the loan you need. Don't let your bad credit stop you from applying for the cash you need now - Just remember, lenders are in the market to do business and their business is the provision of loans to borrowers, without lending, no lender can survive in the market.
Mortgage Calculator Simple

Unknown said...

I was getting bore since morning but as soon as I got this link & reached at this blog, I turned into fresh and also joyful too.online payday loan

Unknown said...

Every day I visit a number of blog sites to see content, however this offers quality based content.USDA mortgage

Mohamed Ali said...

$$$ GENUINE LOAN WITH LOW INTEREST RATE APPLY $$$
Do you need finance to start up your own business or expand your business, Do you need funds to pay off your debt? We give out loan to interested individuals and company's who are seeking loan with good faith. Are you seriously in need of an urgent loan contact us.

Email: shadiraaliuloancompany1@gmail.com
Phone No: +919873186890.

billsgen said...

Thanks for sharing your knowledge..

mortgage calculator

Policy said...

There are many banks offering Low Interest Home loan

Policy said...

There are many banks offering Low Interest Home loan

sonialwotson said...

The quality of your articles and contents is great

China Desktop Calculator
Desktop Calculator Suppliers
Quality Calculator Manufacture

StevenHWicker said...

Great blog. All posts have something to learn. Your work is very good and i appreciate you and hopping for some more informative posts. Mortgage Lending Calculator

aliena dawid said...

Ask all of the questions that you need to know, such as which lender offers the best loan, how much do the payments cost, is there a special credit check hererequired and so on.

Loanwalle said...

This is amazing work and great learning Online loan