Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

NPV and IRR in SQL Expand / Collapse
Author
Message
Posted Sunday, December 9, 2012 9:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 1,945, Visits: 2,912
Years ago I wrote a piece on the Net Present Value (NPV) function in SQL. If you don't know or remember what this is, pretend you can invest $1000 in one of two projects which will give you varying payoffs over time (quarterly dividends, weekly checks, whatever). If the total value of the payoffs is less than your initial investment, you can stop right there. But if the payoffs are not uniform, you use the NPV to compare which one is the better investment at today's interest rates. Here is an example with a 10% interest rate over four periods. Period zero is the initial investment, so it is always negative.

CREATE TABLE CashFlows
(project_name CHAR(15) NOT NULL,
time_period INTEGER NOT NULL,
CHECK (time_period >= 0),
payment_amt DECIMAL(12,4) NOT NULL,
PRIMARY KEY (project_name, time_period));

INSERT INTO CashFlows
VALUES ('Acme', 0, -1000.0000), '('Acme', 1, 500.0000),
('Acme', 2, 400.0000),('Acme', 3, 200.0000),
('Acme', 4, 200.0000),
('Beta', 0, -1000.0000), ('Beta', 1, 100.0000),
('Beta', 2, 200.0000), ('Beta', 3, 200.0000),
('Beta', 4, 700.0000);

The query is a straight implementation of the NPV formula. I'd like to get some suggestions if this can be improved. This is basically compound interest over a series

SELECT project_name,
CAST(SUM(payment_amt/POWER ((1.00 + .10), time_period))
AS DECIMAL(12,2)) AS npv
FROM Cashflows
GROUP BY project_name;

Acme has an NPV of $72.49 and Beta is worth -$113.97, so Acme is a better deal. The earlier payoff and re-investing at the high interest rate overcome the total value of Beta.

But my real question is how to write the Internal Rate of Return (IRR, which depends on the NPV. It finds the interest rate at which your investment would break even if you invested back into the same project. Thus, if you can get a better rate, this is a good investment.

I originally did it with look-up tables built from the NPV cross joined to a table of interest rates and interpolation. I was never happy with that. Can anyone do better than that today?


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1394442
Posted Monday, December 10, 2012 3:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, August 24, 2014 2:10 PM
Points: 141, Visits: 851
Hi Joe,

I don't claim to have a deep understanding of the mathematics of these functions.
However, a few months ago, I needed to implement a function in SQL that works in the same way that XIRR works in excel.
IRR and XIRR differ in periodicity, fixed vs non-fixed respectively.

I looked up how excel did it internally, and I think excel implements the secant method for root finding (something I honestly don't understand) and found this code somewhere on the web by Ankeet Shah.
After modifying the code for my own purposes (accepting a table valued parameter instead of working against a physical table), I also changed the "search" method to a mid-point search (so that I could actually maintain the code too ). It became less efficient, but it performed within acceptable bounds.

Here is the code, verbatim as I found it on the web.
Perhaps you will find it useful?

CREATE FUNCTION dbo.XIRR_1
(
@d DATETIME,
@GUID VARCHAR(128)
)
RETURNS DECIMAL(18,10)
AS
BEGIN

/*
USAGE: SELECT @IRR = dbo.xirr(null, guid)
SELECT @IRR IRR, @IRR * 100 'IRR %'

Note: Leave the first parameter (date) null if you wish to see the XIRR calculated as
of the maximum date in the dataSET provided else provide a specific date to see
the XIRR calculated as the given date.

Created By: Ankeet Shah
Created On: 7/16/2008

*/
IF @d IS NULL
SELECT @d = MAX(d) FROM IncomeTable

DECLARE @irrPrev FLOAT SET @irrPrev = 0
DECLARE @irr FLOAT SET @irr = -0.1
DECLARE @PresentValuePrev FLOAT
DECLARE @PresentValue FLOAT

SET @PresentValuePrev = ( SELECT SUM(amt) FROM XIRRTempData WHERE guid = @GUID )
SET @PresentValue = (SELECT SUM(amt/POWER(1e0+@irr,CAST(dt-@d as FLOAT)/360)) FROM XIRRTempData WHERE guid = @GUID )

WHILE abs(@PresentValue) >= 0.0001
BEGIN
DECLARE @t FLOAT

SET @t = @irrPrev
SET @irrPrev = @irr
SET @irr = @irr + (@t-@irr)*@PresentValue/(@PresentValue-@PresentValuePrev)
SET @PresentValuePrev = @PresentValue
SET @PresentValue = (SELECT SUM(amt/POWER(1e0+@irr,CAST(dt-@d as FLOAT)/365)) FROM XIRRTempData WHERE guid = @GUID )
END

RETURN @irr
END
GO


Post #1394527
Posted Monday, December 10, 2012 10:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 1,945, Visits: 2,912
>> I don't claim to have a deep understanding of the mathematics of these functions. <<

I am supposed to; my first Masters was Math

>> I looked up how excel did it internally, and I think excel implements the secant method for root finding (something I honestly don't understand) and found this code somewhere on the web by Ankeet Shah. <<

I have a version of this same code, but it uses loops and procedural code and I am hoping for a declarative solution. The secant method can fail if there are two or more roots. Maybe some kind of look-up tables?


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1394702
Posted Monday, December 10, 2012 10:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, August 24, 2014 2:10 PM
Points: 141, Visits: 851
I cannot substantiate this but; I think that any lookup table solution you arrive at would have accuracy issues; where its size gave you accuracy with diminishing returns (size:accuracy)? Perhaps it could give you a "zone" to seek in, but in that case, you wouldn't be eliminating the iterative operation altogether

Please let me know if you find something, even though it will probably go way over my head.
Post #1394707
Posted Monday, December 10, 2012 12:30 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:27 PM
Points: 3,135, Visits: 11,479
CELKO (12/10/2012)
>> I don't claim to have a deep understanding of the mathematics of these functions. <<

I am supposed to; my first Masters was Math

>> I looked up how excel did it internally, and I think excel implements the secant method for root finding (something I honestly don't understand) and found this code somewhere on the web by Ankeet Shah. <<

I have a version of this same code, but it uses loops and procedural code and I am hoping for a declarative solution. The secant method can fail if there are two or more roots. Maybe some kind of look-up tables?


I think that one of the problems with IRR is that it can have more that one solution in cases where the stream of payments is not equal. For example, when it returns $1000 a month for 5 years, and then there is a $100,000 balloon payment at the end.
Post #1394753
Posted Monday, December 10, 2012 6:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 3,627, Visits: 5,270
I am no mathemetician so I rely on Wiki for answers of this sort: http://en.wikipedia.org/wiki/Internal_rate_of_return

Since the formula is recursive, I propose a recursive CTE solution. Not sure if this is a "good" set based alternative or not.

;WITH SumFlows AS (
SELECT project_name
,C0=SUM(ABS(CASE time_period WHEN 0 THEN payment_amt END))
,A=SUM(CASE WHEN time_period <> 0 THEN payment_amt END)
,n=COUNT(payment_amt)
FROM #CashFlows
GROUP BY project_name),
IRR AS (
SELECT time_period, project_name, payment_amt, NPV=payment_amt
,NPV0=CAST(0 AS DECIMAL(12,4))
,A, C0, n, r=CAST(r AS FLOAT), rn0=CAST(0 AS FLOAT)
FROM #CashFlows a
CROSS APPLY (
SELECT A, C0, n, r=POWER(A/C0, 2./n)-1.0
FROM SumFlows b
WHERE a.project_name = b.project_name
) b
WHERE time_period = 0
UNION ALL
SELECT a.time_period, a.project_name, a.payment_amt
,NPV=CAST(a.payment_amt/POWER(1.+r, a.time_period) AS DECIMAL(12,4))
,NPV0=NPV
,b.A, b.C0, b.n
,CASE a.time_period
-- Second initial guess (a cheat)
WHEN 1 THEN r-.01 -- POWER(1. + b.r, LOG10(b.A/b.C0)/LOG10(b.A/?)
-- Secant method without correction
ELSE r-((r-rn0)/(NPV-NPV0))
END
,r
FROM #CashFlows a
INNER JOIN IRR b
ON b.time_period + 1 = a.time_period AND a.project_name = b.project_name
)
SELECT project_name, r
FROM IRR
WHERE time_period = 4


Also, this isn't 100% complete as I didn't fully implement the initial guess for r2 (refer to the Wiki page) because I had difficulty interpreting the denominator (see the ? in the commented code). You'd also want it to handle cases where there is a different number of cash flows, whereas I just picked the value in period #4.

Perhaps it is a start though.

Note that the Wiki secant solution implemented (I didn't try to do the corrected secant solution) specifies that the first cash flow must be negative while all subsequent ones must be positive. I recall there are more likely to be multiple roots in cases where some of the later cash flows are negative.

So ends my quick and dirty attempt at this.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1394845
Posted Monday, December 10, 2012 6:58 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 3,627, Visits: 5,270
CELKO (12/10/2012)
The secant method can fail if there are two or more roots.


According to the Wiki article, the secant method doesn't fail on this case. It simply identifies one of the roots. A different pair of initial guesses may identify others.

I believe the secant method may fail to converge in cases where there are negative cash flows after the initial investment.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1394846
Posted Thursday, December 13, 2012 6:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 3,627, Visits: 5,270
So come on Joe, how does my suggestion stack up?

I mentioned on another thread that I've never gotten the forum-ritual CELKO-reaming for some reason. Now's your chance.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1396448
Posted Friday, December 14, 2012 5:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 1,945, Visits: 2,912
So come on Joe, how does my suggestion stack up?


I got an email with a new version of his original code from the guy who gave me the code in SQL FOR SMARTIES. I wanted to check it out first. He is also doing a secant convergent answer.

I was hoping for a look-up table that would be limited to a small set of realistic interest rates. If a root went outside the limits, you would get a "forget it!" result. Maybe not be possible.

I mentioned on another thread that I've never gotten the forum-ritual CELKO-reaming for some reason. Now's your chance.


Sorry, sorry. The best I can do on short notice is "Your mother dresses you funny" but that lacks something.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1396842
Posted Saturday, December 15, 2012 7:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 3,627, Visits: 5,270
CELKO (12/14/2012)
So come on Joe, how does my suggestion stack up?


I got an email with a new version of his original code from the guy who gave me the code in SQL FOR SMARTIES. I wanted to check it out first. He is also doing a secant convergent answer.

I was hoping for a look-up table that would be limited to a small set of realistic interest rates. If a root went outside the limits, you would get a "forget it!" result. Maybe not be possible.

I mentioned on another thread that I've never gotten the forum-ritual CELKO-reaming for some reason. Now's your chance.


Sorry, sorry. The best I can do on short notice is "Your mother dresses you funny" but that lacks something.


My wife dresses me funny enough so you can leave my momma outta this.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1396936
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse