Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 NPV and IRR in SQL Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, December 9, 2012 9:35 PM
 SSCommitted Group: General Forum Members Last Login: Today @ 7:08 PM Points: 1,945, Visits: 3,496
 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 PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
Post #1394442
 Posted Monday, December 10, 2012 3:55 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, April 16, 2015 1:59 AM Points: 141, Visits: 866
 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)ASBEGIN/*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 asof the maximum date in the dataSET provided else provide a specific date to seethe XIRR calculated as the given date.Created By: Ankeet ShahCreated On: 7/16/2008*/IF @d IS NULLSELECT @d = MAX(d) FROM IncomeTableDECLARE @irrPrev FLOAT SET @irrPrev = 0DECLARE @irr FLOAT SET @irr = -0.1DECLARE @PresentValuePrev FLOATDECLARE @PresentValue FLOATSET @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.0001BEGINDECLARE @t FLOATSET @t = @irrPrevSET @irrPrev = @irrSET @irr = @irr + (@t-@irr)*@PresentValue/(@PresentValue-@PresentValuePrev)SET @PresentValuePrev = @PresentValueSET @PresentValue = (SELECT SUM(amt/POWER(1e0+@irr,CAST(dt-@d as FLOAT)/365)) FROM XIRRTempData WHERE guid = @GUID )ENDRETURN @irrENDGO
Post #1394527
 Posted Monday, December 10, 2012 10:14 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 7:08 PM Points: 1,945, Visits: 3,496
 >> I don't claim to have a deep understanding of the mathematics of these functions. <> 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. <
Post #1394702
 Posted Monday, December 10, 2012 10:33 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, April 16, 2015 1:59 AM Points: 141, Visits: 866
 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 Group: General Forum Members Last Login: Monday, April 20, 2015 1:28 AM Points: 3,158, Visits: 11,768
 CELKO (12/10/2012)>> I don't claim to have a deep understanding of the mathematics of these functions. <> 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. <
Post #1394753
 Posted Monday, December 10, 2012 6:42 PM
 Hall of Fame Group: General Forum Members Last Login: Today @ 1:40 AM Points: 3,883, Visits: 6,032
 I am no mathemetician so I rely on Wiki for answers of this sort: http://en.wikipedia.org/wiki/Internal_rate_of_returnSince 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, rFROM IRRWHERE time_period = 4Also, 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1394845
 Posted Monday, December 10, 2012 6:58 PM
 Hall of Fame Group: General Forum Members Last Login: Today @ 1:40 AM Points: 3,883, Visits: 6,032
 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1394846
 Posted Thursday, December 13, 2012 6:35 PM
 Hall of Fame Group: General Forum Members Last Login: Today @ 1:40 AM Points: 3,883, Visits: 6,032
 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1396448
 Posted Friday, December 14, 2012 5:40 PM
 SSCommitted Group: General Forum Members Last Login: Today @ 7:08 PM Points: 1,945, Visits: 3,496
 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 PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
Post #1396842
 Posted Saturday, December 15, 2012 7:12 PM
 Hall of Fame Group: General Forum Members Last Login: Today @ 1:40 AM Points: 3,883, Visits: 6,032
 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1396936

 Permissions