This link may help you

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 ***

Here's a more detailed article

http://www.intelligententerprise.com/print_article_flat.jhtml?article=/o
nline_only/celko/030303_1.jhtml

Regards

Partha Mandayam
Software Consultant
Home page: http://partha.tripod.com


*** Sent via Devdex http://www.devdex.com ***

I did an article on NPV functions via table look-up in INTELLIGENT
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.

*** Sent via Devdex http://www.devdex.com ***

N,

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
[quoted text, click to view]

For income streams that start with payments and end with revenues (or
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

[quoted text, click to view]