Mortgage calculator with the SQL MODEL Clause

View All Scripts Login to Run Script

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".
Area 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


ROWNUMBER A B C
1 20000 100 500
2 19500 97.5 502.5
3 18997.5 94.9875 505.0125
4 18492.4875 92.4624375 507.5375625
5 17984.9499375 89.9247496875 510.0752503125
6 17474.8746871875 87.3743734359375 512.6256265640625
7 16962.2490606234375 84.8112453031171875 515.1887546968828125
8 16447.0603059265546875 82.2353015296327734375 517.7646984703672265625
9 15929.2956074561874609375 79.6464780372809373046875 520.3535219627190626953125
10 15408.9420854934683982421875 77.0447104274673419912109375 522.9552895725326580087890625
11 14885.9867959209357402333984375  -  - 

11 rows selected.

ROWNUMBER	A	B	C
1	20000	100	500
2	19500	97.5	502.5
3	18997.5	94.9875	505.0125
4	18492.4875	92.4624375	507.5375625
5	17984.9499375	89.9247496875	510.0752503125
6	17474.8746871875	87.3743734359375	512.6256265640625
7	16962.2490606234375	84.8112453031171875	515.1887546968828125
8	16447.0603059265546875	82.2353015296327734375	517.7646984703672265625
9	15929.2956074561874609375	79.6464780372809373046875	520.3535219627190626953125
10	15408.9420854934683982421875	77.0447104274673419912109375	522.9552895725326580087890625
11	14885.9867959209357402333984375	-	-

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


ROWNUMBER A B C D
1 20000 100 500 600
2 19500 97.5 502.5 600
3 18997.5 94.9875 505.0125 600
4 18492.4875 92.4624375 507.5375625 600
5 17984.9499375 89.9247496875 510.0752503125 600
6 17474.8746871875 87.3743734359375 512.6256265640625 600
7 16962.2490606234375 84.8112453031171875 515.1887546968828125 600
8 16447.0603059265546875 82.2353015296327734375 517.7646984703672265625 600
9 15929.2956074561874609375 79.6464780372809373046875 520.3535219627190626953125 600
10 15408.9420854934683982421875 77.0447104274673419912109375 522.9552895725326580087890625 600
11 14885.9867959209357402333984375 74.4299339796046787011669921875 525.5700660203953212988330078125 600
12 14360.4167299005404189345654296875 71.8020836495027020946728271484375 528.1979163504972979053271728515625 600
13 13832.2188135500431210292382568359375 69.1610940677502156051461912841796875 530.8389059322497843948538087158203125 600
14 13301.3799076177933366343844481201171875 66.5068995380889666831719222406005859375 533.493100461911033316828077759399414063 600
15 12767.8868071558823033175563703607177734 63.839434035779411516587781851803588867 536.160565964220588483412218148196411133 600
16 12231.7262411916617148341441522125213623 61.1586312059583085741707207610626068115 538.841368794041691425829279238937393189 600
17 11692.8848723976200234083148729735839691 58.4644243619881001170415743648679198455 541.535575638011899882958425635132080155 600
18 11151.3492967596081235253564473384518889 55.7567464837980406176267822366922594445 544.243253516201959382373217763307740556 600
19 10607.1060432434061641429832295751441483 53.0355302162170308207149161478757207415 546.964469783782969179285083852124279259 600
20 10060.141573459623194963698145723019869 50.300707867298115974818490728615099345 549.699292132701884025181509271384900655 600
21 9510.442281326921310938516636451634968345 47.55221140663460655469258318225817484175 552.447788593365393445307416817741825158 600
22 8957.994492733555917493209219633893143187 44.78997246366777958746604609816946571592 555.210027536332220412533953901830534284 600
23 8402.784465197223697080675265732062608903 42.0139223259861184854033763286603130445 557.986077674013881514596623671339686956 600
24 7844.798387523209815566078642060722921947 39.22399193761604907783039321030361460975 560.77600806238395092216960678969638539 600
25 7284.022379460825864643909035271026536557 36.42011189730412932321954517635513268275 563.579888102695870676780454823644867317 600
26 6720.44249135812999396712858044738166924 33.60221245679064996983564290223690834617 566.397787543209350030164357097763091654 600
27 6154.044703814920643936964223349618577586 30.77022351907460321968482111674809288792 569.229776480925396780315178883251907112 600
28 5584.814927333995247156649044466366670474 27.92407463666997623578324522233183335233 572.075925363330023764216754777668166648 600
29 5012.739001970665223392432289688698503826 25.06369500985332611696216144844349251917 574.936304990146673883037838551556507481 600
30 4437.802696980518549509394451137141996345 22.18901348490259274754697225568570998175 577.810986515097407252453027744314290018 600
31 3859.991710465421142256941423392827706327 19.29995855232710571128470711696413853167 580.700041447672894288715292883035861468 600
32 3279.291669017748247968226130509791844859 16.39645834508874123984113065254895922433 583.603541654911258760158869347451040776 600
33 2695.688127362836989208067261162340804083 13.47844063681418494604033630581170402042 586.52155936318581505395966369418829598 600
34 2109.166567999651174154107597468152508103 10.5458328399982558707705379873407625405 589.45416716000174412922946201265923746 600
35 1519.712400839649430024878135455493270643 7.59856200419824715012439067727746635322 592.401437995801752849875609322722533647 600
36 927.310962843847677175002526132770736996 4.63655481421923838587501263066385368498 595.363445185780761614124987369336146315 600
37 331.947517658066915560877538763434590681 1.65973758829033457780438769381717295341 598.340262411709665422195612306182827047 600
38 -266.392744753642749861318073542748236366 -1.33196372376821374930659036771374118183 601.331963723768213749306590367713741182 600
39 -867.724708477410963610624663910461977548 -4.33862354238705481805312331955230988774 604.338623542387054818053123319552309888 600
40 -1472.063332019798018428677787230014287436 -7.36031666009899009214338893615007143718 607.360316660098990092143388936150071437 600
41 -2079.423648679897008520821176166164358873 -10.39711824339948504260410588083082179433 610.397118243399485042604105880830821794 600
42 -2689.820766923296493563425282046995180667 -13.44910383461648246781712641023497590333 613.449103834616482467817126410234975903 600
43 -3303.26987075791297603124240845723015657 -16.51634935378956488015621204228615078283 616.516349353789564880156212042286150783 600
44 -3919.786220111702540911398620499516307353 -19.59893110055851270455699310249758153675 619.598931100558512704556993102497581537 600
45 -4539.38515121226105361595561360201388889 -22.69692575606130526807977806801006944442 622.696925756061305268079778068010069444 600
46 -5162.082076968322358884035391670023958334 -25.81041038484161179442017695835011979167 625.810410384841611794420176958350119792 600
47 -5787.892487353163970678455568628374078126 -28.93946243676581985339227784314187039067 628.939462436765819853392277843141870391 600
48 -6416.831949789929790531847846471515948517 -32.08415974894964895265923923235757974258 632.084159748949648952659239232357579743 600
49 -7048.91610953887943948450708570387352826 -35.24458054769439719742253542851936764133 635.244580547694397197422535428519367641 600
50 -7684.160690086573836681929621132392895901 -38.4208034504328691834096481056619644795 638.42080345043286918340964810566196448 600

Rows 1 - 50. More rows exist.

ROWNUMBER	A	B	C	D
1	20000	100	500	600
2	19500	97.5	502.5	600
3	18997.5	94.9875	505.0125	600
4	18492.4875	92.4624375	507.5375625	600
5	17984.9499375	89.9247496875	510.0752503125	600
6	17474.8746871875	87.3743734359375	512.6256265640625	600
7	16962.2490606234375	84.8112453031171875	515.1887546968828125	600
8	16447.0603059265546875	82.2353015296327734375	517.7646984703672265625	600
9	15929.2956074561874609375	79.6464780372809373046875	520.3535219627190626953125	600
10	15408.9420854934683982421875	77.0447104274673419912109375	522.9552895725326580087890625	600
11	14885.9867959209357402333984375	74.4299339796046787011669921875	525.5700660203953212988330078125	600
12	14360.4167299005404189345654296875	71.8020836495027020946728271484375	528.1979163504972979053271728515625	600
13	13832.2188135500431210292382568359375	69.1610940677502156051461912841796875	530.8389059322497843948538087158203125	600
14	13301.3799076177933366343844481201171875	66.5068995380889666831719222406005859375	533.493100461911033316828077759399414063	600
15	12767.8868071558823033175563703607177734	63.839434035779411516587781851803588867	536.160565964220588483412218148196411133	600
16	12231.7262411916617148341441522125213623	61.1586312059583085741707207610626068115	538.841368794041691425829279238937393189	600
17	11692.8848723976200234083148729735839691	58.4644243619881001170415743648679198455	541.535575638011899882958425635132080155	600
18	11151.3492967596081235253564473384518889	55.7567464837980406176267822366922594445	544.243253516201959382373217763307740556	600
19	10607.1060432434061641429832295751441483	53.0355302162170308207149161478757207415	546.964469783782969179285083852124279259	600
20	10060.141573459623194963698145723019869	50.300707867298115974818490728615099345	549.699292132701884025181509271384900655	600
21	9510.442281326921310938516636451634968345	47.55221140663460655469258318225817484175	552.447788593365393445307416817741825158	600
22	8957.994492733555917493209219633893143187	44.78997246366777958746604609816946571592	555.210027536332220412533953901830534284	600
23	8402.784465197223697080675265732062608903	42.0139223259861184854033763286603130445	557.986077674013881514596623671339686956	600
24	7844.798387523209815566078642060722921947	39.22399193761604907783039321030361460975	560.77600806238395092216960678969638539	600
25	7284.022379460825864643909035271026536557	36.42011189730412932321954517635513268275	563.579888102695870676780454823644867317	600
26	6720.44249135812999396712858044738166924	33.60221245679064996983564290223690834617	566.397787543209350030164357097763091654	600
27	6154.044703814920643936964223349618577586	30.77022351907460321968482111674809288792	569.229776480925396780315178883251907112	600
28	5584.814927333995247156649044466366670474	27.92407463666997623578324522233183335233	572.075925363330023764216754777668166648	600
29	5012.739001970665223392432289688698503826	25.06369500985332611696216144844349251917	574.936304990146673883037838551556507481	600
30	4437.802696980518549509394451137141996345	22.18901348490259274754697225568570998175	577.810986515097407252453027744314290018	600
31	3859.991710465421142256941423392827706327	19.29995855232710571128470711696413853167	580.700041447672894288715292883035861468	600
32	3279.291669017748247968226130509791844859	16.39645834508874123984113065254895922433	583.603541654911258760158869347451040776	600
33	2695.688127362836989208067261162340804083	13.47844063681418494604033630581170402042	586.52155936318581505395966369418829598	600
34	2109.166567999651174154107597468152508103	10.5458328399982558707705379873407625405	589.45416716000174412922946201265923746	600
35	1519.712400839649430024878135455493270643	7.59856200419824715012439067727746635322	592.401437995801752849875609322722533647	600
36	927.310962843847677175002526132770736996	4.63655481421923838587501263066385368498	595.363445185780761614124987369336146315	600
37	331.947517658066915560877538763434590681	1.65973758829033457780438769381717295341	598.340262411709665422195612306182827047	600
38	-266.392744753642749861318073542748236366	-1.33196372376821374930659036771374118183	601.331963723768213749306590367713741182	600
39	-867.724708477410963610624663910461977548	-4.33862354238705481805312331955230988774	604.338623542387054818053123319552309888	600
40	-1472.063332019798018428677787230014287436	-7.36031666009899009214338893615007143718	607.360316660098990092143388936150071437	600
41	-2079.423648679897008520821176166164358873	-10.39711824339948504260410588083082179433	610.397118243399485042604105880830821794	600
42	-2689.820766923296493563425282046995180667	-13.44910383461648246781712641023497590333	613.449103834616482467817126410234975903	600
43	-3303.26987075791297603124240845723015657	-16.51634935378956488015621204228615078283	616.516349353789564880156212042286150783	600
44	-3919.786220111702540911398620499516307353	-19.59893110055851270455699310249758153675	619.598931100558512704556993102497581537	600
45	-4539.38515121226105361595561360201388889	-22.69692575606130526807977806801006944442	622.696925756061305268079778068010069444	600
46	-5162.082076968322358884035391670023958334	-25.81041038484161179442017695835011979167	625.810410384841611794420176958350119792	600
47	-5787.892487353163970678455568628374078126	-28.93946243676581985339227784314187039067	628.939462436765819853392277843141870391	600
48	-6416.831949789929790531847846471515948517	-32.08415974894964895265923923235757974258	632.084159748949648952659239232357579743	600
49	-7048.91610953887943948450708570387352826	-35.24458054769439719742253542851936764133	635.244580547694397197422535428519367641	600
50	-7684.160690086573836681929621132392895901	-38.4208034504328691834096481056619644795	638.42080345043286918340964810566196448	600

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


ROWNUMBER A B C D
1 20000 100 500 600
2 19500 97.5 502.5 600
3 18997.5 94.9875 505.0125 600
4 18492.4875 92.4624375 507.5375625 600
5 17984.9499375 89.9247496875 510.0752503125 600
6 17474.8746871875 87.3743734359375 512.6256265640625 600
7 16962.2490606234375 84.8112453031171875 515.1887546968828125 600
8 16447.0603059265546875 82.2353015296327734375 517.7646984703672265625 600
9 15929.2956074561874609375 79.6464780372809373046875 520.3535219627190626953125 600
10 15408.9420854934683982421875 77.0447104274673419912109375 522.9552895725326580087890625 600
11 14885.9867959209357402333984375 74.4299339796046787011669921875 525.5700660203953212988330078125 600
12 14360.4167299005404189345654296875 71.8020836495027020946728271484375 528.1979163504972979053271728515625 600
13 13832.2188135500431210292382568359375 69.1610940677502156051461912841796875 530.8389059322497843948538087158203125 600
14 13301.3799076177933366343844481201171875 66.5068995380889666831719222406005859375 533.493100461911033316828077759399414063 600
15 12767.8868071558823033175563703607177734 63.839434035779411516587781851803588867 536.160565964220588483412218148196411133 600
16 12231.7262411916617148341441522125213623 61.1586312059583085741707207610626068115 538.841368794041691425829279238937393189 600
17 11692.8848723976200234083148729735839691 58.4644243619881001170415743648679198455 541.535575638011899882958425635132080155 600
18 11151.3492967596081235253564473384518889 55.7567464837980406176267822366922594445 544.243253516201959382373217763307740556 600
19 10607.1060432434061641429832295751441483 53.0355302162170308207149161478757207415 546.964469783782969179285083852124279259 600
20 10060.141573459623194963698145723019869 50.300707867298115974818490728615099345 549.699292132701884025181509271384900655 600
21 9510.442281326921310938516636451634968345 47.55221140663460655469258318225817484175 552.447788593365393445307416817741825158 600
22 8957.994492733555917493209219633893143187 44.78997246366777958746604609816946571592 555.210027536332220412533953901830534284 600
23 8402.784465197223697080675265732062608903 42.0139223259861184854033763286603130445 557.986077674013881514596623671339686956 600
24 7844.798387523209815566078642060722921947 39.22399193761604907783039321030361460975 560.77600806238395092216960678969638539 600
25 7284.022379460825864643909035271026536557 36.42011189730412932321954517635513268275 563.579888102695870676780454823644867317 600
26 6720.44249135812999396712858044738166924 33.60221245679064996983564290223690834617 566.397787543209350030164357097763091654 600
27 6154.044703814920643936964223349618577586 30.77022351907460321968482111674809288792 569.229776480925396780315178883251907112 600
28 5584.814927333995247156649044466366670474 27.92407463666997623578324522233183335233 572.075925363330023764216754777668166648 600
29 5012.739001970665223392432289688698503826 25.06369500985332611696216144844349251917 574.936304990146673883037838551556507481 600
30 4437.802696980518549509394451137141996345 22.18901348490259274754697225568570998175 577.810986515097407252453027744314290018 600
31 3859.991710465421142256941423392827706327 19.29995855232710571128470711696413853167 580.700041447672894288715292883035861468 600
32 3279.291669017748247968226130509791844859 16.39645834508874123984113065254895922433 583.603541654911258760158869347451040776 600
33 2695.688127362836989208067261162340804083 13.47844063681418494604033630581170402042 586.52155936318581505395966369418829598 600
34 2109.166567999651174154107597468152508103 10.5458328399982558707705379873407625405 589.45416716000174412922946201265923746 600
35 1519.712400839649430024878135455493270643 7.59856200419824715012439067727746635322 592.401437995801752849875609322722533647 600
36 927.310962843847677175002526132770736996 4.63655481421923838587501263066385368498 595.363445185780761614124987369336146315 600
37 331.947517658066915560877538763434590681 1.65973758829033457780438769381717295341 331.947517658066915560877538763434590681 333.607255246357250138681926457251763634
38 0 0 0 0
39 0  -  - 0

39 rows selected.

ROWNUMBER	A	B	C	D
1	20000	100	500	600
2	19500	97.5	502.5	600
3	18997.5	94.9875	505.0125	600
4	18492.4875	92.4624375	507.5375625	600
5	17984.9499375	89.9247496875	510.0752503125	600
6	17474.8746871875	87.3743734359375	512.6256265640625	600
7	16962.2490606234375	84.8112453031171875	515.1887546968828125	600
8	16447.0603059265546875	82.2353015296327734375	517.7646984703672265625	600
9	15929.2956074561874609375	79.6464780372809373046875	520.3535219627190626953125	600
10	15408.9420854934683982421875	77.0447104274673419912109375	522.9552895725326580087890625	600
11	14885.9867959209357402333984375	74.4299339796046787011669921875	525.5700660203953212988330078125	600
12	14360.4167299005404189345654296875	71.8020836495027020946728271484375	528.1979163504972979053271728515625	600
13	13832.2188135500431210292382568359375	69.1610940677502156051461912841796875	530.8389059322497843948538087158203125	600
14	13301.3799076177933366343844481201171875	66.5068995380889666831719222406005859375	533.493100461911033316828077759399414063	600
15	12767.8868071558823033175563703607177734	63.839434035779411516587781851803588867	536.160565964220588483412218148196411133	600
16	12231.7262411916617148341441522125213623	61.1586312059583085741707207610626068115	538.841368794041691425829279238937393189	600
17	11692.8848723976200234083148729735839691	58.4644243619881001170415743648679198455	541.535575638011899882958425635132080155	600
18	11151.3492967596081235253564473384518889	55.7567464837980406176267822366922594445	544.243253516201959382373217763307740556	600
19	10607.1060432434061641429832295751441483	53.0355302162170308207149161478757207415	546.964469783782969179285083852124279259	600
20	10060.141573459623194963698145723019869	50.300707867298115974818490728615099345	549.699292132701884025181509271384900655	600
21	9510.442281326921310938516636451634968345	47.55221140663460655469258318225817484175	552.447788593365393445307416817741825158	600
22	8957.994492733555917493209219633893143187	44.78997246366777958746604609816946571592	555.210027536332220412533953901830534284	600
23	8402.784465197223697080675265732062608903	42.0139223259861184854033763286603130445	557.986077674013881514596623671339686956	600
24	7844.798387523209815566078642060722921947	39.22399193761604907783039321030361460975	560.77600806238395092216960678969638539	600
25	7284.022379460825864643909035271026536557	36.42011189730412932321954517635513268275	563.579888102695870676780454823644867317	600
26	6720.44249135812999396712858044738166924	33.60221245679064996983564290223690834617	566.397787543209350030164357097763091654	600
27	6154.044703814920643936964223349618577586	30.77022351907460321968482111674809288792	569.229776480925396780315178883251907112	600
28	5584.814927333995247156649044466366670474	27.92407463666997623578324522233183335233	572.075925363330023764216754777668166648	600
29	5012.739001970665223392432289688698503826	25.06369500985332611696216144844349251917	574.936304990146673883037838551556507481	600
30	4437.802696980518549509394451137141996345	22.18901348490259274754697225568570998175	577.810986515097407252453027744314290018	600
31	3859.991710465421142256941423392827706327	19.29995855232710571128470711696413853167	580.700041447672894288715292883035861468	600
32	3279.291669017748247968226130509791844859	16.39645834508874123984113065254895922433	583.603541654911258760158869347451040776	600
33	2695.688127362836989208067261162340804083	13.47844063681418494604033630581170402042	586.52155936318581505395966369418829598	600
34	2109.166567999651174154107597468152508103	10.5458328399982558707705379873407625405	589.45416716000174412922946201265923746	600
35	1519.712400839649430024878135455493270643	7.59856200419824715012439067727746635322	592.401437995801752849875609322722533647	600
36	927.310962843847677175002526132770736996	4.63655481421923838587501263066385368498	595.363445185780761614124987369336146315	600
37	331.947517658066915560877538763434590681	1.65973758829033457780438769381717295341	331.947517658066915560877538763434590681	333.607255246357250138681926457251763634
38	0	0	0	0
39	0	-	-	0

Statement 4

This SQL Query uses TO_CHAR functions to format the results. The integer "row_number" is now being translated to a month.

Step 4: Add some formatting

select   
  add_months(trunc(sysdate, 'MONTH'), rownumber) as mon,   
  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


MON AMOUNT INTEREST REDEMPTION PAYMENT
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.

MON	AMOUNT	INTEREST	REDEMPTION	PAYMENT
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

Statement 5

Note the different parameters in the MEASURES clause.

Second example (different parameters)

select   
  add_months(trunc(sysdate, 'MONTH'), rownumber) as mon,   
  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


MON AMOUNT INTEREST REDEMPTION PAYMENT
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.

MON	AMOUNT	INTEREST	REDEMPTION	PAYMENT
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

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database