IRR
Updated 2023-10-09 13:06:06.433000
Syntax
SELECT [westclintech].[wct].[IRR] (
<@CF, float,>
,<@Per, int),>
,<@Guess, float,>)
Description
Use the aggregate function IRR to calculate an internal rate of return for a series of cash flows.
Arguments
@Guess
a user-supplied suggestion as to a rate of return to use as a starting point in the iteration calculations. If you do not wish to supply a guess, make this argument NULL. @Guess is an expression of type float or of a type that can be implicitly converted to float.
@CF
the cash flow amounts. @CF_Amt is an expression of type float or of a type that can be implicitly converted to float.
@Per
the period in which the cash flow occurred. @Per is an expression of type int or of a type that can be implicitly converted to int.
Return Type
float
Remarks
The IRR aggregate function requires a series of cash flows (@CF) and the periods in which those cash flows occurred (@Per) as input. As a result, the order of the cash flows is not important.
Periods in which the cash flow is zero, or in which there is no cash flow, do not have to be included.
The periods can start and end with any integer value, including negative numbers.
The guess (@Guess) must remain constant for the GROUP of the aggregate.
IRR and NPV are related in that the IRR is solving for the value that makes the cash flows as sent to NPV equal to zero.
IRR solves for NPV approximately equal to zero, iteratively with a maximum of 100 iterations. If IRR fails to resolve to zero within the maximum number of iterations, it will return a NULL.
IRR requires that there be at least one period with a negative cash flow and one period with a positive cash flow, otherwise it will return a NULL.
If you want to calculate the internal rate of return using dates rather than periods, use the XIRR function.
Just as in EXCEL, the @Guess value can change the result for cash flow datasets with multiple solutions. ## Examples In this example we do not have cash flows occurring in every period and we have two cash flows occurring in period 14.
SELECT wct.IRR(cf_amt, per, NULL) as IRR
FROM
(
VALUES
(-10000, 1),
(2000, 2),
(1500, 5),
(3000, 6),
(3800, 10),
(2500, 14),
(2500, 14)
) n (cf_amt, per);
This produces the following result.
{"columns":[{"field":"IRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"IRR":"0.05861997506787"}]}
If we use the IRR value in the NPV function, it returns a value sufficiently close to zero out to 6 decimal places.
SELECT ROUND(wct.NPV(0.05861997506787, cf_amt, per), 6) as NPV
FROM
(
VALUES
(-10000, 1),
(2000, 2),
(1500, 5),
(3000, 6),
(3800, 10),
(2500, 14),
(2500, 14)
) n (cf_amt, per);
This produces the following result.
{"columns":[{"field":"NPV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"NPV":"0"}]}
If we wanted to calculate the NPV using the IRR calculated values, without having to copy the IRR calculated values into the SQL, the SQL is little more complicated, since AGGREGATE functions cannot contain other AGGREGATE functions. If we tried to do something like this:
SELECT wct.NPV(wct.IRR(cf_amt, per, NULL), cf_amt, per) as NPV
FROM
(
VALUES
(-10000, 1),
(2000, 2),
(1500, 5),
(3000, 6),
(3800, 10),
(2500, 14),
(2500, 14)
) n (cf_amt, per);
We will get the following error.
Msg 130, Level 15, State 1, Line 1Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
So, we need to try something like this.
;with mycte
as (SELECT *
FROM
(
VALUES
(-10000, 1),
(2000, 2),
(1500, 5),
(3000, 6),
(3800, 10),
(2500, 14),
(2500, 14)
) n (cf_amt, per) ),
mycte2 (rate, cf_amt, per)
as (SELECT wct.IRR(cf_amt, per, NULL),
0,
0
from MYCTE
UNION ALL
SELECT NULL,
cf_amt,
per
FROM MYCTE)
SELECT m1.rate as IRR,
ROUND(wct.NPV(m1.rate, m2.cf_amt, m2.per), 6) as NPV
FROM mycte2 m1,
mycte2 m2
WHERE m1.rate is NOT NULL
and m2.rate is NULL
GROUP BY m1.rate;
This produces the following result.
{"columns":[{"field":"IRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"NPV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"IRR":"0.05861997506787","NPV":"0"}]}
In this example, we will use the same cash flows as the previous example, with the same intervals between the cash flows, but instead of starting at period 1, we will start at period 101.
SELECT wct.IRR(cf_amt, per, NULL) as IRR
FROM
(
VALUES
(-10000, 101),
(2000, 102),
(1500, 105),
(3000, 106),
(3800, 110),
(2500, 114),
(2500, 114)
) n (cf_amt, per);
This produces the following result.
{"columns":[{"field":"IRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"IRR":"0.05861997506787"}]}
In this example, we will use the same cash flows, with the same spacing among periods, but we will use negative period numbers and the cash flows will not be in order in the derived table.
SELECT wct.IRR(cf_amt, per, NULL) as IRR
FROM
(
VALUES
(2500, -86),
(2500, -86),
(3800, -90),
(3000, -94),
(1500, -95),
(2000, -98),
(-10000, -99)
) n (cf_amt, per);
This produces the following result.
{"columns":[{"field":"IRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"IRR":"0.05861997506787"}]}
In this example, the period numbers are not stored in a table, but have to be calculated as an input into the function. We will store the cash flows by year and month.
SELECT wct.IRR(cf, yr * 12 + mth, NULL) as IRR
FROM
(
VALUES
(2011, 1, -10000),
(2011, 2, 2000),
(2011, 5, 1500),
(2011, 6, 3000),
(2011, 10, 3800),
(2012, 2, 2500),
(2012, 2, 2500)
) n (yr, mth, cf);
This produces the following result.
{"columns":[{"field":"IRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"IRR":"0.05861997506787"}]}
In this example, we have positive and negative cash flows, but there is no period with a net negative cash flow, so the result of the calculation is NULL.
SELECT wct.IRR(cf, per, NULL) as IRR
FROM
(
VALUES
(-1000, 1),
(-2000, 3),
(-4000, 5),
(5000, 6),
(1000, 1),
(2000, 3),
(4000, 5),
(11000, 11)
) n (cf, per);
{"columns":[{"field":"IRR"}],"rows":[{"IRR":"NULL"}]}
In this example, we will add another column to the derived table from the previous example, which will allow for a different guesses for each period. This will return an error message, as the function requires that the guess value be the same for all cash flows.
SELECT wct.IRR(cf, yr * 12 + mth, guess) as IRR
FROM
(
VALUES
(2011, 1, -10000, .0075),
(2011, 2, 2000, .0075),
(2011, 5, 1500, .0075),
(2011, 6, 3000, .0075),
(2011, 10, 3800, .0075),
(2012, 2, 2500, .0080),
(2012, 2, 2500, .0080)
) n (yr, mth, cf, guess);
This produces the following message and will not return a result.
Msg 6522, Level 16, State 2, Line 1A .NET Framework error occurred during execution of user-defined routine or aggregate "IRR":Guess cannot vary from its original value of 0.0075
IRR is an AGGREGATE function and supports all the syntax available to any SQL Server AGGREGATE function. Here is an example using a GROUP BY.
SELECT port,
wct.IRR(cf, yr * 12 + mth, NULL) as IRR
FROM
(
VALUES
('ABCD', 2009, 1, 3497),
('ABCD', 2009, 2, -3358),
('ABCD', 2009, 3, 146),
('ABCD', 2009, 4, -8530),
('ABCD', 2009, 5, -6005),
('ABCD', 2009, 6, -6849),
('ABCD', 2009, 7, 6315),
('ABCD', 2009, 8, 3728),
('ABCD', 2009, 9, 8471),
('ABCD', 2009, 10, 172),
('ABCD', 2009, 11, -4663),
('ABCD', 2009, 12, -7713),
('ABCD', 2010, 1, 8536),
('ABCD', 2010, 2, 6351),
('ABCD', 2010, 3, 9300),
('ABCD', 2010, 4, 8345),
('ABCD', 2010, 5, 6720),
('ABCD', 2010, 6, -6088),
('ABCD', 2010, 7, 7212),
('ABCD', 2010, 8, 2428),
('ABCD', 2010, 9, 2023),
('ABCD', 2010, 10, -4976),
('ABCD', 2010, 11, 9237),
('ABCD', 2010, 12, -812),
('EFGH', 2009, 1, -2774),
('EFGH', 2009, 2, -2400),
('EFGH', 2009, 3, 9774),
('EFGH', 2009, 4, -5442),
('EFGH', 2009, 5, 499),
('EFGH', 2009, 6, 4190),
('EFGH', 2009, 7, -9722),
('EFGH', 2009, 8, 7384),
('EFGH', 2009, 9, -7862),
('EFGH', 2009, 10, 7119),
('EFGH', 2009, 11, -9999),
('EFGH', 2009, 12, 7275),
('EFGH', 2010, 1, -5450),
('EFGH', 2010, 2, 7129),
('EFGH', 2010, 3, 4492),
('EFGH', 2010, 4, -1588),
('EFGH', 2010, 5, -7667),
('EFGH', 2010, 6, -6398),
('EFGH', 2010, 7, 7388),
('EFGH', 2010, 8, 5962),
('EFGH', 2010, 9, -1882),
('EFGH', 2010, 10, -9719),
('EFGH', 2010, 11, 7783),
('EFGH', 2010, 12, 1326),
('IJKL', 2009, 1, -9360),
('IJKL', 2009, 2, 6233),
('IJKL', 2009, 3, 6496),
('IJKL', 2009, 4, 5031),
('IJKL', 2009, 5, -2620),
('IJKL', 2009, 6, -6014),
('IJKL', 2009, 7, -9232),
('IJKL', 2009, 8, -4584),
('IJKL', 2009, 9, -2909),
('IJKL', 2009, 10, 3169),
('IJKL', 2009, 11, 7171),
('IJKL', 2009, 12, -4596),
('IJKL', 2010, 1, 1249),
('IJKL', 2010, 2, 9354),
('IJKL', 2010, 3, 4577),
('IJKL', 2010, 4, -3013),
('IJKL', 2010, 5, 6318),
('IJKL', 2010, 6, -1721),
('IJKL', 2010, 7, -417),
('IJKL', 2010, 8, 4610),
('IJKL', 2010, 9, -1557),
('IJKL', 2010, 10, -5207),
('IJKL', 2010, 11, 1145),
('IJKL', 2010, 12, 8832)
) n (port, yr, mth, cf)
GROUP BY port;
This produces the following result.
{"columns":[{"field":"port"},{"field":"IRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"port":"ABCD","IRR":"0.125642583356821"},{"port":"EFGH","IRR":"-0.012180899476392"},{"port":"IJKL","IRR":"0.080947665910384"}]}