internal rate of return - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

internal rate of return

Author

Topic

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2003-01-25 : 11:43:46

anyone wrote a function to calcuate internal rate of return?

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-25 : 11:47:13

Looks like the formulas are all here just need to make a udf using sql server's math functions.

http://invest-faq.com/articles/analy-int-rate-return.html

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2003-01-25 : 11:59:31

yes, i learned the calclation method but anyone already wrote a udf for it?

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-25 : 12:31:13

post the formula you want and someone will write a udf
Is it to calculate irr for a single investment or multiple?

Edited by - ValterBorges on 01/25/2003 12:31:21

Edited by - ValterBorges on 01/25/2003 12:48:23

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2003-01-25 : 12:44:57

stated in the link u provided,

IRR is defined by the equation: NPV(C, t, IRR) = 0.
In other words, the IRR is the discount rate which sets the NPV of the given cash flows made at the given times to zero.

In general there is no closed-form solution for IRR. One must find it iteratively. In other words, pick a value for IRR. Plug it into the NPV calculation. See how close to zero the NPV is. Based on that, pick a different IRR value and repeat until the NPV is as close to zero as you care.

I searched through the web and find one function written by a guy named Bob

I don't get a sql server to test it. but is this the function i need ?

/*************************/
-- net present value
-- npv = sum(cf(t)/(1+r)^t) for t=0 to n
-- where cf(t) is the cash flow at time t
-- and r is the discount rate

if exists (select * from dbo.sysobjects where id = object_id('dbo.npv') and
xtype in ('FN', 'IF', 'TF'))
drop function dbo.npv
GO

create function dbo.npv (@rate real) returns real
begin
declare @npv real -- return value
declare @t int
declare @cf money
set @npv=0
set @t=0
declare cur cursor for select cf from test
open cur
fetch next from cur into @cf
while @@FETCH_STATUS = 0
begin
set @npv = @npv + @cf * power(1+@rate, -@t)
set @t = @t+1
fetch next from cur into @cf
end
close cur
deallocate cur
return(@npv)
end
go

/*************************/
-- internal rate of return
-- irr is defined as the discount rate at which the npv of the cash flows is
exactly zero
-- the only way to solve for irr is through iteration
-- the irr can be multivariate or undefined, therefore a guess value is
required
-- irr and npv are inverse functions
-- a good test is the npv of the cash flows at a discount rate equal to the
irr should
-- equal zero (or very close to zero)

if exists (select * from dbo.sysobjects where id = object_id('dbo.irr') and
xtype in ('FN', 'IF', 'TF'))
drop function dbo.irr
GO

create function dbo.irr (@rateguess real) returns real
begin
declare @delta real -- rate delta in 2-point formula
set @delta=.0001 -- .0001 equals one hundreth of one percent
declare @epsilon real -- criteria for success, npv must be within +/-
epsilon of zero
set @epsilon=.005 -- .005 equals one half cent
declare @maxtry smallint -- number of iterations allowed
set @maxtry=10

declare @irr real -- return value
set @irr=null -- assume failure

declare @rate1 real
declare @rate2 real
declare @npv1 real
declare @npv2 real
declare @done smallint
declare @try smallint

set @done=0
set @try=0
while @done=0 and @try<@maxtry
begin
set @rate1 = @rateguess
set @npv1 = dbo.npv(@rate1)
if abs(@npv1) < @epsilon
begin
-- success
set @done=1
set @irr=@rate1
end
else
begin
-- try again with new rateguess
set @rate2 = @rate1 + @delta
set @npv2 = dbo.npv(@rate2)
set @rateguess = @rate1 - @npv1*(@rate2-@rate1)/(@npv2-@npv1)
set @try = @try + 1
end
end
return(@irr)
end
go

/*************************/
-- setup test table of cash flows, first cash flow at t=0
if exists (select * from sysobjects where id = object_id('test') and sysstat
& 0xf = 3)
drop table test
GO

create table test (cf money not null)
go
set nocount on
insert test (cf) values (-100)
insert test (cf) values (10)
insert test (cf) values (10)
insert test (cf) values (10)
insert test (cf) values (10)
insert test (cf) values (10)
insert test (cf) values (10)
insert test (cf) values (10)
insert test (cf) values (10)
insert test (cf) values (10)
insert test (cf) values (10)
insert test (cf) values (10)
set nocount off
go

select dbo.npv(.1)
go
select dbo.irr(.05)
go
-- the net present value of the internal rate of return should be very close
to zero
select dbo.npv(dbo.irr(.05))
go

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-25 : 12:53:05

This looks good npv can also be written without a cursor this might be worth doing if your table has lots of records for multiple accounts.

Returns

-35.049397

0.01623128

1.879624E-5

Simplified the npv function however table must have a sequential column.

/*************************/
-- net present value
-- npv = sum(cf(t)/(1+r)^t) for t=0 to n
-- where cf(t) is the cash flow at time t
-- and r is the discount rate

if exists (select * from dbo.sysobjects where id = object_id('dbo.npv') and
xtype in ('FN', 'IF', 'TF'))
drop function dbo.npv
GO

create function dbo.npv (@rate real) returns real
begin
declare @npv real -- return value

SELECT @npv = SUM(cf*power(1+@rate,-pid))
FROM test
return(@npv)

end
go

/*************************/
-- internal rate of return
-- irr is defined as the discount rate at which the npv of the cash flows is exactly zero
-- the only way to solve for irr is through iteration
-- the irr can be multivariate or undefined, therefore a guess value is required
-- irr and npv are inverse functions
-- a good test is the npv of the cash flows at a discount rate equal to the irr should
-- equal zero (or very close to zero)

if exists (select * from dbo.sysobjects where id = object_id('dbo.irr') and
xtype in ('FN', 'IF', 'TF'))
drop function dbo.irr
GO

create function dbo.irr (@rateguess real) returns real
begin
declare @delta real -- rate delta in 2-point formula
declare @epsilon real -- criteria for success, npv must be within +/- epsilon of zero
declare @maxtry smallint -- number of iterations allowed
declare @irr real -- return value

set @delta=.0001 /*-- .0001 equals one hundreth of one percent */
set @epsilon=.005 -- .005 equals one half cent
set @maxtry=10
set @irr=null -- assume failure

declare @rate1 real
declare @rate2 real
declare @npv1 real
declare @npv2 real
declare @done smallint
declare @try smallint

set @done=0
set @try=0
while @done=0 and @try<@maxtry
begin
set @rate1 = @rateguess
set @npv1 = dbo.npv(@rate1)
if abs(@npv1) < @epsilon
begin
-- success
set @done=1
set @irr=@rate1
end
else
begin
-- try again with new rateguess
set @rate2 = @rate1 + @delta
set @npv2 = dbo.npv(@rate2)
set @rateguess = @rate1 - @npv1*(@rate2-@rate1)/(@npv2-@npv1)
set @try = @try + 1
end
end
return(@irr)
end
go

/*************************/
-- setup test table of cash flows, first cash flow at t=0
if exists (select * from sysobjects where id = object_id('test') and sysstat & 0xf = 3)

drop table test
GO

create table test (pid int not null, cf money not null)
go
set nocount on
insert test (pid,cf) values (0,-100)
insert test (pid,cf) values (1,10)
insert test (pid,cf) values (2,10)
insert test (pid,cf) values (3,10)
insert test (pid,cf) values (4,10)
insert test (pid,cf) values (5,10)
insert test (pid,cf) values (6,10)
insert test (pid,cf) values (7,10)
insert test (pid,cf) values (8,10)
insert test (pid,cf) values (9,10)
insert test (pid,cf) values (10,10)
insert test (pid,cf) values (11,10)
set nocount off
go

select dbo.npv(.1)
go

go
select dbo.irr(.05)
go
-- the net present value of the internal rate of return should be very close to zero
select dbo.npv(dbo.irr(.05))
go

Edited by - ValterBorges on 01/25/2003 15:16:16

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2003-01-25 : 22:18:02

ValterBorges, thx a lot for your help. I will try it when I back to office tomorrow.
BTW, what do u mean table must have a sequential column?
besides, what parameters should i pass to the funcion to calculate?

sorry for my ignornamce

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-25 : 22:53:54

Notice that in my post there is a pid
from the formula in the link it's the t[i] value

quote:

We first have the net present value or NPV:

N
NPV(C, t, d) = Sum C[i]/(1+d)^t[i]
i=0

where:
C[i] is the i-th cash flow (C[0] is the first, C[N] is the last).
d is the assumed discount rate.
t[i] is the time between the first cash flow and the i-th. Obviously, t[0]=0 and t[N]=the length of time under consideration. Pick whatever units of time you like, but remember that IRR will end up being rate of return per chosen time unit.

You would pass it your best guess at the irr and it will find it because it uses an approximation technique
Looks like newton's method to me.
http://groups.google.com/groups?q=newton%27s+approximation+method&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=RL%25v9.170362%24md1.36573%40sccrnsc03&rnum=6

Make sure you read the part about f'(x) being close to 0.
Because if @npv2-@npv1 is zero you'll get a divide by zero error
which means you need a better initial approximation.

set @rateguess = @rate1 - @npv1*(@rate2-@rate1)/(@npv2-@npv1)

until if finds a rate where the npv is 0.

Take a look at this thread.
One guy mentions that starting out with a guess of 10% and using newton's approximation which has quadratic convergence will give you good results most of the time.

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=868045948.9041%40dejanews.com&rnum=12&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D868045948.9041%2540dejanews.com%26rnum%3D12

It's been a long time since I took a numerical analysis course but let me try to explain convergence. One simple method to find a value in a liner function is the bisection method. Image you have a point on a line or an array of values and you have 100 items and they are sorted. It will take at most 10 tries to find the value you're looking for because every time you split the search space in half. It's like guessing for a number a someone telling you higher or lower.

Ex: Let's say I can pick a number from 1 to 100 and I pick 1 however you don't know that and you guess the following

50
I say lower and you guess
25
I say lower and you guess
13
I say lower and you guess
6
etc...
so at most it would take you ten times for 100 values. 10^2=100
Well newton's method is more sophisticated and it would find the correct value in 4 tries. 4^4 > 100 that's why they call it quadratic convergence.
I also remember that some method's work better than other's in different situations and that sometimes these approximations never converge but you'll have to pick up a numerical analysis book to get a better explanation.

Wow I can't believe I forgot so much.
All you mathematicians please forgive me and please feel free to correct me if i'm wrong.

Edited by - ValterBorges on 01/26/2003 00:12:02

nerellav
Starting Member

1 Post

Posted - 2011-08-11 : 07:55:55

@yipchunyu

Have you written a UDT for this? if yes, can you please post the code. when I tried to implement this IRR function I am getting a divide by zero exception.

Thanks in advance.
Vamsi.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources