FVSCHEDULE
Updated: 5 August 2010
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.
Syntax
SELECT [wct].[FVSCHEDULE] (
<@Interest_rate, float,>
)
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
Example
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
----------------------
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
----------------------
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
Reference Rate FV
-------------- ----------------------
1 month LIBOR 102133.79720865
3 month LIBOR 102338.924934961
See Also