Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server schedule of discounted cash flows


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

 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service