SQL Server ODDFSCHED Function

Updated 2023-10-06 22:31:05.990000

Description

Use the table-valued function ODDFSCHED to generate an annuity-like payment schedule where the first period is a different length of the time than all subsequent periods and those subsequent periods are assumed to be of equal length. Principal amortization, however, assumes all periods (including the first one) are of equal length. This results in a schedule where the cash flow for the first period is different than the cash flow for all subsequent periods.

To generate a payment schedule where the first period is of a different length but the payments are the same for all periods, use the ODDFPMTSCHED function.

Syntax

SELECT * FROM [westclintech].[wct].[ODDFSCHED](
  <@Rate, float,>
 ,<@Nper, int,>
 ,<@PV, float,>
 ,<@FV, float,>
 ,<@FirstPeriod, float,>
 ,<@IntRule, nvarchar(4000),>)

Arguments

@Rate

the periodic interest rate. @Rate is of type float or of a type that can be implicitly converted to float.

@Nper

the number of periods (repayments). @Nper is of type int or of a type that can be implicitly converted to int.

@PV

the present value or principal amount. @PV is of type float or of a type that can be implicitly converted to float.

@FV

the future value; the ending balance in the amortization schedule. @FV is an expression of type float or of a type that can be implicitly converted to float.

@FirstPeriod

the length of the first period expressed in periods. @FirstPeriod is an expression of type float or of a type that can be implicitly converted to float.

@IntRule

use 'U' to calculate the first period interest using the US rule and 'A' to calculate first period interest using the Actuarial rule.

Return Type

table

Remarks

@Rate must be greater than -1.

@Nper must be greater than zero.

@FirstPeriod must be greater than zero.

@IntRule must be either 'U' or 'A'.

Examples

Example #1

A loan for 11,500 to be amortized over 36 periods at a periodic interest rate of 0.5%. The first period is half as long as a regular period. Interest is accrued using the US method.

SELECT *
FROM wct.ODDFSCHED(   0.005,  --@Rate
                      36,     --@Nper
                      -11500, --@PV
                      0,      --@FV
                      0.5,    --@FirstPeriod
                      'U'     --@IntRule
                  );

This produces the following result.

Example #2

A loan for 36000 to be amortized over 60 period at a periodic interest rate of 1.5%. The first period is 2/3 the length of a regular period and interest is accrued using the actuarial method.

SELECT *
FROM wct.ODDFSCHED(   0.015,  --@Rate
                      60,     --@Nper
                      -36000, --@PV
                      0,      --@FV
                      0.5,    --@FirstPeriod
                      'A'     --@IntRule
                  );

This produces the following result.

Example #3

In this example the principal amount is 25,000, the first period is 1.5 times the length of a regular period, the periodic rate is .75% and there will be a 5,000 balance at the end of the amortization. Interest is calculated using the US rule.

SELECT *
FROM wct.ODDFSCHED(   0.0075, --@Rate
                      60,     --@Nper
                      -25000, --@PV
                      5000,   --@FV
                      1.5,    --@FirstPeriod
                      'U'     --@IntRule
                  );

This produces the following result.

See Also

LPMTSCHED - Amortization schedule for a loan with constant periodic payemnts and an odd first period where interest is accrued using the US rule

ODDFPMTSCHED - Amortization schedule for an annuity with odd first period

PMT - Calculate the periodic payment for an annuity

PMTSCHED - Calculate an amortization schedule for a loan with no odd periods.

PV - Present value of an annuity