ODDFSCHED
Updated: 26 August 2016
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 [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
RETURNS TABLE (
[num_pmt] [int] NULL,
[amt_prin_init] [float] NULL,
[amt_pmt] [float] NULL,
[amt_int_pay] [float] NULL,
[amt_prin_pay] [float] NULL,
[amt_prin_end] [float] NULL
)
|
Column
|
Description
|
|
num_pmt
|
The number of the payment from 0 to @Nper.
|
|
amt_prin_init
|
the amt_prin_end from the previous row.
|
|
amt_pmt
|
amt_prin_pay + amt_int_pay.
|
|
amt_int_pay
|
When num_pmt > 1 then PMT(@Rate,@Nper,@PV,@FV) - amt_prin_pay. When num_pmt = 1, if @IntRule = 'U' then amt_prin_init * @Rate * @FirstPeriod else amt_prin_init * (POWER(1+@Rate, @FirstPeriod) – 1).
|
|
amt_prin_pay
|
amt_prin_init – amt_prin_end.
|
|
amt_prin_end
|
PV(@Rate,@Nper - num_pmt,PMT(@Rate,@Nper,@PV,@FV),@FV)
|
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