EPV
Updated: 07 February 2011
Use EPV to calculate the discounted value of a cash flow between two periods.
Syntax
SELECT [wctFinancial].[wct].[EPV](
<@Rate, float,>
,<@StartPer, float,>
,<@EndPer, float,>
,<@Cashflow, float,>)
Arguments
@Rate
the interest rate per period. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@StartPer
the starting period for purposes of calculating the discounted cash flow value. @StartPer is an expression of type float or of a type that can be implicitly converted to float.
@EndPer
the ending period for purposes of calculating the discounted cash flow value. @EndPer is an expression of type float or of a type that can be implicitly converted to float.
@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, EPV will return a NULL.
· EPV allows positive and negative values for @Rate .
· @Rate should be consisted with units used for @StartPer and @EndPer. If @StartPer and @EndPer refer to months, then @Rate should be the monthly rate; if @StartPer and @EndPer refer to quarters, then @Rate should be the quarterly rate; if @StartPer and @EndPer refer to weeks, then @Rate should be the weekly rate.
· The @Rate should be the interest rate from @StartPer to @EndPer. 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 @StartPer = @EndPer, then @CashFlow is returned.
· If @Rate = 0, then @CashFlow is returned.
· To calculate a discounted cash flow value using dates, try the XPV 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.EPV(
.01/12 --@Rate
, 0 --@StartPer
, 1 --@EndPer
,10000 --@CashFlow
) as PV
This produces the following result.
PV
----------------------
9991.67693311472
(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.EPV(
.02/12 --@Rate
, 0 --@StartPer
, 12 --@EndPer
,10000 --@CashFlow
) as PV
This produces the following result.
PV
----------------------
9802.22820663997
(1 row(s) affected)
Note, that this is not the same result as using the annual rate and specifying the period as 1 year instead of twelve months.
SELECT wct.EPV(
.02 --@Rate
, 0 --@StartPer
, 1 --@EndPer
,10000 --@CashFlow
) as PV
This produces the following result
PV
----------------------
9803.92156862745
(1 row(s) affected)
This is due to the nature of the compounding effect in the discounted cash flow calculation. In the case where we specified the rate as the annual rate divided by 12 and discounted for 12 periods, the discount factor reflected that monthly compounding. When we used the annual rate and discounted for one period, the discount factor reflected annual compounding. To achieve the same result as the annual compounding, we could have entered the following SQL.
SELECT wct.EPV(
POWER(cast(1.02 as float), cast(1 as float)/cast(12 as float)) - 1
, 0
, 12
, 10000
) as PV
This produces the following result
PV
----------------------
9803.92156862745
(1 row(s) affected)
The EPV function can be used in conjunction with the SUM function to calculate the Net Present Value for 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%
SELECT SUM(wct.EPV(.025/12, 0,(yr*12 + mth) -(2011 * 12) - 1, 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
----------------------
5048.41529972887
(1 row(s) affected)
This is exactly the same result that we would obtain by using the ENPV function.
SELECT SUM(wct.EPV(.025/12, 0,(yr*12 + mth) -(2011 * 12) - 1, CF)) as SUMPV
,wct.ENPV(.025/12, cf,(yr*12 + mth) -(2011 * 12)) as ENPV
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 ENPV
---------------------- ----------------------
5048.41529972887 5048.41529972887
(1 row(s) affected)
Unlike the aggregate ENPV function, however, by using the SUM and EPV functions, it is possible to calculate the present value of the cash flows using different rates for each period. 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 period. For illustration purpose, this SQL will show the calculation for each period. Remember, that the annual rates have to be turned into monthly rates. For purposes of this example, we do that by dividing the annual rates by 12.SELECT *
,wct.EPV(Rate, 0, per, cf) as PV
FROM (
SELECT wct.SPLINE_q('SELECT *
FROM (
VALUES
(0, 0),
(1, 0.142),
(3, 0.160),
(6, 0.173),
(12, 0.246),
(24, 0.605),
(36, 1.008)
) r (per, annrate)'
,(yr*12 + mth) -(2011 * 12) - 1) / 12.000000 as Rate
,(yr*12 + mth) -(2011 * 12) - 1 as per
,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 per CF PV
---------------------- ----------- ----------- ----------------------
0 0 -10000 -10000
0.0118333333333333 1 2000 1976.61011365508
0.0125803977465554 4 1500 1426.83316705619
0.0132143487409217 5 3000 2809.40659367638
0.0173449781103645 9 3800 3255.13785710192
0.0220103811089712 13 2500 1883.74024049593
0.0220103811089712 13 2500 1883.74024049593
(7 row(s) affected)
And when we use the SUM function.
SELECT SUM(wct.EPV(Rate, 0, per, cf)) as SUMPV
FROM (
SELECT wct.SPLINE_q('SELECT *
FROM (
VALUES
(0, 0),
(1, 0.142),
(3, 0.160),
(6, 0.173),
(12, 0.246),
(24, 0.605),
(36, 1.008)
) r (per, annrate)'
,(yr*12 + mth) -(2011 * 12) - 1) / 12.000000 as Rate
,(yr*12 + mth) -(2011 * 12) - 1 as per
,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.
SUMPV
----------------------
3235.46821248144
(1 row(s) affected)
One of the more interesting aspects of the EPV function is that we can combine it with the YEARFRAC function, since periods do not have to be integers. For example, we could use the EPV function to calculate cash flows for the following data.
SELECT SUM(wct.EPV(.03,0,wct.YEARFRAC('07-Feb-11', cf_date,3), cf_amt)) as EPV
FROM (
VALUES
('07-Feb-2011', -82500),
('31-Mar-2017', 1250),
('30-Sep-2017',1250),
('31-Mar-2018', 1250),
('30-Sep-2018',1250),
('31-Mar-2019', 1250),
('30-Sep-2019',1250),
('31-Mar-2020', 1250),
('30-Sep-2020',101250)
)n(cf_date, cf_amt)
This produces the following result.
EPV
----------------------
600.79936009321
(1 row(s) affected)
The 3% rate is the annual rate, since the periods that YEARFRAC are calculation are annual periods. Since we used a basis of 3 in the YEARFRAC calculation, this is exactly the same calculation as the XPV calculation.
SELECT SUM(wct.EPV(.03,0,wct.YEARFRAC('07-Feb-11', cf_date,3), cf_amt)) as EPV
,SUM(wct.XPV(.03, '07-Feb-11', cf_date, cf_amt)) as XPV
FROM (
VALUES
('07-Feb-2011', -82500),
('31-Mar-2017', 1250),
('30-Sep-2017',1250),
('31-Mar-2018', 1250),
('30-Sep-2018',1250),
('31-Mar-2019', 1250),
('30-Sep-2019',1250),
('31-Mar-2020', 1250),
('30-Sep-2020',101250)
)n(cf_date, cf_amt)
This produces the following result.
EPV XPV
---------------------- ----------------------
600.79936009321 600.79936009321
(1 row(s) affected)
But, since we are using the YEARFRAC function, we can actually change the interest basis used in the discounting process to any of the bases supported by YEARFRAC (actual/actual, BOND, actual/360, actual/365, and EBOND). For example, if we wanted to calculate the net present values using the actual/actual basis, we could enter the following SQL.
SELECT SUM(wct.EPV(.03,0,wct.YEARFRAC('07-Feb-11', cf_date,1), cf_amt)) as EPV
FROM (
VALUES
('07-Feb-2011', -82500),
('31-Mar-2017', 1250),
('30-Sep-2017',1250),
('31-Mar-2018', 1250),
('30-Sep-2018',1250),
('31-Mar-2019', 1250),
('30-Sep-2019',1250),
('31-Mar-2020', 1250),
('30-Sep-2020',101250)
)n(cf_date, cf_amt)
This produces the following result.
EPV
----------------------
619.749402368514
(1 row(s) affected)