XNPVT

Updated 2024-02-29 21:37:16.337000

Syntax

SELECT [westclintech].[wct].[XNPVT](
  <@Disc_rate, float,>
 ,<@CF_Amt, float,>
 ,<@Time, float,>

Description

Use the aggregate function XNPVT to calculate the net present value for a series of cash flows with irregular time periods—cash flows of varying amount occurring at various points in time. The net present value is calculated as:

\rm{XNPVT=\sum_{i=1}^nc_i\times{r^{-t_i}}}

WhereN = the number of cash flowsci = cash flow amountti = timer = 1 + discount rate

Arguments

@CF_Amt

the cash flow amounts to be used in the calculation. @CF_Amt is an expression of type float or of a type that can be implicitly converted to float.

@Time

the time (expressed in periods) associated with the @CF_Amt. @Time is an expression of type float or of a type that can be implicitly converted to float.

@Disc_rate

the rate to be used in the calculation. @Disc_rate is an expression of type float or of a type that can be implicitly converted to float.

Return Type

float

Remarks

@CF_Amt and @Time are passed in as pairs, but they can be passed into the function in any order.

The @Disc_rate must remain constant for the GROUP of the aggregate.

@Disc_rate should be in the same units as @Time.

Examples

In this example, we demonstrate the similarity between the XNPV function and the XNPVT function. This requires using the YEARFRAC function to calculate @Time setting the basis parameter = 3.

SELECT wct.XNPV(.06, amt_cf, date_cf) AS XNPV,

       wct.XNPVT(.06, amt_cf, wct.YEARFRAC('2011-11-30', date_cf, 3)) as XNPVT

FROM

(

    VALUES

        (-100000, '2011-11-30'),

        (-50000, '2012-03-15'),

        (-2500, '2012-07-18'),

        (12500, '2012-11-30'),

        (37500, '2013-01-23'),

        (75000, '2013-04-30'),

        (90000, '2014-02-06')

) n (amt_cf, date_cf);

This produces the following result.

{"columns":[{"field":"XNPV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"XNPVT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"XNPV":"43568.8517245775","XNPVT":"43568.8517245775"}]}

In this example, we calculate the net present value using the actual/actual day-count convention rather than an actual/365 day-count convention.

SELECT wct.XNPVT(.06, amt_cf, wct.YEARFRAC('2011-11-30', date_cf, 1)) as XNPVT

FROM

(

    VALUES

        (-100000, '2011-11-30'),

        (-50000, '2012-03-15'),

        (-2500, '2012-07-18'),

        (12500, '2012-11-30'),

        (37500, '2013-01-23'),

        (75000, '2013-04-30'),

        (90000, '2014-02-06')

) n (amt_cf, date_cf);

This produces the following result.

{"columns":[{"field":"XNPVT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"XNPVT":"43582.4812123601"}]}

In this example, we calculate the net present value using NL/365 day-count convention, where leap years are counted as having 365 days.

SELECT wct.XNPVT(.06, amt_cf, wct.DAYSNL('2011-11-30', date_cf) / cast(365 as float)

          ) as XNPVT

FROM

(

    VALUES

        (-100000, '2011-11-30'),

        (-50000, '2012-03-15'),

        (-2500, '2012-07-18'),

        (12500, '2012-11-30'),

        (37500, '2013-01-23'),

        (75000, '2013-04-30'),

        (90000, '2014-02-06')

) n (amt_cf, date_cf);

This produces the following result.

{"columns":[{"field":"XNPVT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"XNPVT":"43591.7730068018"}]}

In this example we use the T360 function to calculate the net present value using a 30/360 day-count convention and assuming semi-annual compounding.

SELECT wct.XNPVT(SQRT(1.06) - 1, amt_cf, wct.T360('2014-02-06', '2011-11-30', 

          date_cf, 2, 0)) as XNPVT

FROM

(

    VALUES

        (-100000, '2011-11-30'),

        (-50000, '2012-03-15'),

        (-2500, '2012-07-18'),

        (12500, '2012-11-30'),

        (37500, '2013-01-23'),

        (75000, '2013-04-30'),

        (90000, '2014-02-06')

) n (amt_cf, date_cf);

This produces the following result.

{"columns":[{"field":"XNPVT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"XNPVT":"43606.9053002285"}]}

Unlike the XNPV function where cash flows are discounted to the earliest date in the dataset, XNPVT supports discounting the cash flows to any arbitrary date, much like the XDCF function.

SELECT wct.XNPVT(.06, amt_cf, wct.YEARFRAC('2011-06-30', date_cf, 3)) as XNPVT,

       wct.XDCF(.06, '2011-06-30', amt_cf, date_cf) as XDCF

FROM

(

    VALUES

        (-100000, '2011-11-30'),

        (-50000, '2012-03-15'),

        (-2500, '2012-07-18'),

        (12500, '2012-11-30'),

        (37500, '2013-01-23'),

        (75000, '2013-04-30'),

        (90000, '2014-02-06')

) n (amt_cf, date_cf);

This produces the following result.

{"columns":[{"field":"XNPVT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"XDCF","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"XNPVT":"42517.5714299942","XDCF":"42517.5714299942"}]}

See Also

ENPV - Net Present Value for irregular periodic cash flows

NPV - Net Present Value for irregular periodic cash flows using the Excel definition

ODDFPV - Present Value of an annuity with an odd first period

PV - Present value of an annuity

XDCF - Discounted cash flow value

XIRRT - Internal rate of return for cash flows discounted using XNPVT

XNFV - Net Future Value for irregular cash flows

XNPV - Net Present Value for irregular cash flows

XNPV30360 - Net Present Value for irregular cash flows using a 30/360 day-count convention

XPV - Calculate the discounted value of a cash flow between two dates.