|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
The FPMTSCHED function calculates a payment schedule (principal plus interest) for paying off a series of fixed-rate installment loans over a specified number of time periods. For each time period, you specify the amount of the loans incurred during that time period and a single interest rate that will apply to those loans over their lifetime.
FPMTSCHED(loans, rates, n, [time-dimension])
A numeric expression that contains the initial amounts of the loans. When loans does not have a time dimension, or when loans is dimensioned by more than one time dimension, the time-dimension argument is required.
A numeric expression that contains the interest rates charged for loans. When rates is a dimensioned variable, it can be dimensioned by any dimension, including a different time dimension. When rates is dimensioned by a time dimension, you specify the interest rate in each time period that will apply to the loans incurred in that period. The interest rate for the time period in which a loan is incurred applies throughout the lifetime of that loan. The rates are expressed as decimals; for example, a 5 percent rate is expressed as
A numeric expression that specifies the number of payments required to pay off the loans in the series. The n expression can be dimensioned, but it cannot be dimensioned by the time dimension argument. One payment is made in each time period of the time dimension by which loans is dimensioned or in each time period of the dimension specified in the time-dimension argument. For example, one payment each month is made when loans is dimensioned by
The name of the dimension along which the interest payments are calculated. When the time dimension for loans has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional, unless loans has more than one time dimension.
The result returned by the FPMTSCHED function is dimensioned by the union of all the dimensions of loans and rates and the dimension used as the time-dimension argument.
FPMTSCHED calculates the payment for a given time period as the sum of the principal and interest due on each loan that is incurred or outstanding in that period.
When loans has a value other than
NA and the corresponding value of rates is
NA, an error occurs.
FPMTSCHED is affected by the NASKIP option. When NASKIP is set to
YES (the default), and a loan value is
NA for the affected time period, the result returned by FPMTSCHED depends on whether the corresponding interest rate has a value of
NA or a value other than
NA. Table 13-3, "Effect of NASKIP When Loan or Rate Values are NA for a Time Period" illustrates how NASKIP affects the results when a loan or rate value is
NA for a given time period.
As an example, suppose a loan expression and a corresponding interest expression both have
NA values for 1997 but both have values other than
NA for succeeding years. When the number of payments is 3, FPMTSCHED returns
NA for 1997, 1998, and 1999. For 2000, FPMTSCHED returns the payment due for loans incurred in 1998, 1999, and 2000.
The FPMTSCHED calculation begins with the first time dimension value, regardless of how the status of that dimension may be limited. For example, suppose loans is dimensioned by
year, and the values of
year range from
Yr99. The calculation always begins with
Yr95, even when you limit the status of
year so that it does not include
However, when loans is not dimensioned by the time dimension, the FPMTSCHED calculation begins with the first value in the current status of the time dimension. For example, suppose loans is not dimensioned by
year is specified as time-dimension. When the status of
year is limited to
Yr99, the calculation begins with
Yr97 instead of
The VPMTSCHED function, which calculates the payment schedule for a series of variable-rate loans, and the FINTSCHED and VINTSCHED functions, which calculate the interest portion of the payments on fixed-rate and variable-rate loans.
Example 13-31 Calculating a Payment Schedule
The following statements create two variables called
DEFINE loans DECIMAL <year> DEFINE rates DECIMAL <year>
Suppose you assign the following values to the variables
year loans rates -------------- ---------- ---------- Yr95 100.00 0.05 Yr96 200.00 0.06 Yr97 300.00 0.07 Yr98 0.00 0.00 Yr99 0.00 0.00
For each year,
loans contains the initial value of the fixed-rate loan incurred during that year. For each year, the value of
rates is the interest rate that will be charged for any loans incurred in that year; for those loans, this same rate is charged each year until the loans are paid off.
The following statement specifies that each loan is to be paid off in three payments, calculates the schedule for paying off the principal and interest on the loans,
REPORT W 20 HEADING 'Payment' FPMTSCHED(loans, rates, 3, year)
and produces the following report.
YEAR Payment -------------- -------------------- Yr95 36.72 Yr96 111.54 Yr97 225.86 Yr98 189.14 Yr99 114.32
The payment for 1995 is the principal due on the loan of $100 incurred in 1995, plus interest on the loan at 5 percent. The payment due in 1996 is the sum of the second payment on the loan incurred in 1995 (principal plus 5 percent interest), plus the first payment on the loan of $200 incurred in 1996 (principal plus 6 percent interest). The 1997 payment is the sum of the third and final payment on the loan incurred in 1995, the second of the three payments on the 1996 loan, and the first payment on the loan of $300 incurred in 1997 (principal plus 7 percent interest). Since the 1995 loan is paid off in 1997, the payment for 1998 covers the principal and interest for the 1996 and 1997 loans. The payment for 1999 is the final payment of principal and interest for the 1997 loan.