Updated: 07 February 2011

Use 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.

SELECT [wctFinancial].[wct].[XFV](

<@StartDate, datetime,>

,<@CashflowDate, datetime,>

,<@EndDate, datetime,>

,<@CashflowRate, float,>

,<@EndRate, float,>

,<@Cashflow, float,>)

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**.

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**.

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**.

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**.

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

the cash flow value. *@CashFlow* is an expression of type **float** or of a type that can be implicitly converted to **float**.

float

Â· 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.

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

(1 row(s) affected)

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

(1 row(s) affected)

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

(1 row(s) affected)

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

(1 row(s) affected)

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

(1 row(s) affected)

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

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

GO

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

GO

DROP TABLE #r

GO

This produces the following result.

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

GO

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

GO

DROP TABLE #r

GO

This produces the following result.

SUMFV

----------------------

5341.16397150935

(1 row(s) affected)

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

(1 row(s) affected)

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

(1 row(s) affected)

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

(1 row(s) affected)