Feeds:
Posts

## SQL SERVER – Excel Financial functions – PMT

In my earlier article, I implemented a lot of excel financial functions including PMT function using CLR  and I found CLR implementation is the effective way to implement excel functions in SQL Server. However you can develop the same functionality in SQL using User Defined Function as well.

Given below is the script of PMT financial function in SQL Server with examples. Basically PMT function not only calculates the future value of any investment on an interest rate, it also calculates the payments for a loan.

```CREATE FUNCTION UDF_PMT
(@InterestRate  NUMERIC(18,8), --Rate is the interest rate per period.
@Nper          INT,           --Nper is the total number of payment
--periods in an annuity.
@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.
@Fv            NUMERIC(18,4), --Fv is the future 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) --float
AS
BEGIN
DECLARE  @Value NUMERIC(18,2)
SELECT @Value = Case
WHEN @Type=0
THEN Convert(float,@InterestRate / 100)
/(Power(Convert(float,(1 + @InterestRate / 100)),@Nper)-1)
* -(@Pv*Power(Convert(float,(1 + @InterestRate / 100)),@Nper)
+@Fv)

WHEN @Type=1
THEN Convert(float,@InterestRate / 100) /
(Power(Convert(float,(1 + @InterestRate / 100)),@Nper)-1)
* -(@Pv*Power(Convert(float,(1 + @InterestRate / 100)),@Nper)
+@Fv)
/(1 + Convert(float,(@InterestRate / 100)))

END
RETURN @Value
END
GO
SELECT dbo.UDF_PMT(0.625,24,5000,0,0)
--OUTPUT in SQL
--(\$225.00)
--Equivalent function in excel
--=PMT(7.5%/12, 2*12, 5000, 0, 0)
--OUTPUT in Excel
--(\$225.00)
GO
SELECT dbo.UDF_PMT(0.11538461,208,8000,0,1)
--OUTPUT in SQL
--(\$43.23)
--Equivalent function in excel
--=PMT(6%/52, 4*52, 8000, 0, 0)
--OUTPUT in Excel
--(\$43.23)
GO
SELECT dbo.UDF_PMT(5.25,10,6500,0,0)
--OUTPUT in SQL
--(\$852.03)
--Equivalent function in excel
--=PMT(5.25%/1, 10*1, 6500, 0, 0)
--OUTPUT in Excel
--(\$852.03)
GO
SELECT dbo.UDF_PMT(0.666666667,36,5000,-1000,1)
--OUTPUT in SQL
--(\$131.14)
--Equivalent function in excel
--=PMT(8%/12, 3*12, 5000, -1000, 0)
--OUTPUT in Excel
--(\$131.14)
GO
```

Reference : Castle
Techonthenet

## SQL Server – Excel Financial Functions – using CLR

Few 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 it.

In this article, I will share, how to use common excel financial functions in SQL Server using CLR.
Note : You don’t need to install Excel on the server.
Given below is the list of Excel Financial Functions, we will implement using CLR.

• FV
• PV
• Rate
• DDB
• IPMT
• PPMT
• SLN
• SYD

Deployment:
There are two methods to deploy above CLR functions in SQL.
Method -1 : (If you have Visual Studio)
Open it in the Visual Studio, go to Build, first build the solution then deploy the solution.
It will ask for SQL server name and credentials and will directly deploy it on the SQL Server.
This is simple solution.

Method -2: (If you do not have Visual Studio)
Lets deploy and test it step by step.

Step 1 :
First download DLL from here and rename it to .zip and extract it.

Step 2 :
Copy the above DLL in a specified folder. (eg. C:\CLRExcel_Financial_Functions.DLL)

Step 3 :
Enable CLR in the SQL Server.

```sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
```

Step 4 :
Restart SQL Server.

Step 5 :
Create assembly and CLR functions in SQL Server.

```CREATE ASSEMBLY [CLRExcel_Financial_Functions]
AUTHORIZATION
[dbo] from 'c:\CLRExcel_Financial_Functions.dll' WITH PERMISSION_SET = SAFE
GO

CREATE FUNCTION [dbo].[FV](@Rate [float], @NPer [float], @Pmt [float], @PV [float], @Type [bit])
RETURNS [float] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[FV]

GO
CREATE FUNCTION [dbo].[DDB](@Cost [float], @Salvage [float], @Life [float], @Period [float], @Factor [float])
RETURNS [float] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[DDB]
GO

CREATE FUNCTION [dbo].[IPMT](@Rate [float], @NPer [float], @PV [float], @FV [float], @Type [bit])
RETURNS [float] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[IPMT]
GO

CREATE FUNCTION [dbo].[PPMT](@Rate [float], @Per [float], @NPer [float], @PV [float], @FV [float], @Type [bit])
RETURNS [float] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[PPMT]

GO

CREATE FUNCTION [dbo].[PV](@Rate [float], @NPer [float], @Pmt [float], @FV [float], @Type [bit])
RETURNS [float] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[PV]

GO

CREATE FUNCTION [dbo].[Rate](@NPer [float], @Pmt [float], @PV [float], @FV [float], @Type [bit], @Guess [float])
RETURNS [float] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[Rate]

GO

CREATE FUNCTION [dbo].[SLN](@Cost [float], @Salvage [float], @Life [float])
RETURNS [float] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[SLN]

GO

CREATE FUNCTION [dbo].[SYD](@Cost [float], @Salvage [float], @Life [float], @Period [float])
RETURNS [float] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[SYD]
GO
```

Step 6 :
Now, we are ready to use Excel financial function in SQL.

Given below are some examples of above functions.

```
Select dbo.FV(0.075/12,24,-250,-5000,0) as [Future Value]
GO
Select dbo.FV(0.06,30,0,-5000,1) as [Future Value]
GO
Select dbo.FV(0.06,30,-1200,0,1) as [Future Value]
GO

SELECT dbo.PV(0.075/12,24,250,0,0) as [Present Value]
GO
SELECT dbo.PV(0.06/52,4*52,50,0,1) as [Present Value]
GO
SELECT dbo.PV(0.0525,10,100,0,0) as [Present Value]
GO

Select dbo.Rate(24,-250,5000,0,0,0.1) as [Rate]
GO
Select dbo.Rate(4*52,-700,8000,0,0,0.1) as [Rate]
GO
Select dbo.Rate(10*1,-1000,6500,0,0,0.1) as [Rate]
GO

Select dbo.DDB(10000, 5000, 5, 1,2.0) as [DDB]
GO
Select dbo.DDB(10000, 5000, 5, 2,2.0) as [DDB]
GO
Select dbo.DDB(10000, 5000, 5, 3,2.0) as [DDB]
GO

Select dbo.IPMT(0.075/12, 8, 2*12, 5000,0) as [IPMT]
GO
Select dbo.IPMT(0.075/12, 8, 2*12, 8000,0) as [IPMT]
GO
Select dbo.IPMT(0.06/12, 8, 2*12, 8000,0) as [IPMT]
GO

Select dbo.PPMT(0.075/12, 5, 2*12, 5000, 0, 1) as [PPMT]
GO
Select dbo.PPMT(0.06/12, 5, 2*12, 5000, 0, 1) as [PPMT]
GO
Select dbo.PPMT(0.06/12, 5, 2*12, 10000, 0, 1) as [PPMT]
GO

Select dbo.SLN(10000, 5000, 5) as [SLN]
GO
Select dbo.SLN(1000, 5000, 5) as [SLN]
GO
Select dbo.SLN(100, 5000, 5) as [SLN]
GO

Select dbo.SYD(10000, 5000, 5, 1) as [SYD]
GO
Select dbo.SYD(10000, 5000, 5, 2) as [SYD]
GO
Select dbo.SYD(10000, 5000, 5, 3) as [SYD]
```

Cleaning

```
Drop Function dbo.DDB
Drop Function dbo.FV
Drop Function dbo.IPMT
Drop Function dbo.PPMT
Drop Function dbo.PV
Drop Function dbo.Rate
Drop Function dbo.SLN
Drop Function dbo.SYD
Drop Assembly CLRExcel_Financial_Functions
```

Let me know your valuable feedback.

## SQL SERVER – Present value of Annuity(PV)

In 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 of Annuity(PV) function in SQL Server, another important financial function.

Given below is the script of Present Value of Annuity function in SQL with examples :

```CREATE FUNCTION UDF_PresentValue
(@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.PaymentValue must be
--entered as a negative number.
@Fv            NUMERIC(18,4), --Fv is the future value, or the lump-sum
--amount that a series of future payments
--is worth right now. If Fv is omitted,
--it is assumed to be 0 (zero).
--FV 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) --float
AS
BEGIN
DECLARE  @Value NUMERIC(18,2)
SELECT @Value =
Case WHEN @Type=0
THEN @Pmt*(Power(Convert(float,(1 + @InterestRate / 100)),@Nper)
-1) /(((@InterestRate / 100))
* Power((Convert(float,1 + @InterestRate / 100)),@Nper))
+ @Fv *
Power(Convert(float,(1 + @InterestRate / 100)),@Nper)

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

SELECT dbo.UDF_PresentValue(0.625,24,250,0,0)
---5555.61						--SQL OUTPUT
--=PV(7.5%/12, 2*12, 250,0, 0)	--EXCEL FORMULA
--(\$5,555.61)					--EXCEL OUTPUT
GO

SELECT dbo.UDF_PresentValue(0.1153846,208,50,0,1)
---9252.07						--SQL OUTPUT
--==PV(6%/52, 4*52, 50, , 1)	--EXCEL FORMULA
--(\$9,252.07)					--EXCEL OUTPUT
GO
SELECT dbo.UDF_PresentValue(5.25,10,100,0,0)
---762.88						--SQL OUTPUT
--=PV(5.25%/1, 10*1, 100, , 0)	--EXCEL FORMULA
--(\$762.88)					    --EXCEL OUTPUT
GO
```

Reference : Castle
Techonthenet

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