IRR calculation
N
Has anyone here had any experience with internal rate of return
(IRR)calculations on SQL Server. We have an asset management system where a
client invests money and then makes transactions over a period of time. We
need to calculation an IRR value for inception, year and quarter data. This
IRR is an exceptionally iterative calc and i just wanted to know if anyone
has had experience with this?
At the moment we do not follow the strict formula for calculating the IRR as
this is too iterative and we use a guessing system based on the transactions
in the period concerned.
Thanks
N
Partha Mandayam
http://www.frick-cpa.com/tvom/TVOM_FV_Annuity.asp
Regards
Partha Mandayam
Software Consultant
Home page: http://partha.tripod.com
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Partha Mandayam
http://www.intelligententerprise.com/print_article_flat.jhtml?article=/o
nline_only/celko/030303_1.jhtml
Steve Kass
I don't know how messy your income streams are, but I would expect
Newton's method not to require too many iterations. I doubt guessing is a
bad idea either. Here's a Newton's method solution, and you'll probably
have to adjust the present value calculation for whatever quirky rules there
are. Mine is based on simple interest and an assumption that each year has
365 days. You'll also want to add parameters to the function to select out
specific accounts or whatever, depending on what's in your table.
create table IncomeStream (
amt float,
d datetime
)
insert into IncomeStream values (-1000,'19980101')
insert into IncomeStream values (200,'19990101')
insert into IncomeStream values (200,'20000101')
insert into IncomeStream values (200,'20010101')
insert into IncomeStream values (200,'20020101')
insert into IncomeStream values (200,'20030101')
insert into IncomeStream values (200,'20040101')
go
create function irr(
@d datetime
) returns decimal(18,10) as begin
declare @irrPrev float set @irrPrev = 0
declare @irr float set @irr = 0.1
declare @pvPrev float
declare @pv float
set @pvPrev = (
select sum(amt)
from IncomeStream
)
set @pv = (
select sum(amt/power(1e0+@irr,cast(d-@d as float)/360e0))
from IncomeStream
)
while abs(@pv) >= 0.0001 begin
declare @t float
set @t = @irrPrev
set @irrPrev = @irr
set @irr = @irr + (@t-@irr)*@pv/(@pv-@pvPrev)
set @pvPrev = @pv
set @pv = (
select sum(amt/power(1e0+@irr,cast(d-@d as float)/365e0))
from IncomeStream
)
end
return @irr
end
go
select dbo.irr('19980101')
go
drop function irr
drop table IncomeStream
go
Steve Kass
Drew University
"N" <raa...@hotmail.com> wrote in message
news:edAYaSG...@TK2MSFTNGP10.phx.gbl...
Joe Celko
ENTERPRISE and then showed how to use a query to get the IRR. The point
of the article was to demonstrate how to do table look-ups in SQL, not
how to get the best NPV values. At the end, I did a IRR with a MIN()
aggregate. The problem is that since NPV is a polynominal, you can have
more than one IRR.
I'd recommend getting a financial tool of some kind for this problem.
You get floating point errors, ugly iterations, etc.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
Steve Kass
vice versa), Descartes's rule of signs guarantees that the IRR exists
and is unique. Newton's method is a reliable method for finding it in
such cases, but for messier income streams, the problem is, well, messier.
SK
N
"N" <raa...@hotmail.com> wrote in message
news:edAYaSG...@TK2MSFTNGP10.phx.gbl...
MD
I got the same problem like N.
Please do post if there is any Sql server program or somthing to calculate XIRR.
Thanks
MD
From http://www.developmentnow.com/g/113_2004_7_0_0_429699/IRR-calculation.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/
--CELKO--
appears in my book THINKING IN SETS.
--CELKO--
> Did you bother to Google the article in my origial posting? It also
> appears in my book THINKING IN SETS.
http://www.intelligententerprise.com/online_only/celko/030303_1.jhtml
Steve Kass
answer I gave "N" in 2004:
N,
go
go
Steve Kass
Drew University
http://www.stevekass.com
