# Mortgage calculator with the SQL MODEL Clause

• Script Name Mortgage calculator with the SQL MODEL Clause
• Description This example shows a "creative" usage example for the SQL MODEL clause. Given a mortgage amount, an interest rate and a monthly payment, the SQL MODEL clause will calculate the "mortgage plan".
• Category SQL General / Analytics
• Contributor Carsten Czarski (Oracle)
• Created Friday October 16, 2015
• Statement 1
This step adds the formulas (RULES) - their semantics is similar to a spreadsheet. In this first step, the calculation parameters are embedded into the rules, but that will change in a later step. Column "a" will be the amount, "b" interest and "c" will contain the actual redemption each month.

Step 1: Author the MODEL clause

``````select rownumber, a, b, c from dual
MODEL
DIMENSION BY (rownum rownumber)
MEASURES (0 a, 0 b, 0 c)
RULES SEQUENTIAL ORDER ITERATE (10) (
a[1]                    = 20000,
b[ITERATION_NUMBER + 1] = a[cv(rownumber)] * 6 / 1200,
c[ITERATION_NUMBER + 1] = 600 - b[cv(rownumber)],
a[ITERATION_NUMBER + 2] = a[cv(rownumber)-1] - c[cv(rownumber) - 1]
)
order by rownumber``````
ROWNUMBERABC
120000100500
21950097.5502.5
318997.594.9875505.0125
418492.487592.4624375507.5375625
517984.949937589.9247496875510.0752503125
617474.874687187587.3743734359375512.6256265640625
716962.249060623437584.8112453031171875515.1887546968828125
816447.060305926554687582.2353015296327734375517.7646984703672265625
915929.295607456187460937579.6464780372809373046875520.3535219627190626953125
1015408.942085493468398242187577.0447104274673419912109375522.9552895725326580087890625
1114885.9867959209357402333984375 - -

11 rows selected.
• Statement 2
The starting values are now part of the MEASURES clause - no hardcoded values in the formulas any more. But this query does not terminate when the mortgage amount reaches zero.

Step 2: Separate calculation data from formulas

``````select rownumber, a, b, c, d from dual
MODEL
DIMENSION BY (rownum rownumber)
-- Change Amount, Payment and Interest Rate here!
MEASURES (20000 a, 0 b, 0 c, 600 d, 6 e)
RULES SEQUENTIAL ORDER ITERATE (500) (
b[ITERATION_NUMBER + 1] = a[cv(rownumber)] * e[cv(rownumber)] / 1200,
c[ITERATION_NUMBER + 1] = d[cv(rownumber)] - b[cv(rownumber)],
a[ITERATION_NUMBER + 2] = a[cv(rownumber)-1] - c[cv(rownumber) - 1],
e[ITERATION_NUMBER + 2] = e[cv(rownumber)-1],
d[ITERATION_NUMBER + 2] = d[cv(rownumber)-1]
)
order by rownumber``````
ROWNUMBERABCD
120000100500600
21950097.5502.5600
318997.594.9875505.0125600
418492.487592.4624375507.5375625600
517984.949937589.9247496875510.0752503125600
617474.874687187587.3743734359375512.6256265640625600
716962.249060623437584.8112453031171875515.1887546968828125600
816447.060305926554687582.2353015296327734375517.7646984703672265625600
915929.295607456187460937579.6464780372809373046875520.3535219627190626953125600
1015408.942085493468398242187577.0447104274673419912109375522.9552895725326580087890625600
1114885.986795920935740233398437574.4299339796046787011669921875525.5700660203953212988330078125600
1214360.416729900540418934565429687571.8020836495027020946728271484375528.1979163504972979053271728515625600
1313832.218813550043121029238256835937569.1610940677502156051461912841796875530.8389059322497843948538087158203125600
1413301.379907617793336634384448120117187566.5068995380889666831719222406005859375533.493100461911033316828077759399414063600
1512767.886807155882303317556370360717773463.839434035779411516587781851803588867536.160565964220588483412218148196411133600
1612231.726241191661714834144152212521362361.1586312059583085741707207610626068115538.841368794041691425829279238937393189600
1711692.884872397620023408314872973583969158.4644243619881001170415743648679198455541.535575638011899882958425635132080155600
1811151.349296759608123525356447338451888955.7567464837980406176267822366922594445544.243253516201959382373217763307740556600
1910607.106043243406164142983229575144148353.0355302162170308207149161478757207415546.964469783782969179285083852124279259600
2010060.14157345962319496369814572301986950.300707867298115974818490728615099345549.699292132701884025181509271384900655600
219510.44228132692131093851663645163496834547.55221140663460655469258318225817484175552.447788593365393445307416817741825158600
228957.99449273355591749320921963389314318744.78997246366777958746604609816946571592555.210027536332220412533953901830534284600
238402.78446519722369708067526573206260890342.0139223259861184854033763286603130445557.986077674013881514596623671339686956600
247844.79838752320981556607864206072292194739.22399193761604907783039321030361460975560.77600806238395092216960678969638539600
257284.02237946082586464390903527102653655736.42011189730412932321954517635513268275563.579888102695870676780454823644867317600
266720.4424913581299939671285804473816692433.60221245679064996983564290223690834617566.397787543209350030164357097763091654600
276154.04470381492064393696422334961857758630.77022351907460321968482111674809288792569.229776480925396780315178883251907112600
285584.81492733399524715664904446636667047427.92407463666997623578324522233183335233572.075925363330023764216754777668166648600
295012.73900197066522339243228968869850382625.06369500985332611696216144844349251917574.936304990146673883037838551556507481600
304437.80269698051854950939445113714199634522.18901348490259274754697225568570998175577.810986515097407252453027744314290018600
313859.99171046542114225694142339282770632719.29995855232710571128470711696413853167580.700041447672894288715292883035861468600
323279.29166901774824796822613050979184485916.39645834508874123984113065254895922433583.603541654911258760158869347451040776600
332695.68812736283698920806726116234080408313.47844063681418494604033630581170402042586.52155936318581505395966369418829598600
342109.16656799965117415410759746815250810310.5458328399982558707705379873407625405589.45416716000174412922946201265923746600
351519.7124008396494300248781354554932706437.59856200419824715012439067727746635322592.401437995801752849875609322722533647600
36927.3109628438476771750025261327707369964.63655481421923838587501263066385368498595.363445185780761614124987369336146315600
37331.9475176580669155608775387634345906811.65973758829033457780438769381717295341598.340262411709665422195612306182827047600
38-266.392744753642749861318073542748236366-1.33196372376821374930659036771374118183601.331963723768213749306590367713741182600
39-867.724708477410963610624663910461977548-4.33862354238705481805312331955230988774604.338623542387054818053123319552309888600
40-1472.063332019798018428677787230014287436-7.36031666009899009214338893615007143718607.360316660098990092143388936150071437600
41-2079.423648679897008520821176166164358873-10.39711824339948504260410588083082179433610.397118243399485042604105880830821794600
42-2689.820766923296493563425282046995180667-13.44910383461648246781712641023497590333613.449103834616482467817126410234975903600
43-3303.26987075791297603124240845723015657-16.51634935378956488015621204228615078283616.516349353789564880156212042286150783600
44-3919.786220111702540911398620499516307353-19.59893110055851270455699310249758153675619.598931100558512704556993102497581537600
45-4539.38515121226105361595561360201388889-22.69692575606130526807977806801006944442622.696925756061305268079778068010069444600
46-5162.082076968322358884035391670023958334-25.81041038484161179442017695835011979167625.810410384841611794420176958350119792600
47-5787.892487353163970678455568628374078126-28.93946243676581985339227784314187039067628.939462436765819853392277843141870391600
48-6416.831949789929790531847846471515948517-32.08415974894964895265923923235757974258632.084159748949648952659239232357579743600
49-7048.91610953887943948450708570387352826-35.24458054769439719742253542851936764133635.244580547694397197422535428519367641600
50-7684.160690086573836681929621132392895901-38.4208034504328691834096481056619644795638.42080345043286918340964810566196448600

Rows 1 - 50. More rows exist.
• Statement 3
This query will terminate when the mortgage has been completely paid back - i.e. when the "amount" reaches zero.

Step 3: Add a termination condition

``````select rownumber, a, b, c, d from dual
MODEL
DIMENSION BY (rownum rownumber)
-- Change Amount, Payment and Interest Rate here!
MEASURES (20000 a, 0 b, 0 c, 600 d, 6 e)
RULES SEQUENTIAL ORDER ITERATE (500) UNTIL (a[ITERATION_NUMBER + 1] <= 0) (
b[ITERATION_NUMBER + 1] = a[cv(rownumber)] * e[cv(rownumber)] / 1200,
d[ITERATION_NUMBER + 1] = least(d[cv(rownumber)], a[cv(rownumber)] + b[cv(rownumber)]),
c[ITERATION_NUMBER + 1] = d[cv(rownumber)] - b[cv(rownumber)],
a[ITERATION_NUMBER + 2] = a[cv(rownumber)-1] - c[cv(rownumber) - 1],
e[ITERATION_NUMBER + 2] = e[cv(rownumber)-1],
d[ITERATION_NUMBER + 2] = d[cv(rownumber)-1]
)
order by rownumber``````
ROWNUMBERABCD
120000100500600
21950097.5502.5600
318997.594.9875505.0125600
418492.487592.4624375507.5375625600
517984.949937589.9247496875510.0752503125600
617474.874687187587.3743734359375512.6256265640625600
716962.249060623437584.8112453031171875515.1887546968828125600
816447.060305926554687582.2353015296327734375517.7646984703672265625600
915929.295607456187460937579.6464780372809373046875520.3535219627190626953125600
1015408.942085493468398242187577.0447104274673419912109375522.9552895725326580087890625600
1114885.986795920935740233398437574.4299339796046787011669921875525.5700660203953212988330078125600
1214360.416729900540418934565429687571.8020836495027020946728271484375528.1979163504972979053271728515625600
1313832.218813550043121029238256835937569.1610940677502156051461912841796875530.8389059322497843948538087158203125600
1413301.379907617793336634384448120117187566.5068995380889666831719222406005859375533.493100461911033316828077759399414063600
1512767.886807155882303317556370360717773463.839434035779411516587781851803588867536.160565964220588483412218148196411133600
1612231.726241191661714834144152212521362361.1586312059583085741707207610626068115538.841368794041691425829279238937393189600
1711692.884872397620023408314872973583969158.4644243619881001170415743648679198455541.535575638011899882958425635132080155600
1811151.349296759608123525356447338451888955.7567464837980406176267822366922594445544.243253516201959382373217763307740556600
1910607.106043243406164142983229575144148353.0355302162170308207149161478757207415546.964469783782969179285083852124279259600
2010060.14157345962319496369814572301986950.300707867298115974818490728615099345549.699292132701884025181509271384900655600
219510.44228132692131093851663645163496834547.55221140663460655469258318225817484175552.447788593365393445307416817741825158600
228957.99449273355591749320921963389314318744.78997246366777958746604609816946571592555.210027536332220412533953901830534284600
238402.78446519722369708067526573206260890342.0139223259861184854033763286603130445557.986077674013881514596623671339686956600
247844.79838752320981556607864206072292194739.22399193761604907783039321030361460975560.77600806238395092216960678969638539600
257284.02237946082586464390903527102653655736.42011189730412932321954517635513268275563.579888102695870676780454823644867317600
266720.4424913581299939671285804473816692433.60221245679064996983564290223690834617566.397787543209350030164357097763091654600
276154.04470381492064393696422334961857758630.77022351907460321968482111674809288792569.229776480925396780315178883251907112600
285584.81492733399524715664904446636667047427.92407463666997623578324522233183335233572.075925363330023764216754777668166648600
295012.73900197066522339243228968869850382625.06369500985332611696216144844349251917574.936304990146673883037838551556507481600
304437.80269698051854950939445113714199634522.18901348490259274754697225568570998175577.810986515097407252453027744314290018600
313859.99171046542114225694142339282770632719.29995855232710571128470711696413853167580.700041447672894288715292883035861468600
323279.29166901774824796822613050979184485916.39645834508874123984113065254895922433583.603541654911258760158869347451040776600
332695.68812736283698920806726116234080408313.47844063681418494604033630581170402042586.52155936318581505395966369418829598600
342109.16656799965117415410759746815250810310.5458328399982558707705379873407625405589.45416716000174412922946201265923746600
351519.7124008396494300248781354554932706437.59856200419824715012439067727746635322592.401437995801752849875609322722533647600
36927.3109628438476771750025261327707369964.63655481421923838587501263066385368498595.363445185780761614124987369336146315600
37331.9475176580669155608775387634345906811.65973758829033457780438769381717295341331.947517658066915560877538763434590681333.607255246357250138681926457251763634
380000
390 - - 0

39 rows selected.
• Statement 4
This SQL Query uses TO_CHAR functions to format the results. The integer "row_number" is now being translated to a month.

``````select
to_char(a,'9G999G990D00') amount,
to_char(b,'9G999G990D00') interest,
to_char(c,'9G999G990D00') redemption,
to_char(d,'9G999G990D00') payment
from dual
MODEL
DIMENSION BY (rownum rownumber)
-- Change Amount, Payment and Interest Rate here!
MEASURES (20000 a, 0 b, 0 c, 600 d, 6 e)
RULES SEQUENTIAL ORDER ITERATE (500) UNTIL (a[ITERATION_NUMBER + 1] <= 0) (
b[ITERATION_NUMBER + 1] = a[cv(rownumber)] * e[cv(rownumber)] / 1200,
d[ITERATION_NUMBER + 1] = least(d[cv(rownumber)], a[cv(rownumber)] + b[cv(rownumber)]),
c[ITERATION_NUMBER + 1] = d[cv(rownumber)] - b[cv(rownumber)],
a[ITERATION_NUMBER + 2] = a[cv(rownumber)-1] - c[cv(rownumber) - 1],
e[ITERATION_NUMBER + 2] = e[cv(rownumber)-1],
d[ITERATION_NUMBER + 2] = d[cv(rownumber)-1]
)
order by rownumber``````
MONAMOUNTINTERESTREDEMPTIONPAYMENT
01-AUG-18 20,000.00 100.00 500.00 600.00
01-SEP-18 19,500.00 97.50 502.50 600.00
01-OCT-18 18,997.50 94.99 505.01 600.00
01-NOV-18 18,492.49 92.46 507.54 600.00
01-DEC-18 17,984.95 89.92 510.08 600.00
01-JAN-19 17,474.87 87.37 512.63 600.00
01-FEB-19 16,962.25 84.81 515.19 600.00
01-MAR-19 16,447.06 82.24 517.76 600.00
01-APR-19 15,929.30 79.65 520.35 600.00
01-MAY-19 15,408.94 77.04 522.96 600.00
01-JUN-19 14,885.99 74.43 525.57 600.00
01-JUL-19 14,360.42 71.80 528.20 600.00
01-AUG-19 13,832.22 69.16 530.84 600.00
01-SEP-19 13,301.38 66.51 533.49 600.00
01-OCT-19 12,767.89 63.84 536.16 600.00
01-NOV-19 12,231.73 61.16 538.84 600.00
01-DEC-19 11,692.88 58.46 541.54 600.00
01-JAN-20 11,151.35 55.76 544.24 600.00
01-FEB-20 10,607.11 53.04 546.96 600.00
01-MAR-20 10,060.14 50.30 549.70 600.00
01-APR-20 9,510.44 47.55 552.45 600.00
01-MAY-20 8,957.99 44.79 555.21 600.00
01-JUN-20 8,402.78 42.01 557.99 600.00
01-JUL-20 7,844.80 39.22 560.78 600.00
01-AUG-20 7,284.02 36.42 563.58 600.00
01-SEP-20 6,720.44 33.60 566.40 600.00
01-OCT-20 6,154.04 30.77 569.23 600.00
01-NOV-20 5,584.81 27.92 572.08 600.00
01-DEC-20 5,012.74 25.06 574.94 600.00
01-JAN-21 4,437.80 22.19 577.81 600.00
01-FEB-21 3,859.99 19.30 580.70 600.00
01-MAR-21 3,279.29 16.40 583.60 600.00
01-APR-21 2,695.69 13.48 586.52 600.00
01-MAY-21 2,109.17 10.55 589.45 600.00
01-JUN-21 1,519.71 7.60 592.40 600.00
01-JUL-21 927.31 4.64 595.36 600.00
01-AUG-21 331.95 1.66 331.95 333.61
01-SEP-21 0.00 0.00 0.00 0.00
01-OCT-21 0.00 - - 0.00

39 rows selected.
• Statement 5
Note the different parameters in the MEASURES clause.

Second example (different parameters)

``````select
to_char(a,'9G999G990D00') amount,
to_char(b,'9G999G990D00') interest,
to_char(c,'9G999G990D00') redemption,
to_char(d,'9G999G990D00') payment
from dual
MODEL
DIMENSION BY (rownum rownumber)
-- Change Amount, Payment and Interest Rate here!
MEASURES (50000 a, 0 b, 0 c, 800 d, 2 e)
RULES SEQUENTIAL ORDER ITERATE (500) UNTIL (a[ITERATION_NUMBER + 1] <= 0) (
b[ITERATION_NUMBER + 1] = a[cv(rownumber)] * e[cv(rownumber)] / 1200,
d[ITERATION_NUMBER + 1] = least(d[cv(rownumber)], a[cv(rownumber)] + b[cv(rownumber)]),
c[ITERATION_NUMBER + 1] = d[cv(rownumber)] - b[cv(rownumber)],
a[ITERATION_NUMBER + 2] = a[cv(rownumber)-1] - c[cv(rownumber) - 1],
e[ITERATION_NUMBER + 2] = e[cv(rownumber)-1],
d[ITERATION_NUMBER + 2] = d[cv(rownumber)-1]
)
order by rownumber``````
MONAMOUNTINTERESTREDEMPTIONPAYMENT
01-AUG-18 50,000.00 83.33 716.67 800.00
01-SEP-18 49,283.33 82.14 717.86 800.00
01-OCT-18 48,565.47 80.94 719.06 800.00
01-NOV-18 47,846.41 79.74 720.26 800.00
01-DEC-18 47,126.16 78.54 721.46 800.00
01-JAN-19 46,404.70 77.34 722.66 800.00
01-FEB-19 45,682.04 76.14 723.86 800.00
01-MAR-19 44,958.18 74.93 725.07 800.00
01-APR-19 44,233.11 73.72 726.28 800.00
01-MAY-19 43,506.83 72.51 727.49 800.00
01-JUN-19 42,779.34 71.30 728.70 800.00
01-JUL-19 42,050.64 70.08 729.92 800.00
01-AUG-19 41,320.73 68.87 731.13 800.00
01-SEP-19 40,589.59 67.65 732.35 800.00
01-OCT-19 39,857.24 66.43 733.57 800.00
01-NOV-19 39,123.67 65.21 734.79 800.00
01-DEC-19 38,388.88 63.98 736.02 800.00
01-JAN-20 37,652.86 62.75 737.25 800.00
01-FEB-20 36,915.62 61.53 738.47 800.00
01-MAR-20 36,177.14 60.30 739.70 800.00
01-APR-20 35,437.44 59.06 740.94 800.00
01-MAY-20 34,696.50 57.83 742.17 800.00
01-JUN-20 33,954.33 56.59 743.41 800.00
01-JUL-20 33,210.92 55.35 744.65 800.00
01-AUG-20 32,466.27 54.11 745.89 800.00
01-SEP-20 31,720.38 52.87 747.13 800.00
01-OCT-20 30,973.25 51.62 748.38 800.00
01-NOV-20 30,224.87 50.37 749.63 800.00
01-DEC-20 29,475.24 49.13 750.87 800.00
01-JAN-21 28,724.37 47.87 752.13 800.00
01-FEB-21 27,972.24 46.62 753.38 800.00
01-MAR-21 27,218.86 45.36 754.64 800.00
01-APR-21 26,464.23 44.11 755.89 800.00
01-MAY-21 25,708.33 42.85 757.15 800.00
01-JUN-21 24,951.18 41.59 758.41 800.00
01-JUL-21 24,192.77 40.32 759.68 800.00
01-AUG-21 23,433.09 39.06 760.94 800.00
01-SEP-21 22,672.14 37.79 762.21 800.00
01-OCT-21 21,909.93 36.52 763.48 800.00
01-NOV-21 21,146.45 35.24 764.76 800.00
01-DEC-21 20,381.69 33.97 766.03 800.00
01-JAN-22 19,615.66 32.69 767.31 800.00
01-FEB-22 18,848.35 31.41 768.59 800.00
01-MAR-22 18,079.77 30.13 769.87 800.00
01-APR-22 17,309.90 28.85 771.15 800.00
01-MAY-22 16,538.75 27.56 772.44 800.00
01-JUN-22 15,766.31 26.28 773.72 800.00
01-JUL-22 14,992.59 24.99 775.01 800.00
01-AUG-22 14,217.58 23.70 776.30 800.00
01-SEP-22 13,441.28 22.40 777.60 800.00

Rows 1 - 50. More rows exist.