sqlserver存储过程实现Excel中npv和irr函数

/*************************/-- 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 rateif exists (select * from dbo.sysobjects where id = object_id('dbo.npv') andxtype in ('FN', 'IF', 'TF'))drop function dbo.npvGOcreate function dbo.npv (@rate real) returns realbegindeclare @npv real -- return valuedeclare @t intdeclare @cf moneyset @npv=0set @t=0declare cur cursor for select cf from testopen curfetch next from cur into @cfwhile @@FETCH_STATUS = 0beginset @npv = @npv + @cf * power(1+@rate, -@t)set @t = @t+1fetch next from cur into @cfendclose curdeallocate curreturn(@npv)endgo/*************************/-- 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') andxtype in ('FN', 'IF', 'TF'))drop function dbo.irrGOcreate function dbo.irr (@rateguess real) returns realbegindeclare @delta real -- rate delta in 2-point formulaset @delta=.0001 -- .0001 equals one hundreth of one percentdeclare @epsilon real -- criteria for success, npv must be within +/-epsilon of zeroset @epsilon=.005 -- .005 equals one half centdeclare @maxtry smallint -- number of iterations allowedset @maxtry=10declare @irr real -- return valueset @irr=null -- assume failuredeclare @rate1 realdeclare @rate2 realdeclare @npv1 realdeclare @npv2 realdeclare @done smallintdeclare @try smallintset @done=0set @try=0while @done=0 and @try<@maxtrybeginset @rate1 = @rateguessset @npv1 = dbo.npv(@rate1)if abs(@npv1) < @epsilonbegin-- successset @done=1set @irr=@rate1endelsebegin-- try again with new rateguessset @rate2 = @rate1 + @deltaset @npv2 = dbo.npv(@rate2)set @rateguess = @rate1 - @npv1*(@rate2-@rate1)/(@npv2-@npv1)set @try = @try + 1endendreturn(@irr)endgo/*************************/-- setup test table of cash flows, first cash flow at t=0if exists (select * from sysobjects where id = object_id('test') and sysstat& 0xf = 3)drop table testGOcreate table test (cf money not null)goset nocount oninsert 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 offgoselect dbo.npv(.1)goselect dbo.irr(.05)go-- the net present value of the internal rate of return should be very close to zeroselect dbo.npv(dbo.irr(.05))go

--另一种带顺序的写法：

/*************************/ -- 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 testreturn(@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 (1,-916) insert test (pid,cf) values (2,124) insert test (pid,cf) values (3,340) insert test (pid,cf) values (4,474) insert test (pid,cf) values (5,802) insert test (pid,cf) values (6,1739) insert test (pid,cf) values (7,-916) insert test (pid,cf) values (8,-792) insert test (pid,cf) values (9,-452) insert test (pid,cf) values (10,21) insert test (pid,cf) values (11,823) insert test (pid,cf) values (12,2562) set nocount off go 
select dbo.npv(.1) go 
goselect dbo.irr(.1) go -- the net present value of the internal rate of return should be very close to zero select dbo.npv(dbo.irr(.05)) go