SQL Server Portal

Whenever I used to work on financial reports, I used to grab the data from SQL Server and do it on Excel 2007/ 2010. The reason behind this is Excel has very strong financial functions. I tried to develop these financial functions in SQL with the same parameters like Excel, so the developers using Excel can do these financial reports in SQL.

Given below is the Future Value of Annuity function in SQL :

Create FUNCTION UDF_FutureValue
(@InterestRate NUMERIC(18,8), --Rate is the interest rate per period.
@Nper INT                 ,  --Nper is the total number of payment periods in an
--annuity.
@Pmt  NUMERIC(18,4)       ,  --Pmt is the payment made each period; it cannot
--change over the life of the annuity.
--Payment Value must be entered as a negative
--number.
@Pv   NUMERIC(18,4)       ,  --Pv is the present value, or the lump-sum amount
--that a series of future payments is worth right
--now. If Pv is omitted, it is assumed to be 0
--(zero). PV must be entered as a negative number.
@Type  BIT                    --Type is the number 0 or 1 and indicates when
--payments are due.
--If type is omitted, it is assumed to be 0
--which represents at the end of the period.
--If payments are due at the beginning of
--the period, type should be 1.
) RETURNS NUMERIC(18,2)
AS
BEGIN
DECLARE  @Value NUMERIC(18,2)
SELECT @Value =
Case WHEN @Type=0 THEN
@Pmt* ((Power(Convert(float,(1 + @InterestRate / 100)),@Nper)-1)
/Convert(float,(@InterestRate / 100)))
+ @Pv * Power((1 + @InterestRate / 100),@Nper)

WHEN @Type=1 THEN
@Pmt*((Power(Convert(float,(1 + @InterestRate / 100)),@Nper)-1)
/Convert(float,(@InterestRate / 100))) *
Convert(float,(1 + @InterestRate / 100))+ @Pv *
Power(Convert(float,(1 + @InterestRate / 100)),@Nper)
END
RETURN @Value*-1
END
GO
--Syntax
--dbo.UDF_FutureValue(@InterestRate,@Nper,@Pmt,@Pv ,@Type)
--You can find the same syntax in excel.
--FV(Rate,Nper,Pmt,Pv ,Type)

SELECT dbo.UDF_FutureValue(0.625,24,-250,-5000,0)
--12258.14                           --SQL OUTPUT
--=FV(7.5%/12, 2*12, -250, -5000, 0) --EXCEL FORMULA
--$12,258.14                         --EXCEL OUTPUT
GO
SELECT dbo.UDF_FutureValue(6,30,0,-5000,1)
--28717.46                          --SQL OUTPUT
--=FV(0.06,30,0,-5000,1)            --EXCEL FORMULA
--$28,717.46                        --EXCEL OUTPUT
GO
SELECT dbo.UDF_FutureValue(6,30,-1200,0,1)
--100562.01                      --SQL OUTPUT
--=FV(0.06,30,-1200,0,1)         --EXCEL FORMULA
--$100,562.01                    --EXCEL OUTPUT
GO
SELECT dbo.UDF_FutureValue(6,30,-1200,-5000,1)
--129279.47                     --SQL OUTPUT
--=FV(0.06,30,-1200,-5000,1)    --EXCEL FORMULA
--$129,279.47                   --EXCEL OUTPUT
GO
SELECT dbo.UDF_FutureValue(0.50,360,-100,0,1)
--100953.76                     --SQL OUTPUT
--=FV(0.06/12,360,-100,0,1)     --EXCEL FORMULA
--$100,953.76                   --EXCEL OUTPUT

Let me know your valuable feedback about this function. Shall post Present Value of Annuity in the coming post.

Reference : Castle
Techonthenet