Feeds:
Posts

## SQL SERVER – Future value of Annuity(FV)

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

Reference : Castle
Techonthenet

### 9 Responses

1. on February 8, 2013 at 6:37 pm | Reply TheSQLGuru

Don’t forget though that using UDFs will void the use of parallelism by the SQL Server query engine (among MANY other flaws they have). I would think CLR would be much more efficient here and doesn’t prevent parallelism as long as you aren’t doing other data access, which you wouldn’t need here.

• on February 8, 2013 at 7:19 pm | Reply Muhammad Imran

Hi Kevin,
Thank you for your feedback. This is really nice suggestion, will do the same in CLR and post it in my upcoming articles.

Regards,
Imran

2. on February 8, 2013 at 11:07 pm | Reply Marc Jellinek

I have to second the recommendation to use CLR… this way, you can directly call the Excel functions and don’t have to worry about different implementations of the same function.

The bad news: you have to install Excel on the same server as SQL.

• on February 9, 2013 at 1:56 am | Reply TheSQLGuru

You really don’t want to call out to Excel from SQL CLR function!! Perf would be HORRIBLE and you would have to make the function UNSAFE too IIRC.

• on February 10, 2013 at 12:01 am Muhammad Imran

Guys, Thanks for your valuable feedback,But what should be the best solution ?

3. on February 11, 2013 at 5:42 pm | Reply RyGuy7272

This works great, Muhammad. Thanks for sharing this with us!!

@TheSQLGuru, he is not calling an Excel Function, he’s confirming the results of the calculation.

• on February 14, 2013 at 6:18 pm | Reply Muhammad Imran

Thanks RyGuy,
So what do you think, It should be developed as a function in SQL or do we need to go for CLR implementation ?

Imran

4. […] days ago, I developed two excel functions Future Value of Annuity and Present Value of Annuity in SQL Server as a User defined function and shared […]

5. […] my last article, I shared how to calculate Future Value of Annuity(FV) function in SQL Server and as promised in this article, today I will be sharing about Present Value […]