Getting started

SQL Server XFV Function

Updated 2023-10-09 12:51:51.123000

Description

Use the scalar function XFV to calculate the future value of a cash flow between two dates. See the Examples for an explanation of the formula used in the XFV calculation.

Syntax

SELECT [westclintech].[wct].[XFV](
  <@StartDate, datetime,>
 ,<@CashflowDate, datetime,>
 ,<@EndDate, datetime,>
 ,<@CashflowRate, float,>
 ,<@EndRate, float,>
 ,<@Cashflow, float,>)

Arguments

@StartDate

the starting date for the annual interest rates used in the XFV calculation. Thus, the rate for the date of the cash flow is the rate from the start date (@StartDate) to the cash flow date (@CashflowDate) and the rate for the end date (@EndDate) is the rate from the start date (@StartDate) to the end date (@EndDate). @StartDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@CashflowDate

The date on which the cash flows occurs. @CashflowDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@EndDate

the ending date for purposes of calculating the future value. The future value is calculated from the cash flow date to the end date. @EndDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@CashflowRate

the annual interest rate for the cash flow date. This should be the interest rate from the start date (@StartDate) to the cash flow date (@CashflowDate). @CashflowRate is an expression of type float or of a type that can be implicitly converted to float.

@EndRate

the annual interest rate for the end date. This should be the interest rate from the start date (@StartDate) to the end date (@EndDate). @EndRate is an expression of type float or of a type that can be implicitly converted to float.

Return Type

float

Remarks

The future value will have the same sign as the cash flow amount (@CashFlow).

If the @CashflowRate is equal to -1, XPV will return a NULL.

XFV allows positive and negative values for @CashflowRate.

XFV allows positive and negative values for @EndRate.

@CashflowRate is an annual rate of interest.

@EndRate is an annual rate of interest.

The @CashflowRate should be the annual interest rate from @StartDate to @CashflowDate.

The @EndRate should be the annual interest rate from @StartDate to @EndDate.

To calculate a future value using periods or for different interest bases, try the EFV function.

Examples

On 01-Feb-11 calculate the future value of a cash flow to be received in one month through to the 01-Feb-12. The one-month rate is .142%. The one-year rate is .246%.

SELECT wct.XFV(   '2011-02-01', --@StartDate
                  '2011-03-01', --@CashflowDate
                  '2012-02-01', --@EndDate
                  .00142,       --@CashflowRate
                  .00246,       --@EndRate
                  100000        --@Cashflow
              ) as FV;

This produces the following result.

FV
100235.088391894

The result of this calculation means that on 01-Feb-11, we anticipate that 100,000 received on 01-Mar-11 will be worth approximately 100,235.09 on 01-Feb-12, based on the rates provided to the function. Mathematically, this is the same as the following calculation:

SELECT wct.XFV(   '2011-02-01', --start date
                  '2011-02-01', --cash flow date
                  '2012-02-01', --end date in one year
                  .00000,       --cash rate
                  .00246,       --one year rate
                  1             --cash flow amount
              ) / wct.XFV(   '2011-02-01', --start date
                             '2011-02-01', --cash flow date
                             '2011-03-01', --end date in one month
                             .00000,       --cash rate
                             .00142,       --one month rate
                             1             --cash flow amount
                         ) * 100000 as FV;

This produces the following result.

FV
100235.088391894

Calculate the future value as at 01-Feb-16 of a cash flow to be received in six months. The six-month rate is .172% and the 5-year rate is 2.007%.

SELECT wct.XFV(   '2011-02-01', --start date
                  '2011-08-01', --cash flow date
                  '2016-02-01', --end date
                  .00172,       --six-month rate
                  .02007,       --five-year rate
                  100000        --cash flow amount
              ) as FV;

This produces the following result.

FV
110357.896730894

The XFV function can be used in conjunction with the SUM function to calculate the Net Future Value of a collection of cash flows. Let's say we had the following monthly cash flows and cash flow periods, and we wanted calculate the future value of the cash flows using an annual rate of 2.5%. For purposes of this example, we will assume that all the cash flows occur on the last of the month, and we will use the EOMONTH function and the CALCDATE function to turn the year and month in the derived table into the last calendar day of the month.

SELECT SUM(wct.XFV(
                      '2011-01-31',
                      wct.EOMONTH(wct.CALCDATE(yr, mth, 1), 0),
                      wct.CALCDATE(2012, 2, 29),
                      0.0250,
                      0.0250,
                      CF
                  )
          ) as SUMFV
FROM
(
    VALUES
        (2011, 1, -10000),
        (2011, 2, 2000),
        (2011, 5, 1500),
        (2011, 6, 3000),
        (2011, 10, 3800),
        (2012, 2, 2500),
        (2012, 2, 2500)
) n (yr, mth, cf);

This produces the following result.

SUMFV
5189.23863529536

This is exactly the same result that we would obtain by using the XNFV function.

SELECT SUM(wct.XFV(
                      '2011-01-31',
                      wct.EOMONTH(wct.CALCDATE(yr, mth, 1), 0),
                      wct.CALCDATE(2012, 2, 29),
                      0.0250,
                      0.0250,
                      CF
                  )
          ) as SUMFV,
       wct.XNFV(.025, cf, wct.EOMONTH(wct.CALCDATE(yr, mth, 1), 0)) as XNFV
FROM
(
    VALUES
        (2011, 1, -10000),
        (2011, 2, 2000),
        (2011, 5, 1500),
        (2011, 6, 3000),
        (2011, 10, 3800),
        (2012, 2, 2500),
        (2012, 2, 2500)
) n (yr, mth, cf);

This produces the following result.

SUMFV	XNFV
5189.23863529536	5189.23863529536

Unlike the aggregate XNFV function, however, by using the SUM and XFV functions, it is possible to calculate the present value of the cash flows using different rates for each cash flow date. In this example, we have a table of rates upon which we do cubic spline interpolation (using the SPLINE_Q function from the XLeratorDB/math function library) and join to our cash flows using the appropriate interpolated rate for the each date. For illustration purpose, this SQL will show the calculation for each period.

SELECT wct.EOMONTH('2011-01-31', k.SeriesValue) as mdate,
       wct.SPLINE_q(
                       'SELECT *
      FROM (
            VALUES
            (wct.DATEINT(2011,1,31), 0),
            (wct.DATEINT(2011,2,28), 0.142),
            (wct.DATEINT(2011,4,30), 0.160),
            (wct.DATEINT(2011,7,31), 0.173),
            (wct.DATEINT(2012,1,31), 0.246),
            (wct.DATEINT(2013,1,31), 0.605),
            (wct.DATEINT(2014,1,31), 1.008)
            ) r (rdate, annrate)',
                       cast(wct.EOMONTH('1/31/2011', k.SeriesValue) as float)
                   ) / 100 as mrate
INTO #r
FROM wct.SeriesInt(0, 36, NULL, NULL, NULL) as k;
SELECT *,
       wct.XFV(StartDate, CashFlowDate, EndDate, CashFlowRate, EndRate, cf) as 
                 FV
FROM
(
    SELECT '31 Jan 2011' as StartDate,
           CONVERT(varchar(11), wct.EOMONTH(wct.CALCDATE(n.yr, n.mth, 1), 0), 106)
                     as CashFlowDate,
           '31 Jan 2013' as EndDate,
           r1.mrate as CashFlowRate,
           r2.mrate as EndRate,
           cf
    FROM
    (
        VALUES
            (2011, 1, -10000),
            (2011, 2, 2000),
            (2011, 5, 1500),
            (2011, 6, 3000),
            (2011, 10, 3800),
            (2012, 2, 2500),
            (2012, 2, 2500)
    ) n (yr, mth, cf) ,
    #r r1,
    #r r2
    WHERE r1.mdate = wct.EOMONTH(wct.CALCDATE(n.yr, n.mth, 1), 0)
          AND r2.mdate = wct.CALCDATE(2013, 1, 31)
) m;
DROP TABLE #r;

This produces the following result.

StartDate	CashFlowDate	EndDate	CashFlowRate	EndRate	cf	FV
31 Jan 2011	31 Jan 2011	31 Jan 2013	0	0.00605	-10000	-10121.5332860383
31 Jan 2011	28 Feb 2011	31 Jan 2013	0.00142	0.00605	2000	2024.08631484058
31 Jan 2011	31 May 2011	31 Jan 2013	0.00149024760746661	0.00605	1500	1517.48688022446
31 Jan 2011	30 Jun 2011	31 Jan 2013	0.00157012464555445	0.00605	3000	3034.50285720946
31 Jan 2011	31 Oct 2011	31 Jan 2013	0.00209416902047763	0.00605	3800	3840.16928670732
31 Jan 2011	29 Feb 2012	31 Jan 2013	0.00262753313860248	0.00605	2500	2523.22595928291
31 Jan 2011	29 Feb 2012	31 Jan 2013	0.00262753313860248	0.00605	2500	2523.22595928291

And when we use the SUM function.

SELECT wct.EOMONTH('2011-01-31', k.SeriesValue) as mdate,
       wct.SPLINE_q(
                       'SELECT *
      FROM (
            VALUES
            (wct.DATEINT(2011,1,31), 0),
            (wct.DATEINT(2011,2,28), 0.142),
            (wct.DATEINT(2011,4,30), 0.160),
            (wct.DATEINT(2011,7,31), 0.173),
            (wct.DATEINT(2012,1,31), 0.246),
            (wct.DATEINT(2013,1,31), 0.605),
            (wct.DATEINT(2014,1,31), 1.008)
            ) r (rdate, annrate)',
                       cast(wct.EOMONTH('1/31/2011', k.SeriesValue) as float)
                   ) / 100 as mrate
INTO #r
FROM wct.SeriesInt(0, 36, NULL, NULL, NULL) as k;
SELECT SUM(wct.XFV(StartDate, CashFlowDate, EndDate, CashFlowRate, EndRate, cf)) 
          as SUMFV
FROM
(
    SELECT '31 Jan 2011' as StartDate,
           CONVERT(varchar(11), wct.EOMONTH(wct.CALCDATE(n.yr, n.mth, 1), 0), 106)
                     as CashFlowDate,
           '31 Jan 2013' as EndDate,
           r1.mrate as CashFlowRate,
           r2.mrate as EndRate,
           cf
    FROM
    (
        VALUES
            (2011, 1, -10000),
            (2011, 2, 2000),
            (2011, 5, 1500),
            (2011, 6, 3000),
            (2011, 10, 3800),
            (2012, 2, 2500),
            (2012, 2, 2500)
    ) n (yr, mth, cf) ,
    #r r1,
    #r r2
    WHERE r1.mdate = wct.EOMONTH(wct.CALCDATE(n.yr, n.mth, 1), 0)
          AND r2.mdate = wct.CALCDATE(2013, 1, 31)
) m;
DROP TABLE #r;

This produces the following result.

SUMFV
5341.16397150935

One more interesting way to use the XFV function is in conjunction with the XNFV function. The XNFV function calculates the future value of all the cash flows passed to the calculation using the maximum date value in the set as the anchor. In other words, let's say that on 01-Feb-11 you are looking at a series of cash flows that terminate on 15-Aug-12. The XNFV calculation will reflect the XNFV as of 15-Aug-12. If you want to know what the future value will be on 31-Dec-12, you can try to add another cashflow to the data for 31-Dec-12 with an amount of zero, but in many cases it might be easier to use XFV to calculate the future value from the 15-Aug-12 to 31-Dec-12.

SELECT wct.XNFV(.025, cfamt, cfdate) as FV
FROM
(
    VALUES
        ('2011-06-30', -100000),
        ('2011-09-15', 5000),
        ('2011-11-28', 10000),
        ('2012-01-31', 15000),
        ('2012-03-17', 20000),
        ('2012-06-18', 25000),
        ('2012-08-15', 30000)
) n (cfdate, cfamt);

This produces the following result.

FV
2971.20547453459

To calculate the future value to 31-Dec-12, we could use the following SQL.

SELECT wct.XFV('2011-02-01', MAX(cfdate), '2012-12-31', .0250, .0250, wct.XNFV(
          .025, cfamt, cfdate)) as FV
FROM
(
    VALUES
        ('2011-06-30', -100000),
        ('2011-09-15', 5000),
        ('2011-11-28', 10000),
        ('2012-01-31', 15000),
        ('2012-03-17', 20000),
        ('2012-06-18', 25000),
        ('2012-08-15', 30000)
) n (cfdate, cfamt);

This produces the following result.

FV
2999.07405063237

Since the rate for XFV and XNFV are the same in this example, this is equivalent to the following SQL.

SELECT wct.XNFV(.025, cfamt, cfdate) as FV
FROM
(
    VALUES
        ('2011-06-30', -100000),
        ('2011-09-15', 5000),
        ('2011-11-28', 10000),
        ('2012-01-31', 15000),
        ('2012-03-17', 20000),
        ('2012-06-18', 25000),
        ('2012-08-15', 30000),
        ('2012-12-31', 0)
) n (cfdate, cfamt);

This produces the following result.

FV
2999.07405063237