AMORTIZECASHFLOWS
Updated: 13 January 2013
Use the table-valued function AMORTIZECASHFLOWS to generate a schedule showing the discounted cash flow value of a series of cash flows at each cash flow date. AMORTIZECASHFLOWS automatically calculates the Internal Rate of Return (IRR) for the cash flows. It will return the discounted cash flow value as at each date, using the IRR, for all cash flows greater than cash flow date for that row.
Syntax
SELECT * FROM [wctFinancial].[wct].[AMORTIZECASHFLOWS](
<@CashFlows_RangeQuery, nvarchar(max),>)
Arguments
@CashFlow_RangeQuery
a SELECT statement, as a string, which will return the cash-flow amounts and cash-flow dates to be used in the function.
Return Type
RETURNS TABLE (
[num_pmt] [int] NULL,
[date_pmt] [datetime] NULL,
[pv_begin] [float] NULL,
[amt_cashflow] [float] NULL,
[pv_end] [float] NULL
Remarks
· num_pmt is calculated in date order.
· If the internal rate of return cannot be calculated, no rows are returned.
· The time component of the date values are ignored.
· Only 1 row is returned for each date. Multiple cash flows for the same date are summed into amt_cashflow
· pv_begin is the pv_end from the previous row; i.e. the row where num_pmt is 1 less than the current row.
· pv_end is the discounted cash flow value of all the cash flows having a cash flow date greater than the current row. The cash flows are discounted to date_pmt.
Example
We will use the following cash flows in this example.
|
cash flow
|
date
|
|
-1350
|
2013-01-15
|
|
18.89
|
2013-04-15
|
|
18.89
|
2013-07-15
|
|
18.89
|
2013-10-15
|
|
18.89
|
2014-01-15
|
|
18.89
|
2014-04-15
|
|
18.89
|
2014-07-15
|
|
18.89
|
2014-10-15
|
|
1418.89
|
2015-01-15
|
SELECT
num_pmt,
date_pmt,
pv_begin,
amt_cashflow,
pv_end
FROM wct.AmortizeCashFlows(
'SELECT
cf,
cast(CAST(d as varchar(max)) as datetime)
FROM (
VALUES
(20130115,-1350)
,(20130415,18.89)
,(20130715,18.89)
,(20131015,18.89)
,(20140115,18.89)
,(20140415,18.89)
,(20140715,18.89)
,(20141015,18.89)
,(20150115,1418.89)
)n(d,cf)'
)
This produces the following result, which has been reformatted for ease of viewing.
|
num_pmt
|
date_pmt
|
pv_begin
|
amt_cashflow
|
pv_end
|
|
0
|
2013-01-15
|
0
|
-1350
|
1350
|
|
1
|
2013-04-15
|
1350
|
18.89
|
1355.521033
|
|
2
|
2013-07-15
|
1355.521033
|
18.89
|
1361.416716
|
|
3
|
2013-10-15
|
1361.416716
|
18.89
|
1367.69627
|
|
4
|
2014-01-15
|
1367.69627
|
18.89
|
1374.091919
|
|
5
|
2014-04-15
|
1374.091919
|
18.89
|
1380.048588
|
|
6
|
2014-07-15
|
1380.048588
|
18.89
|
1386.392758
|
|
7
|
2014-10-15
|
1386.392758
|
18.89
|
1393.134063
|
|
8
|
2015-01-15
|
1393.134063
|
1418.89
|
0
|
In this example, we put the cash flows into a table and select them from the table.
SELECT
cf as amt_cf,
cast(CAST(d as varchar(max)) as datetime) as date_cf
INTO
#t
FROM (
VALUES
(20130115,-1350)
,(20130415,18.89)
,(20130715,18.89)
,(20131015,18.89)
,(20140115,18.89)
,(20140415,18.89)
,(20140715,18.89)
,(20141015,18.89)
,(20150115,1418.89)
)n(d,cf)
SELECT
*
FROM
wct.AMORTIZECASHFLOWS(
'SELECT
amt_cf,
date_cf
FROM
#t'
)
This produces the following result.
|
num_pmt
|
date_pmt
|
pv_begin
|
amt_cashflow
|
pv_end
|
|
0
|
2013-01-15
|
0
|
-1350
|
1350
|
|
1
|
2013-04-15
|
1350
|
18.89
|
1355.521033
|
|
2
|
2013-07-15
|
1355.521033
|
18.89
|
1361.416716
|
|
3
|
2013-10-15
|
1361.416716
|
18.89
|
1367.69627
|
|
4
|
2014-01-15
|
1367.69627
|
18.89
|
1374.091919
|
|
5
|
2014-04-15
|
1374.091919
|
18.89
|
1380.048588
|
|
6
|
2014-07-15
|
1380.048588
|
18.89
|
1386.392758
|
|
7
|
2014-10-15
|
1386.392758
|
18.89
|
1393.134063
|
|
8
|
2015-01-15
|
1393.134063
|
1418.89
|
0
|