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)
First download the source code from here and extract it.
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.
Read Full Post »
SQL Server Portal 