FVSCHEDULE

Updated 2023-10-10 22:24:29.223000

Syntax

SELECT [wct].[FVSCHEDULE] (
   <@Interest_rate, float,>
 )

Description

Use the aggregate function FVSCHEDULE to calculate the future value of an initial investment using a series of compound rates. This function calculates the value of the compound rates, the result of which can then be used to multiply against the initial investment.

Arguments

@Interest_rate

the column in the table specified in the WHERE clause that contains the interest rate values to be compounded.

Return Type

float

Remarks

FVSCHEDULE is an aggregate function, so the results to be returned are a function of the criteria specified in the WHERE clause.

Examples

In this example we will compare the future value of an initial investment of $100,000 against two different term structures. One will be one month rates, compounded monthly, and the other will be three month rates compounded quarterly.

Create a table to store the rates to be used in the calculation:

CREATE TABLE [dbo].[int_fact]

(

    [mth] [int] NOT NULL,

    [int_fact_desc] [nvarchar](50) NOT NULL,

    [int_fact] [float] NOT NULL,

    CONSTRAINT [PK_int_fact]

        PRIMARY KEY CLUSTERED (

                                  [mth] ASC,

                                  [int_fact_desc] ASC

                              )

        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

                  ALLOW_ROW_LOCKS = ON,

              ALLOW_PAGE_LOCKS = ON

             ) ON [PRIMARY]

) ON [PRIMARY];

Load the following values into the table.

INSERT INTO int_fact

VALUES

(0, 'LIBOR1M', 0.015);

INSERT INTO int_fact

VALUES

(1, 'LIBOR1M', 0.0175);

INSERT INTO int_fact

VALUES

(2, 'LIBOR1M', 0.0195);

INSERT INTO int_fact

VALUES

(3, 'LIBOR1M', 0.02175);

INSERT INTO int_fact

VALUES

(4, 'LIBOR1M', 0.02225);

INSERT INTO int_fact

VALUES

(5, 'LIBOR1M', 0.0223745);

INSERT INTO int_fact

VALUES

(6, 'LIBOR1M', 0.025);

INSERT INTO int_fact

VALUES

(7, 'LIBOR1M', 0.02625);

INSERT INTO int_fact

VALUES

(8, 'LIBOR1M', 0.02325);

INSERT INTO int_fact

VALUES

(9, 'LIBOR1M', 0.021215);

INSERT INTO int_fact

VALUES

(10, 'LIBOR1M', 0.02);

INSERT INTO int_fact

VALUES

(11, 'LIBOR1M', 0.0195);

INSERT INTO int_fact

VALUES

(0, 'LIBOR3M', 0.02);

INSERT INTO int_fact

VALUES

(3, 'LIBOR3M', 0.0225);

INSERT INTO int_fact

VALUES

(6, 'LIBOR3M', 0.024);

INSERT INTO int_fact

VALUES

(9, 'LIBOR3M', 0.02625);

To calculate the value one year hence using the one month LIBOR (LIBOR1M), use the following SELECT statement:

select 100000 * wct.FVSCHEDULE(int_fact / 12)

from int_fact

where int_fact_desc = 'LIBOR1M';

Here is the result set.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"102133.79720865"}]}

To calculate the value one year hence using the three month LIBOR (LIBOR3M), use the following SELECT statement:

select 100000 * wct.FVSCHEDULE(int_fact / 4)

from int_fact

where int_fact_desc = 'LIBOR3M';

Here is the result set.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"102338.924934961"}]}

And to see both results in the same resultant table.

select '1 month LIBOR' as [Reference Rate]
,100000 * wct.FVSCHEDULE(int_fact/12) as FV
from int_fact
where int_fact_desc = 'LIBOR1M'
UNION ALL
Select '3 month LIBOR' as [Reference Rate]
,100000 * wct.FVSCHEDULE(int_fact/4) as FV
from int_fact
Where int_fact_desc = 'LIBOR3M';

Here is the result set.

{"columns":[{"field":"Reference Rate"},{"field":"FV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Reference Rate":"1 month LIBOR","FV":"02133.79720865"},{"Reference Rate":"3 month LIBOR","FV":"02338.924934961"}]}