XPV
Updated: 07 February 2011
Use XPV to calculate the discounted value of a cash flow between two dates.
Syntax
SELECT [wctFinancial].[wct].[XPV](
<@Rate, float,>
,<@StartDate, datetime,>
,<@EndDate, datetime,>
,<@Cashflow, float,>)
Arguments
@Rate
the annual interest rate. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@StartDate
the starting date for purposes of calculating the discounted cash flow value. @Startdate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@EndDate
the ending period for purposes of calculating the discounted cash flow value. @EndDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@CashFlow
the cash flow value to be discounted. @CashFlow is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
· The discounted cash flow value will have the same sign as the cash flow amount (@CashFlow).
· If the @Rate is equal to -1, XPV will return a NULL.
· XPV allows positive and negative values for @Rate.
· @Rate is the annual rate of interest.
· The @Rate should be the annual interest rate from @StartDate to @EndDate. For example, if the start period is in 3 months time and the end period is in six months time, the rate should be the 3-month rate in three months time (also known as the forward/forward rate).
· If @StartDate = @EndDate, then @CashFlow is returned.
· If @Rate = 0, then @CashFlow is returned.
· To calculate a discounted cash flow value using periods, try the EPV function.
Examples
Calculate the present value of a cash flow to be received in one month, with the nominal one month rate at 1%.
SELECT wct.XPV(
.01 --@Rate
,'02/01/2011' --@StartDate
,'03/01/2011' --@EndDate
,10000 --@CashFlow
) as PV
This produces the following result.
PV
----------------------
9992.36978197548
(1 row(s) affected)
Calculate the present value of a cash flow to be received in twelve months, with the nominal one year rate at 2%.
SELECT wct.XPV(
.02 --@Rate
,'02/01/2011' --@StartPer
,'02/01/2012' --@EndPer
,10000 --@CashFlow
) as PV
This produces the following result.
PV
----------------------
9803.92156862745
(1 row(s) affected)
The XPV function can be used in conjunction with the SUM function to calculate the Net Present Value of a collection of cash flows. Let’s say we had the following monthly cash flows and cash flow periods, and we wanted to discount all 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 day 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.XPV(.025
,'01/31/2011'
,wct.EOMONTH(wct.CALCDATE(yr, mth, 1), 0)
,CF
)) as SUMPV
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.
SUMPV
----------------------
5052.74922738005
(1 row(s) affected)
This is exactly the same result that we would obtain by using the XNPV function.
SELECT
SUM(wct.XPV(.025
,'01/31/2011'
,wct.EOMONTH(wct.CALCDATE(yr, mth, 1), 0)
,CF
)) as SUMPV
,wct.XNPV(.025
,cf
,wct.EOMONTH(wct.CALCDATE(yr, mth, 1), 0)
) as XNPV
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.
SUMPV XNPV
---------------------- ----------------------
5052.74922738005 5052.74922738005
(1 row(s) affected)
Unlike the aggregate XNPV function, however, by using the SUM and XPV 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 to using the appropriate interpolated rate for the each period. For illustration purpose, this SQL will show the calculation for each period.SELECT Rate
,CONVERT(varchar(10), cfdate,106) as cdate
,cf
,wct.XPV(Rate, wct.CALCDATE(2011,1,31), cfdate, cf) as PV
FROM (
SELECT 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(wct.CALCDATE(yr, mth, 1), 0) as float)) / 100 as Rate
,wct.EOMONTH(wct.CALCDATE(yr, mth, 1), 0) as cfdate
,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)
) m
This produces the following result.
Rate cdate cf PV
---------------------- ---------- ----------- ----------------------
0 31 Jan 201 -10000 -10000
0.00142 28 Feb 201 2000 1999.78230337158
0.00149024760746661 31 May 201 1500 1499.26581016899
0.00157012464555445 30 Jun 201 3000 2998.06637142149
0.00209416902047763 31 Oct 201 3800 3794.05884284792
0.00262753313860248 29 Feb 201 2500 2492.92857907553
0.00262753313860248 29 Feb 201 2500 2492.92857907553
(7 row(s) affected)
And when we use the SUM function.
SELECT SUM(wct.XPV(Rate, wct.CALCDATE(2011,1,31), cfdate, cf)) as PV
FROM (
SELECT 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(wct.CALCDATE(yr, mth, 1), 0) as float)) / 100 as Rate
,wct.EOMONTH(wct.CALCDATE(yr, mth, 1), 0) as cfdate
,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)
) m
This produces the following result.
PV
----------------------
5277.03048596104
(1 row(s) affected)
One more interesting way to use the XPV function is in conjunction with the XNPV function. The XNPV function calculates the present value of all the cash flows passed to the calculation using the minimum 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 commence on 30-Jun-11. The XNPV calculation will reflect the XNPV as of 30-Jun-11. You can try to add another cashflow to the data for 01-Feb-11 with an amount of zero, but in many cases it might be easier to use XPV to discount from the 30-Jun-11 to 01-Feb-11.
SELECT wct.XNPV(.025,cfamt,cfdate) as PV
FROM (
VALUES
('06/30/2011', -100000),
('09/15/2011', 5000),
('11/28/2011', 10000),
('01/31/2012', 15000),
('03/17/2012', 20000),
('06/18/2012', 25000),
('08/15/2012', 30000)
) n(cfdate, cfamt)
This produces the following result.
PV
----------------------
2889.53487096227
(1 row(s) affected)
To discount the cash flow to 01-Feb-11, we could use the following SQL.
SELECT wct.XPV(.025, '02/01/11','06/30/11', wct.XNPV(.025,cfamt,cfdate)) as PV
FROM (
VALUES
('06/30/2011', -100000),
('09/15/2011', 5000),
('11/28/2011', 10000),
('01/31/2012', 15000),
('03/17/2012', 20000),
('06/18/2012', 25000),
('08/15/2012', 30000)
) n(cfdate, cfamt)
This produces the following result.
PV
----------------------
2860.55467075575
(1 row(s) affected)
Since the rate for XPV and XNPV are the same in this example, this is equivalent to the following SQL.
SELECT wct.XNPV(.025,cfamt,cfdate) as PV
FROM (
VALUES
('06/30/2011', -100000),
('09/15/2011', 5000),
('11/28/2011', 10000),
('01/31/2012', 15000),
('03/17/2012', 20000),
('06/18/2012', 25000),
('08/15/2012', 30000),
('02/01/2011', 0)
) n(cfdate, cfamt)
This produces the following result.
PV
----------------------
2860.55467075575
(1 row(s) affected)