Collections, Part III

In this series, I am demonstrating the use of PL/SQL collections with an case study of a mortgage amortization table, with each line of the table being an element of the collection. In the previous installment, I used an 8 byte character string (the date of the payment, in yyyymmdd format) as the index to an associative array, and illustrated some of the problems with that. In it I used the PRIOR method to find the previous element (since subtracting 1 from the current index no longer worked). Having brought up the subject of these collection methods (or pseudo operators, or functions), I may as well list all of the available ones. I put this table together as a quick summary:


Method                    Description                           Array    Varray      Nest. Tbl

EXISTS(n)   Returns TRUE if element 'n'     No    Yes[1],[2] Yes[1],[2]
            exists, otherwise FALSE

COUNT       Returns the current number of   Yes[3]  Yes[3]   Yes[3]
            elements in a collection

LIMIT       Returns the maximum size of a   Yes[5]  Yes[4]   Yes[5]
            collection.  Useful if one is
            using a TYPE stored in the
            Database, and one is not sure
            what the limit for this Varray
            may be.

FIRST       Returns the first or last       Yes[6]  Yes[7]   Yes[8]
LAST        subscript value in a collection,
            or NULL if the collection is empty.


PRIOR(sub)  Returns the item whose subscript
NEXT(sub)   is right before 'sub' (PRIOR)   Yes[9]  Yes[9]   Yes[9]
            or right after 'sub' (NEXT), or
            NULL if there is no next or prior
            element.

TRIM     TRIM removes one element from       No     Yes[10]  Yes[10]
TRIM(n)  the (high) end of a collection.
         TRIM(n) removes 'n' elements from
         the end of a collection.

DELETE      DELETE removes all elements     Yes[10] Yes[10]  Yes[10]
DELETE(n)   a collection.  DELETE(n) removes
DELETE(n,m) the element with subscript 'n' from
          a collection.  DELETE(n,m) deletes
          element 'n' (low) through 'm' (high).
          If 'm' is greater than 'n', nothing
          is deleted, and no error is raised.

EXTEND    Add a null element to the (high)    No    Yes[10]   Yes{10]
EXTEND(n) end of the collection.  EXTEND(n)
EXTEND(n,i) adds 'n' null elements to the end
          of the collection.  And EXTEND(n,i)
          adds 'n' copies of the 'i-th' element.

--------------------------
[1]  One way to skip around missing elements in a sparse nested table.

[2]  Another way to avoid the SUBSCRIPT_OUTSIDE_LIMIT error.

[3]  For Varrays, COUNT always equals LAST.  With Nested Tables, if you TRIM
or DELETE an element, the COUNT is reduced accordingly.  With Associative
Arrays, you don't even have to do a DELETE (since you can load an array up
sparsely).  In other words, if you create an array V, and assign V(1), V(3),
V(5), the COUNT is 3.

[4]  A Varray has an absolute maximum size, which is set by it's TYPE statement. 
Thus, once a PL/SQL block starts executing, the value returned by LIMIT is known. 
The number of elements actually in the Varray will often be less than this absolute 
LIMIT, so COUNT will usually be less than LIMIT.

[5]  But really only useful for Varrays, as Associative Arrays and Nested
tables always return NULL.

[6]  If an Associative Array is indexed by a VARCHAR variable, what is first
and last depend on the character set and sort order.  Since numeric
Associative Arrays can be entered with any subscript value, LAST can be
greater than COUNT, even if nothing has been deleted.

[7]  For Varrays, FIRST always = 1 and LAST always = COUNT.

[8]  For Nested Tables, FIRST can be greater than 1 if beginning elements are
deleted. If nothing has been deleted (in the middle) LAST = COUNT.  If
something has been deleted, then LAST < COUNT

[9]  As with FIRST and LAST, which of all the subscripts of the collection is
the previous or the subsequent may depend on the sort order of the character
 set.  For integer subscripts, there is no such dependency, but exactly what
 subscripts are in the collection can cause some surprises.  For example, if
one loads up a PL/SQL table with subscripts 2, 8, 3, 12, 15, 19, 1 (in that
order), then PRIOR(12) is the element whose subscript is 8, and not the
element whose subscript is 3.

[10] The TRIM, DELETE,  and EXTEND methods are complicated, especially when
one uses them in tandem.  A complete discussion of these is being defered to a
later posting.

Well, let's now look at some more of these methods, as applied to Associative Arrays. We are adding an additional complication to the requirements of the previous post: We only want the first value, the year-ending values, and the very last value. So, we do this the quick-and-dirty way: we take what we already have, and then go and delete what we no longer want.

If not very elegant, it does provide an excuse for using the DELETE method:

            Listing-3

1 alter session set nls_date_format = 'yyyymmdd';
2 
3 DECLARE
4  type amortTableLine is record (
5   ToInterest     number(12,2),
6   ToPrincipal    number(12,2),
7   EndingBalance  number(12,2)
8                               );
9 type amortTableType is table of amortTableLine
10   index by varchar2(9);
11
12  amortTable amortTabletype;
13
14  p_LoanAmount     number(10,2);
15  p_InterestRate   number;
16  p_loanTerm       number(2,0);
17  p_effectiveDate  date;
18  p_payment        number(7,2);
19  v_pmtNumber      number;
20
21  v_pmtDate        date;
22  v_pmtDateIdx     varchar2(9);   /* Because we are using it as an index */
23  v_pmtDateJan     varchar2(9);   /* Lower range of dates we are going to*/
24                                  /* delete (typically, will be in January*/
25  v_pmtDateNov     varchar2(9);   /* November of the year of v_pmtDateJan*/
26  v_monthlyRate    number;
27  v_totalInt       number(13,2);
28  v_totalPmt       number(13,2);
29  v_currentPrinc   number(8,2);
30 BEGIN
31 p_LoanAmount     := 100000;
32 p_InterestRate   := 6.5;
33 p_loanTerm       := 30;
34 p_effectiveDate  := to_date('20090315','yyyymmdd');
35 p_payment        := 632.07;
36 v_pmtDateIdx     := to_char(add_months(p_effectiveDate,1));   /* Deliberately using the session's date mask */
37 v_monthlyRate    := p_InterestRate / 1200;
38
39 amortTable(v_pmtDateIdx).ToInterest     := p_LoanAmount * v_monthlyRate;
40 amortTable(v_pmtDateIdx).ToPrincipal    := p_payment - amortTable(v_pmtDateIdx).ToInterest;
41 amortTable(v_pmtDateIdx).EndingBalance  := p_LoanAmount - amortTable(v_pmtDateIdx).ToPrincipal;
42 v_totalInt                   := amortTable(v_pmtDateIdx).ToInterest;
43 v_totalPmt                   := p_payment;
44 v_pmtDate        := add_months(p_effectiveDate,2);
45 v_pmtDateIdx     := to_char(v_pmtDate);
46
47 WHILE (v_pmtDate < add_months(p_effectiveDate,p_loanTerm * 12)) LOOP
48   amortTable(v_pmtDateIdx).ToInterest     := amortTable(amortTable.prior(v_pmtDateIdx)).EndingBalance * v_monthlyRate;
49   amortTable(v_pmtDateIdx).ToPrincipal    := p_payment - amortTable(v_pmtDateIdx).ToInterest;
50   amortTable(v_pmtDateIdx).EndingBalance  := amortTable(amortTable.prior(v_pmtDateIdx)).EndingBalance - amortTable(v_pmtDateIdx).ToPrincipal;
51   v_totalInt                   := v_totalInt + amortTable(v_pmtDateIdx).ToInterest;
52   v_totalPmt                   := v_totalPmt + p_payment;
53   v_pmtDate        := add_months(v_pmtDate,1);
54   v_pmtDateIdx     := to_char(v_pmtDate);
55 END LOOP;
 56   amortTable(v_pmtDateIdx).ToInterest     := amortTable(amortTable.prior(v_pmtDateIdx)).EndingBalance * v_monthlyRate;
 57   amortTable(v_pmtDateIdx).ToPrincipal    := amortTable(amortTable.prior(v_pmtDateIdx)).EndingBalance;
 58   amortTable(v_pmtDateIdx).EndingBalance  := 0;
 59   v_totalInt                   := v_totalInt + amortTable(v_pmtDateIdx).ToInterest;
 60   v_totalPmt                   := v_totalPmt + amortTable(v_pmtDateIdx).ToPrincipal + amortTable(v_pmtDateIdx).ToInterest;
 61
 62     /*Just to Illustrate the DELETE method, we will here delete */
 63     /*Everything except the 1st line, the last line, and the    */
 64     /*end of the month.                                         */
 65   v_pmtDateJan := amortTable.next(amortTable.first);
 66   v_pmtDateNov := substr(v_pmtDateJan,1,4)||'11'||substr(v_pmtDateJan,7,2);
 67   WHILE (v_pmtDateNov <= amortTable.last) LOOP
 68     amortTable.delete(v_pmtDateJan, v_pmtDateNov);
 69     v_pmtDateJan := amortTable.next(amortTable.next(v_pmtDateNov));
 70     v_pmtDateNov := substr(v_pmtDateJan,1,4)||'11'||substr(v_pmtDateJan,7,2);
 71   END LOOP;
 72     v_pmtDateNov := amortTable.prior(amortTable.last);
 73     amortTable.delete(v_pmtDateJan, v_pmtDateNov);
 74
 75   v_pmtDateIdx := amortTable.first;
 76   WHILE (v_pmtDateIdx <= amortTable.last) LOOP
 77     dbms_output.put_line(v_pmtDateIdx||'   '||
 78                          amortTable(v_pmtDateIdx).ToInterest||'   '||
 79                          amortTable(v_pmtDateIdx).ToPrincipal||'   '||
 80                          amortTable(v_pmtDateIdx).EndingBalance);
 81     v_pmtDateIdx := amortTable.next(v_pmtDateIdx);
 82   END LOOP;
 83
 84* END;

Lines 23-25: The plan is to use the DELETE(n,m) form of the delete function.
Since our index is a character string of the form 'yyyymmdd', 'n' will have
usually have the form 'yyyy01dd' (will be in January, except for the first set)
and 'm' will usually be of the form 'yyyy11dd' (will be in November) except
for the final year, where it could be any month.

Line 66: Here we start to have something different from the last posting. We
want to keep the first subscript, and start deleting from the second to the
end of the year.

Lines 68-72: We have set up our loop variables, and use them here.

Here is the output:

20090415   541.67   90.4     99909.6
20091215   537.67   94.4     99168.51
20101215   531.35   100.72   97995.06
20111215   524.61   107.46   96743.03
20121215   517.41   114.66   95407.15
20131215   509.73   122.34   93981.82
20141215   501.54   130.53   92461.03
20151215   492.8    139.27   90838.38
20161215   483.47   148.6    89107.05
20171215   473.52   158.55   87259.77
20181215   462.9    169.17   85288.79
20191215   451.57   180.5    83185.81
20201215   439.48   192.59   80941.99
20211215   426.58   205.49   78547.89
20221215   412.82   219.25   75993.44
20231215   398.14   233.93   73267.96
20241215   382.47   249.6    70359.91
20251215   365.75   266.32   67257.11
20261215   347.92   284.15   63946.51
20271215   328.89   303.18   60414.21
20281215   308.58   323.49   56645.32
20291215   286.92   345.15   52624.03
20301215   263.8    368.27   48333.43
20311215   239.14   392.93   43755.48
20321215   212.82   419.25   38870.92
20331215   184.74   447.33   33659.22
20341215   154.79   477.28   28098.51
20351215   122.82   509.25   22165.38
20361215   88.72    543.35   15834.9
20371215   52.33    579.74   9080.48
20381215   13.5     618.57   1873.67
20390315    3.39    626.46   0

Collections, Part II

In the last installment I went over the high-level specifications for our case study (a mortgage amortization schedule) using PL/SQL collections. It is time to actually view some code. So, without further ado, here is Listing-1:

          Listing-1

1  DECLARE
2   type amortTableLine is record (
3     pmtDate        date,
4     ToInterest     number(12,2),
5     ToPrincipal    number(12,2),
6     EndingBalance  number(12,2)
7                                 );
8   type amortTableType is table of amortTableLine
9     index by binary_integer;
10
11   amortTable amortTabletype;
12
13   p_LoanAmount     number(10,2);
14   p_InterestRate   number;
15   p_loanTerm       number(2,0);
16   p_effectiveDate  date;
17   p_payment        number(7,2);
18
19   v_pmtNumber      number(4,0);
20   v_monthlyRate    number;
21   v_totalInt       number(13,2);
22   v_totalPmt       number(13,2);
23   v_currentPrinc   number(8,2);
24  BEGIN
25   p_LoanAmount     := 100000;
26   p_InterestRate   := 6.5;
27   p_loanTerm       := 30;
28   p_effectiveDate  := to_date('20090315','yyyymmdd');
29   p_payment        := 632.07;
30   v_pmtNumber      := 2;
31   v_monthlyRate    := p_InterestRate / 1200;
32
33   amortTable(1).pmtdate       := add_months(p_effectiveDate,1);
34   amortTable(1).ToInterest     := p_LoanAmount * v_monthlyRate;
35   amortTable(1).ToPrincipal    := p_payment - amortTable(1).ToInterest;
36   amortTable(1).EndingBalance  := p_LoanAmount - amortTable(1).ToPrincipal;
37   v_totalInt                   := amortTable(1).ToInterest;
38   v_totalPmt                   := p_payment;
39
40   WHILE (v_pmtNumber <>

What Listing-1 does is to put each line of the amortization table into a separate
Associative Array record.  Some comments on the program:

Lines 2 - 11:  Here we define the array in which we will store each line of the
amortization table.  This is done in the standard way, by first (lines 2 - 6)
defining a record type (to define the fields of the line), then an array type
(lines 8-9) and finally (line 11) declaring the array (amortTable) as an instance
of that array type.  This could be done in one step if Oracle supplied a suitable
built-in data type, but it doesn't.

Lines 13 - 23:  Now we define the variables to be used in the routine.  Although
this is going to be run as an anonymous PL/SQL block, we define 5 variables to sort
of be input parameters, and indicate as much by prefixing them with 'p_'

Lines 25 - 331:  Here we initialize those variables which require initialization.
Yes, this could have been combined with lines 13 - 23.

Lines 33 - 36:  The first line of the amortization table requires special treatment,
so we do that here.

Lines 37 & 38:  We are going to calculate the total of payments and the total
interest paid by accumulating the values, so we start the process here.  Back when
I was more of a mathematician, I would have skipped this and calculated them at the
end.  Since then, I have been perverted by the Finance people, and insist on doing
these calculations the hard way.

Lines 40 - 49:  The body of the loop where all but the last payment are calculated.

Lines 50 - 55:  Here we calculate the final line in the table.

Lines 57 - 67:  And here we print out the results.

Remember the Sherlock Holmes story about the barking dog?  The dog didn't bark.  And
that was the key to the mystery.

What is most significant about using associative arrays with a dense integer
subscript is what does not have to be done.  There is no need to do anything special
to create a row in the array; one just adds it.  There is no need to do anything
special to navigate the array, one just increments the index.

The results of running Listing-1 are as follows:













Month
Interest
Principal
Balance 
15-APR-09 
  541.67 
  90.40  
 99909.60
15-MAY-09 
  541.18 
  90.89 
  99818.71
15-JUN-09 
  540.68 
  91.39 
  99727.32
15-JUL-09  
 540.19 
  91.88 
  99635.44
15-AUG-09  
 539.69 
  92.38 
  99543.06
.
.
.
.
.
.
.
.
.
.
.
.
15-JAN-39 
  10.15 
  621.92
  1251.75
15-FEB-39 
  6.78 
  625.29 
  626.46
15-MAR-39 
  3.39 
  626.46 
  0
 

Nothing surprising here.  So, let's now try something new.  We can now index a PL/SQL table with a VARCHAR2 index (which is why they are now called 'associative arrays').  Let us try to do this by using the payment date as the index, while making as few changes as necessary to our program.  The result is Listing-2.

  Listing-2

1  DECLARE
2   type amortTableLine is record (
3     ToInterest     number(12,2),  /* No more payment date field in the record */
4     ToPrincipal    number(12,2),
5     EndingBalance  number(12,2)
6                                 );
7   type amortTableType is table of amortTableLine
8     index by varchar2(9);    /* This will become the payment date, but it */
9                              /* cannot be a date type */
10   amortTable amortTabletype;
11
12   p_LoanAmount     number(10,2);
13   p_InterestRate   number;
14   p_loanTerm       number(2,0);
15   p_effectiveDate  date;
16   p_payment        number(7,2);
17   v_pmtNumber      number;
18
19   v_pmtDate        date;          /* We track the payment date as a date (to use date arithmetic) */
20   v_pmtDateIdx     varchar2(9);   /* Because we are using it as an index */
21   v_monthlyRate    number;
22   v_totalInt       number(13,2);
23   v_totalPmt       number(13,2);
24   v_currentPrinc   number(8,2);
25  BEGIN
26   p_LoanAmount     := 100000;
27   p_InterestRate   := 6.5;
28   p_loanTerm       := 30;
29   p_effectiveDate  := to_date('20090315','yyyymmdd');
30   p_payment        := 632.07;
31   v_pmtDateIdx     := to_char(add_months(p_effectiveDate,1));   /* Deliberately using the session's date mask */
32   v_monthlyRate    := p_InterestRate / 1200;
33
34   amortTable(v_pmtDateIdx).ToInterest     := p_LoanAmount * v_monthlyRate;
35   amortTable(v_pmtDateIdx).ToPrincipal    := p_payment - amortTable(v_pmtDateIdx).ToInterest;
36   amortTable(v_pmtDateIdx).EndingBalance  := p_LoanAmount - amortTable(v_pmtDateIdx).ToPrincipal;
37   v_totalInt                   := amortTable(v_pmtDateIdx).ToInterest;
38   v_totalPmt                   := p_payment;
39   v_pmtDate        := add_months(p_effectiveDate,2);
40   v_pmtDateIdx     := to_char(v_pmtDate);
41
42   WHILE (v_pmtDate <>

The first change is at Line 8, where the 'index by' variable becomes a varchar2(9).  This induces a change at Line 3, where we take the payment date out of the record (we could have left it in, but the result would have been  very  confusing.

But this leads to a problem: how do we generate new indexes?  Back in the old days, we would have kept the date in a numeric variable, and written routines to increment it as needed (and hoped we didn't have to cross the year 2100 on February 29th!). But this is Oracle, and we have a real Date Type, with Real Date Arithmetic. So, we keep the payment date variable as a date, and use standard date arithmetic on it.  Then, we convert the incremented date to the varchar2 variable we use as the actual index (this can be seen in lines 39 - 40 and 48 - 49).

Lines 43 and 45 illustrate the next problem.  With an integer subscript, the predecessor of row 'I' was simply 'I - 1'. To do this here would require us to subtract 1 from a varchar2 variable, which is an exercise in frustration.  But PL/SQL is a modern, somewhat object-oriented language.  So we have a 'prior' method (or function), which returns the previous row, as needed.

So, now when we print out the results we get the following:














Month
Interest
Principal
Balance 
15-APR-09 
  541.67 
  90.4 
  99909.6
15-MAY-09 
  541.18
  90.89 
  99818.71
15-JUN-09  
 541.18 
  90.89 
  99818.71
15-JUL-09 
  541.18 
  90.89 
99818.71
15-AUG-09
   541.18 
  90.89 
  99818.71
.
.
.
.
.
.
.
.
.
.
.
.
15-JAN-39
   524.61 
  107.46
   96743.03
15-FEB-39 
  524.61 
  107.46 
  96743.03
15-MAR-39  
    96957.37 

 0
 

Which is not even close to what we want.

Here is what went wrong:  when we calculated the second row, we calculated the index as May 15, 2009.  This was converted using the default date mask to '15-MAY-09'.  The prior function returned the loan balance as of the only other index present, which was '15-APR-09'.

Next we calculated the third row.  Now the index is '15-JUN-09'  The prior function grabbed the loan balance for the index which sorts just before '15-JUN-09'.  This is not '15-MAY-09', but '15-APR-09' all over again. And the same thing happened with July 15, 2009.  Indeed, it is not until we get to August that we again reference '15-MAY-09', by which time we were intending to reference '15-JUL-09'.

The fix is set the default date mask to something which coverts to a varchar2 value which sorts in the order we need:

SQL> alter session set nls_date_format = 'yyyymmdd';

Then, when we run the program, we get what we want:














Month
Interest
Principal
Balance 
15-APR-09 
  541.67 
  90.4  
 99909.6
15-MAY-09 
  541.18 
  90.89 
  99818.71
15-JUN-09 
  540.68 
  91.39 
  99727.32
15-JUL-09  
 540.19 
  91.88 
  99635.44
15-AUG-09  
 539.69 
  92.38 
  99543.06
.
.
.
.
.
.
.
.
.
.
.
.
15-JAN-39 
  10.15 
  621.92
  1251.75
15-FEB-39 
  6.78 
  625.29 
  626.46
15-MAR-39 
  3.39 
  626.46 
  0
 

The alternative is to obey Singer's first law of Oracle Date Types:

"Never convert (implicitly or explicitly) an Oracle Date to a number or a character string without wrapping it explicitly in a TO_CHAR function with a format mask you supply" 
But this did nicely illustrate what can go wrong with a varchar2 index on an associative array where the sort order is not what one expects.

Collections, Part I

By "Collections", Oracle means the use of Varrays, Nested Tables, or Associative Arrays (formerly Index-By Tables (formerly PL/SQL Tables)) in PL/SQL. There are many examples extant, most of which use an (almost) meaningless code fragment to illustrate one, or perhaps two, points. My goal here is to illustrate the use of Collections through a meaningful example. Which is more complicated than one might assume at first glance.

For the first question one must ask when considering the appropriateness of an array in a PL/SQL program, is, "why is a relational table not being used instead"? Many of the examples I have come across in existing programs appear to have been written because the programmer was afraid to write a join. Indeed, when I first was introduced to PL/SQL, my immediate thought was "now I won't have to become a SQL expert." Today, I argue that any proper use of collections should be to do something which cannot be done with SQL.

In 8i and 9i, one standard use was for bulk fetches or inserts. Today, Oracle will automatically optimize DML to use bulk fetches or inserts (the problem is to prevent it when one-row-at-a-time is more efficient.) So, for this series of posts, I want to illustrate other uses for them.

I am going to develop a case study, specifically, that of a mortgage amortization schedule. As an illustration I am going to use what Lending Tree was displaying at the time I went looking. The exact numbers are not important at the moment; here I just want to show the basics of the calculations (only the first 5 and the last two lines are shown):

Amortization Schedule

Month	Interest	Principal	Balance
Mar, 2009	$541.67	$90.40	$99,909.60
Apr, 2009	$541.18	$90.89	$99,818.71
May, 2009	$540.68	$91.38	$99,727.32
Jun, 2009	$540.19	$91.88	$99,635.45
Jul, 2009	$539.69	$92.38	$99,543.07
Jan, 2039	$6.79	$625.28	$628.66
Feb, 2039	$3.41	$628.66	$0.00

Elsewhere, we are told that the annual interest rate is 6.5%, the loan amount is $100,000, and the monthly payment is $632.07. The table then gives us the date of each payment, the amount of the payment allocated to interest, the amount allocated to principal reduction, and the loan balance after the payment is made.

If we were given this as a programming assignment, these would be the proverbial "napkin specs." Before we can proceed, we must bring them up to at least "one-pager" status. We can start by looking at how each column gets calculated:

Month: [initial] = Loan Origination Date + one month
[subsequent] = Previous value + one month

Balance [initial] = Loan Amount - Current Principal
[subsequent] = Previous value - Current Principal

Principal = Payment - Current Interest

Interest: = Previous Balance * I
where I = (Annual Interest Rate) / 1200

To make sure we really understand what is happening, let's look at a few lines. The monthly interest rate is 6.5/1200, or 0.005416667. Multiply this by the original loan balance, and we get the interest for the first month: $541.67. Subtracting this from the payment ($632.07) we find that the principal reduction is $90.40. Subtract $90.40 from $100,000 and we get the balance after 1 month, $99,909.60.

Just to make sure that this isn't a fluke, let us repeat this with the third payment. The balance after two payments is $99,818.71. Multiplying this by 0.005416667 we get the interest for this payment, $540.68. Subtracting this from $632.07 we get the amount devoted to principal, $91.39. Subtract this from last month's balance, and we get the new loan balance, $99.727.32. And the published chart has $91.38 for this month's principal reduction. Payment 3 and we are already out of synch.

The reader who has stayed with me this far with the numbers will have already noted a number of other places where the published chart is wrong. What is happening is that the program computing the numbers is not rounding at each step. The actual payment used is $632.0680238 (which we see rounded as $632.07). The actual interest charge for the first payment is $541.666667 and the balance after one payment is actually $99,909.59868, the balance after two payments $99,818.70765, and the balance after three $99,727.32429. Since the unrounded interest is 540.68467 and the unrounded principal reduction is $91.38335 we see that things really are in balance.

It is, of course, illegal for a financial institution to actually use the example numbers. This gives us another specification for our routine: make sure all intermediate results are properly rounded.

And there is yet another item to be added. Because no intermediate results were rounded, the last line happens to balance perfectly. In general this will not be the case, instead, the last payment will be a little larger or smaller than the others. So, the finished routine will have to adjust the last payment.

Before any specification can be considered complete, the boundary conditions must be examined. We have already examined the case where the final payment must be examined. What happens if the specified payment is less than the interest due? At first glance, all seems well, the principal reduction will become negative and get added to the outstanding principal. But there is a problem here: if we are not careful, we could end up breaking the law.

The "law" allows for two approaches to this problem, where the payment is less than the interest due. One approach, known as the "actuarial method" (because it is more mathematically elegant) allows for the interest to be added to the principal, and, in the next period, for interest to be charged on this interest. The other, termed the U.S. Rule, has the interest added to the principal, but no interest is then collected on this interest. Which rule applies depends on the loan documents. For simplicity, I am going to be applying the actuarial rule, but this complication must be mentioned at least in passing.

Already at the end of the first post, and not a line of PL/SQL has yet been written. Expect quite a bit of it in the next post.