SQL Server AMORTIZECASHFLOWS Function

Updated 2023-10-09 13:03:53.843000

Description

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 [westclintech].[wct].[AMORTIZECASHFLOWS](
   <@CashFlows_RangeQuery, nvarchar(max),>)

Arguments

Return Type

table

colName	colDatatype	colDesc
num_pmt	int	the number of the payment
date_pmt	datetime	the date of the payment
pv_begin	float	the present value of the cashflows before the payment is applied
amt_cashflow	float	the amount of the cash flow
pv_end	float	the present value of the remaining cash flows after the payment has been applied

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.

Examples

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.

num_pmt	date_pmt	pv_begin	amt_cashflow	pv_end
0	2013-01-15 00:00:00.000	0	-1350	1350.00000000319
1	2013-04-15 00:00:00.000	1350.00000000319	18.89	1355.5210328773
2	2013-07-15 00:00:00.000	1355.5210328773	18.89	1361.4167163173
3	2013-10-15 00:00:00.000	1361.4167163173	18.89	1367.69627038986
4	2014-01-15 00:00:00.000	1367.69627038986	18.89	1374.09191939051
5	2014-04-15 00:00:00.000	1374.09191939051	18.89	1380.04858829142
6	2014-07-15 00:00:00.000	1380.04858829142	18.89	1386.39275774477
7	2014-10-15 00:00:00.000	1386.39275774477	18.89	1393.13406303405
8	2015-01-15 00:00:00.000	1393.13406303405	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'
            );