XFV
Updated 2023-10-09 12:51:51.123000
Syntax
SELECT [westclintech].[wct].[XFV](
<@StartDate, datetime,>
,<@CashflowDate, datetime,>
,<@EndDate, datetime,>
,<@CashflowRate, float,>
,<@EndRate, float,>
,<@Cashflow, float,>)
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.
Arguments
@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.
@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.
@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.
@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.
@CashFlow
the cash flow value. @CashFlow 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.
{"columns":[{"field":"FV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"FV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"FV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"SUMFV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"SUMFV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"XNFV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"SUMFV":"5189.23863529536","XNFV":"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.
{"columns":[{"field":"StartDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CashFlowDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"EndDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CashFlowRate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"EndRate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"cf","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"FV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"StartDate":"31 Jan 2011","CashFlowDate":"31 Jan 2011","EndDate":"31 Jan 2013","CashFlowRate":"0","EndRate":"0.00605","cf":"-10000","FV":"-10121.5332860383"},{"StartDate":"31 Jan 2011","CashFlowDate":"28 Feb 2011","EndDate":"31 Jan 2013","CashFlowRate":"0.00142","EndRate":"0.00605","cf":"2000","FV":"2024.08631484058"},{"StartDate":"31 Jan 2011","CashFlowDate":"31 May 2011","EndDate":"31 Jan 2013","CashFlowRate":"0.00149024760746661","EndRate":"0.00605","cf":"1500","FV":"1517.48688022446"},{"StartDate":"31 Jan 2011","CashFlowDate":"30 Jun 2011","EndDate":"31 Jan 2013","CashFlowRate":"0.00157012464555445","EndRate":"0.00605","cf":"3000","FV":"3034.50285720946"},{"StartDate":"31 Jan 2011","CashFlowDate":"31 Oct 2011","EndDate":"31 Jan 2013","CashFlowRate":"0.00209416902047763","EndRate":"0.00605","cf":"3800","FV":"3840.16928670732"},{"StartDate":"31 Jan 2011","CashFlowDate":"29 Feb 2012","EndDate":"31 Jan 2013","CashFlowRate":"0.00262753313860248","EndRate":"0.00605","cf":"2500","FV":"2523.22595928291"},{"StartDate":"31 Jan 2011","CashFlowDate":"29 Feb 2012","EndDate":"31 Jan 2013","CashFlowRate":"0.00262753313860248","EndRate":"0.00605","cf":"2500","FV":"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.
{"columns":[{"field":"SUMFV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"FV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"FV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"FV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"FV":"2999.07405063237"}]}