Oracle® OLAP DML Reference 11g Release 2 (11.2) E17122-07

 PDF · Mobi · ePub

# VPMTSCHED

The VPMTSCHED function calculates a payment schedule (principal plus interest) for paying off a series of variable-rate installment loans over a specified number of time periods. VPMTSCHED 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. For each time period, you specify the initial amount of the loans incurred in that time period and the interest rate that is charged in that time period for each new or outstanding loan.

Return Value

DECIMAL

The result returned by the VPMTSCHED function is dimensioned by the union of all the dimensions of loans and rates and the dimension used as the time-dimension argument.

Syntax

VPMTSCHED(loansratesn, [time-dimension] [STATUS])

Parameters

loans

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 multiple time dimensions, the time-dimension argument is required.

rates

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 applies to the loans incurred or outstanding in that period. The interest rates are expressed as decimal values; for example, a 5 percent rate is expressed as .05.

n

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 a month is made when loans is dimensioned by `month`.

time-dimension

The name of the dimension along which the interest payments are calculated. When loans has a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional, unless loans has more than dimension of this type.

STATUS

Specifies that VPMTSCHED should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the payment schedule. By default VPMTSCHED uses the default status list.

Usage Notes

VPMTSCHED and NA Values

When loans has a value other than `NA` and the corresponding value of rates is `NA`, an error occurs.

VPMTSCHED 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 VPMTSCHED depends on whether the corresponding interest rate has a value of `NA` or a value other than `NA`. Table 8-21, "How NASKIP Affects the Results When a Loan or Rate Value is NA for a Given 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 1994, but both have values other than `NA` for succeeding years. When the number of payments is 3, VPMTSCHED returns `NA` for 1994, 1995, and 1996. For 1997, VPMTSCHED returns the payment due for loans incurred in 1995, 1996, and 1997.

VPTMPTSCHED Ignores the Status of the Time Dimension

The VPMTSCHED calculation begins with the first value of the time dimension, 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 `Yr95` to `Yr99`. The calculation always begins with `Yr95`, even when you limit the status of `year` so that it does not include `Yr95`.

However, when loans is not dimensioned by the time dimension, the VPMTSCHED calculation begins with the first value in the current status of the time dimension. For example, suppose loans is not dimensioned by `year`, but `year` is specified as time-dimension. When the status of `year` is limited to `Yr97` to `Yr99`, the calculation begins with `Yr97` instead of `Yr95`.

Examples

Example 8-165 Using VPMTSCHED

The following statements create two variables called `loans` and `rates`.

```DEFINE loans DECIMAL <year>
DEFINE rates DECIMAL <year>
```

Suppose you assign the following values to the variables `loans` and `rates`.

```YEAR             LOANS      RATES
-------------- ---------- ----------
Yr95               100.00       0.05
Yr96               200.00       0.06
Yr97               300.00       0.07
Yr98                 0.00       0.07
Yr99                 0.00       0.07
```

For each year, `loans` contains the initial value of the variable-rate loan incurred during that year. For each year, the value of `rates` is the interest rate that is charged for that year on any loans incurred or outstanding in that year.

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' VPMTSCHED(loans, rates, 3, year)
```

and produces the following report.

```YEAR                        Payment
-------------- --------------------
Yr95                          36.72
Yr96                         112.06
Yr97                         227.78
Yr98                         190.19
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 of principal on the loan incurred in 1995, plus the first payment of principal on the loan of \$200 incurred in 1996, plus interest on the remaining principals of both loans at 6 percent. The 1997 payment is the sum of the third and final principal payment on the loan incurred in 1995, the second of the three principal payments on the 1996 loan, the first payment of principal on the loan of \$300 incurred in 1997, plus interest on the remaining principals of all three loans at 7 percent. 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.