There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

drop FUNCTION dbo.ufn_NPV

go

CREATE FUNCTION dbo.ufn_NPV(@rate decimal(30,10), @strIDs varchar(8000))

RETURNS decimal(30,10)

AS

BEGIN

DECLARE @t_IDs table(id int identity(0,1), value decimal(30,10))

DECLARE @strID varchar(12), @sepPos int

set @strIDs = coalesce(@strIDs + ',', '')

set @sepPos = charindex(',', @strIDs)

WHILE @sepPos > 0 BEGIN

SET @strID = LEFT(@strIDs, @sepPos - 1)

INSERT INTO @t_IDs(value) SELECT (CAST(@strID AS decimal(20,10))) where isnumeric(@strID)=1

SET @strIDs = RIGHT(@strIDs, DATALENGTH(@strIDs) - @sepPos)

set @sepPos = charindex(',', @strIDs)

END

RETURN (select SUM(value/power(1+@rate, id)) from @t_IDs)

END

go

drop FUNCTION dbo.ufn_IRR

go

CREATE FUNCTION dbo.ufn_IRR(@strIDs varchar(8000), @guess decimal(30,10))

RETURNS decimal(30,10)

AS

BEGIN

DECLARE @t_IDs table(id int identity(0,1), value decimal(30,10))

DECLARE @strID varchar(12), @sepPos int, @NPV decimal(30,10)

set @strIDs = coalesce(@strIDs + ',', '')

set @sepPos = charindex(',', @strIDs)

WHILE @sepPos > 0 BEGIN

SET @strID = LEFT(@strIDs, @sepPos - 1)

INSERT INTO @t_IDs(value) SELECT (CAST(@strID AS decimal(20,10))) where isnumeric(@strID)=1

SET @strIDs = RIGHT(@strIDs, DATALENGTH(@strIDs) - @sepPos)

set @sepPos = charindex(',', @strIDs)

END

set @guess = case when isnull(@guess, 0) <= 0 then 0.00001 else @guess end

select @NPV = SUM(value/power(1+@guess, id)) from @t_IDs

WHILE @NPV > 0

BEGIN

set @guess = @guess + 0.00001

select @NPV = SUM(value/power(1+@guess, id)) from @t_IDs

END

RETURN @guess

END

go

-- values to compare with excel (note that for excel 0.00001 is 0.1%)

select dbo.ufn_irr('-90,13,14,15,

dbo.ufn_npv(0.001, '-90,13,14,15,16,17,18') as npv

HTH

Hilaire