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

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

/*************************/ 
-- 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 (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 
go
select 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