AMORTIZECASHFLOWS

Updated 2023-10-09 13:03:53.843000

Syntax

SELECT * FROM [westclintech].[wct].[AMORTIZECASHFLOWS](
   <@CashFlows_RangeQuery, nvarchar(max),>)

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.

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

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "4f0b2dcb-0e08-4e65-b15c-5af30f830b89", "colName": "num_pmt", "colDatatype": "int", "colDesc": "the number of the payment"}, {"id": "4191294a-5932-4ddc-9091-9f8b37e13433", "colName": "date_pmt", "colDatatype": "datetime", "colDesc": "the date of the payment"}, {"id": "b55c415a-6e84-4763-830c-68d40412a745", "colName": "pv_begin", "colDatatype": "float", "colDesc": "the present value of the cashflows before the payment is applied"}, {"id": "6ab4e1fa-5d5b-405d-ac69-57b21c15aa39", "colName": "amt_cashflow", "colDatatype": "float", "colDesc": "the amount of the cash flow"}, {"id": "c17c9404-5dcf-435c-800e-a2b8769660fd", "colName": "pv_end", "colDatatype": "float", "colDesc": "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.

{"columns":[{"field":"cash flow","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"cash flow":"-1350","date":"2013-01-15"},{"cash flow":"18.89","date":"2013-04-15"},{"cash flow":"18.89","date":"2013-07-15"},{"cash flow":"18.89","date":"2013-10-15"},{"cash flow":"18.89","date":"2014-01-15"},{"cash flow":"18.89","date":"2014-04-15"},{"cash flow":"18.89","date":"2014-07-15"},{"cash flow":"18.89","date":"2014-10-15"},{"cash flow":"1418.89","date":"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.

{"columns":[{"field":"num_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"date_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"pv_begin","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_cashflow","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"pv_end","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"num_pmt":"0","date_pmt":"2013-01-15 00:00:00.000","pv_begin":"0","amt_cashflow":"-1350","pv_end":"1350.00000000319"},{"num_pmt":"1","date_pmt":"2013-04-15 00:00:00.000","pv_begin":"1350.00000000319","amt_cashflow":"18.89","pv_end":"1355.5210328773"},{"num_pmt":"2","date_pmt":"2013-07-15 00:00:00.000","pv_begin":"1355.5210328773","amt_cashflow":"18.89","pv_end":"1361.4167163173"},{"num_pmt":"3","date_pmt":"2013-10-15 00:00:00.000","pv_begin":"1361.4167163173","amt_cashflow":"18.89","pv_end":"1367.69627038986"},{"num_pmt":"4","date_pmt":"2014-01-15 00:00:00.000","pv_begin":"1367.69627038986","amt_cashflow":"18.89","pv_end":"1374.09191939051"},{"num_pmt":"5","date_pmt":"2014-04-15 00:00:00.000","pv_begin":"1374.09191939051","amt_cashflow":"18.89","pv_end":"1380.04858829142"},{"num_pmt":"6","date_pmt":"2014-07-15 00:00:00.000","pv_begin":"1380.04858829142","amt_cashflow":"18.89","pv_end":"1386.39275774477"},{"num_pmt":"7","date_pmt":"2014-10-15 00:00:00.000","pv_begin":"1386.39275774477","amt_cashflow":"18.89","pv_end":"1393.13406303405"},{"num_pmt":"8","date_pmt":"2015-01-15 00:00:00.000","pv_begin":"1393.13406303405","amt_cashflow":"1418.89","pv_end":"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.

{"columns":[{"field":"num_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"date_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"pv_begin","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_cashflow","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"pv_end","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"num_pmt":"0","date_pmt":"2013-01-15 00:00:00.000","pv_begin":"0","amt_cashflow":"-1350","pv_end":"1350.00000000319"},{"num_pmt":"1","date_pmt":"2013-04-15 00:00:00.000","pv_begin":"1350.00000000319","amt_cashflow":"18.89","pv_end":"1355.5210328773"},{"num_pmt":"2","date_pmt":"2013-07-15 00:00:00.000","pv_begin":"1355.5210328773","amt_cashflow":"18.89","pv_end":"1361.4167163173"},{"num_pmt":"3","date_pmt":"2013-10-15 00:00:00.000","pv_begin":"1361.4167163173","amt_cashflow":"18.89","pv_end":"1367.69627038986"},{"num_pmt":"4","date_pmt":"2014-01-15 00:00:00.000","pv_begin":"1367.69627038986","amt_cashflow":"18.89","pv_end":"1374.09191939051"},{"num_pmt":"5","date_pmt":"2014-04-15 00:00:00.000","pv_begin":"1374.09191939051","amt_cashflow":"18.89","pv_end":"1380.04858829142"},{"num_pmt":"6","date_pmt":"2014-07-15 00:00:00.000","pv_begin":"1380.04858829142","amt_cashflow":"18.89","pv_end":"1386.39275774477"},{"num_pmt":"7","date_pmt":"2014-10-15 00:00:00.000","pv_begin":"1386.39275774477","amt_cashflow":"18.89","pv_end":"1393.13406303405"},{"num_pmt":"8","date_pmt":"2015-01-15 00:00:00.000","pv_begin":"1393.13406303405","amt_cashflow":"1418.89","pv_end":"0"}]}