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 16, 2012 11:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
BWAA-HAAA!!!! Speaking of dressing funny... Joe... How long have you been wearing that same vest and shirt?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1397015
Posted Sunday, December 16, 2012 5:37 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:28 PM
Points: 1,945, Visits: 2,900
How long have you been wearing that same vest and shirt?


I have not worn the same black three-piece suit for the past three decades. That is a vile slander, sir!

The truth is that I own 8 identical black three-piece suits, 4 identical pairs of Manganni's, 10 identical narrow black silk neck ties and 12 french cuffed dress shirts. I liven up my wardrobe with super-hero cuff links. I have not changed sizes in 30+ years! My Nehru jacket still fits I do dye my beard before I go on stage these days, tho


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 #1397037
Posted Sunday, December 16, 2012 5:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
CELKO (12/16/2012)
How long have you been wearing that same vest and shirt?


I have not worn the same black three-piece suit for the past three decades. That is a vile slander, sir!

The truth is that I own 8 identical black three-piece suits, 4 identical pairs of Manganni's, 10 identical narrow black silk neck ties and 12 french cuffed dress shirts. I liven up my wardrobe with super-hero cuff links. I have not changed sizes in 30+ years! My Nehru jacket still fits I do dye my beard before I go on stage these days, tho


BWAAA-HAAA!!!! Too funny. Thanks for the great laugh, Joe.

I met a wonderful woman way back in '86 and have been with her ever since. She believes in the old saying that "Kissin' don't last but cookin' do". I went from 130 to 180 in the first five years. Even my feet have gotten longer because of her cooking. She does know how to make my tongue throw a party for my mouth. Man! Can that woman bake!

On the beard coloring thing... I don't do that because I'd also have to color my hair. That would't be the ANSI thing to do because the dye isn't that portable.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1397038
Posted Sunday, December 16, 2012 6:22 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: Wednesday, August 20, 2014 4:14 AM
Points: 3,618, Visits: 5,254
I've worn the same fish in my avatar for nearly a year now. Hoped for a change when I went fishing last month but no joy.


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 #1397048
Posted Monday, December 17, 2012 10:24 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:28 PM
Points: 1,945, Visits: 2,900
On the beard coloring thing... I don't do that because I'd also have to color my hair. That would't be the ANSI thing to do because the dye isn't that portable.


There is an ISO hair coloring, but ISO stands for Innovative Styling Options. The company is a division of Zotos International Inc., which creates lines of perms, styling products and hair color for salon professionals.

How did we live before Google?


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 #1397342
Posted Monday, December 17, 2012 12:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:51 PM
Points: 7,135, Visits: 15,153
Jeff Moden (12/16/2012)
CELKO (12/16/2012)
How long have you been wearing that same vest and shirt?


I have not worn the same black three-piece suit for the past three decades. That is a vile slander, sir!

The truth is that I own 8 identical black three-piece suits, 4 identical pairs of Manganni's, 10 identical narrow black silk neck ties and 12 french cuffed dress shirts. I liven up my wardrobe with super-hero cuff links. I have not changed sizes in 30+ years! My Nehru jacket still fits I do dye my beard before I go on stage these days, tho


BWAAA-HAAA!!!! Too funny. Thanks for the great laugh, Joe.

I met a wonderful woman way back in '86 and have been with her ever since. She believes in the old saying that "Kissin' don't last but cookin' do". I went from 130 to 180 in the first five years. Even my feet have gotten longer because of her cooking. She does know how to make my tongue throw a party for my mouth. Man! Can that woman bake!

On the beard coloring thing... I don't do that because I'd also have to color my hair. That would't be the ANSI thing to do because the dye isn't that portable.


slacker! I picked up my fat and happy pounds in 5 months (though I did get the hint after that )

That said - why not just take this on brute force style? cash flow schedules rarely get to be huge data sets. Assuming you put some boundaries around the thing - this returns in no time flat:

/*test data */
drop table #cashflows
;with numbers as (
select row_number() over (order by (select null)) N from sys.columns sc1 cross join sys.columns sc2),
n2 as (
select 0 n,cast(10000 as money) C --start out with an initial investment
union
select n, cast(rand(checksum(newid())) *10000 -6500 as float(53)) C --try to make a realistic cashflow schedule so skew towards having more inflows thant outflows
from numbers
where n<=30*12) --30 years of monthly cash
select * into #cashflows from n2

/*end of test data */

;with targetCTE as (select cast(row_number() over (order by (select null))*1.0 as float(53)) targetIRR from sys.columns sc1 cross join sys.columns sc2),
candidateIRRs as (
select targetIRR,sum(c/cast(power(1+targetIRR/1000,n) as float(53))) NPV from #cashflows cross join targetcte
where targetIRR<1001
group by targetIRR
)
select min(targetIRR)/1000.0 from candidateIRRs
where NPV>=0



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1397379
Posted Tuesday, December 18, 2012 5:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:28 PM
Points: 1,945, Visits: 2,900
why not just take this on brute force style? cash flow schedules rarely get to be huge data sets. Assuming you put some boundaries around the thing - this returns in no time flat:


I agree. And the available interest rates are often limited by law and bank conventions to a fixed decimal range and precision. I just wanted to fin something general and beautiful I had overlooked.


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 #1398111
Posted Thursday, February 13, 2014 5:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 3:48 AM
Points: 5, Visits: 26
There are a number of numerical methods that allow you to find IRR solving for the root(s) of the underlying IRR equations ( and there are many ways to define the equation )

I am not a T-Sql programmer so forgive me for any error or omission in the code that follows

The following code to find IRR is based on JavaScript financial functions library tadJS that itself is based on popular tadXL add-in for Excel

The code implements Newton-Raphson method to solve the root of the IRR equation represented with NPV - net present value. The root if found using 10% as guess rate will be one of the many possible IRR values.

The function may not always find an IRR in which case it returns a value of NULL. In such cases one would enter a guess rate other than 10% so the NR method retry finding an IRR

CREATE TYPE dbo.tadIrrTable AS TABLE
(
theValue DECIMAL(19, 9) NOT NULL
)
GO

CREATE FUNCTION dbo.tadIRR
(
@Sample tadIrrTable READONLY,
@Rate DECIMAL(19, 9) = 0.1
)
RETURNS DECIMAL(38, 9)
AS
BEGIN
DECLARE @X DECIMAL(19, 9) = 0.0,
@X0 DECIMAL(19, 9) = 0.1,
@f DECIMAL(19, 9) = 0.0,
@fbar DECIMAL(19, 9) = 0.0,
@i TINYINT = 0,

IF @Rate IS NULL
SET @Rate = 0.1

SET @X0 = @Rate

WHILE @i < 100
BEGIN
SELECT @f = 0.0,
@fbar = 0.0

SELECT @f = @f + theValue * POWER(1 + @X0, -T),
@fbar = @fbar - T * theValue * POWER(1 + @X0, (-T - 1))
FROM (
SELECT theValue,
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS T
FROM @Sample
) AS d

IF @fbar = 0
RETURN NULL

SET @X = @X0 - @f / @fbar

If ABS(@X - @X0) < 0.00000001
RETURN @X

SET @X0 = @X
SET @i += 1
END
RETURN NULL
END
GO

Post #1541125
Posted Monday, February 17, 2014 4:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 3:48 AM
Points: 5, Visits: 26
This is bit off topic but I am looking for a programming job (I know a bit of financial math and programming a bit in various languages other than T-SQL)

Used to run my own online shops that is all gone now

Accept my apologies if this is the wrong place to be posting this
Post #1542308
Posted Monday, February 17, 2014 9:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
bobthefinancialengineer (2/17/2014)
This is bit off topic but I am looking for a programming job (I know a bit of financial math and programming a bit in various languages other than T-SQL)

Used to run my own online shops that is all gone now

Accept my apologies if this is the wrong place to be posting this


So far as I'm concerned, there's never a wrong place to advertise your availability especially if you need a job. I will suggest that not too many people are going to see your message here.

As for what to do to find a job, I almost always turn to recruiters. Yeah, I know... a lot of them just throw bodies at jobs and jobs at bodies and they're terrible at what they do. But, there are good ones out there (even some of the bad ones turn out to be great if you take the time to talk with them as to what you're actually looking for) and they seem to have the inside track on a whole lot of jobs.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1542348
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse