SQL Server – Excel Financial Functions – using CLR

« SQL Server – Dont’s for Offset Rows And Fetch Next

SQL SERVER – How to run sp_spaceused for all tables in a database »

SQL Server – Excel Financial Functions – using CLR

February 25, 2013 by Muhammad Imran

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.

Posted in Functions, SQL SERVER | Tagged DDB, FV, IPMT, PPMT, PV, raresql, Rate, SLN, SQL, SQL Server, SQL Server - Excel Financial Functions - Using CLR, SYD | 29 Comments

29 Responses

on February 26, 2013 at 6:50 pm | Reply Marc JellinekMarc Jellinek

Are these re-implemented equivalents of the Excel functions or are they native Excel functions? If they are native Excel functions, how do you avoid installing Excel?
- on February 27, 2013 at 1:26 am | Reply Muhammad Imran
  
  Hi Marc,
  These are the built-in dot net financial functions. I just implemented it in SQL using CLR.
  
  Imran
on May 16, 2013 at 11:36 pm | Reply Kyle Gammill

This is exactly what I was looking for…but I am having an issue downloading the files/renaming them to zip (they say there is no files once I rename and try to extract). Great article.

Any way you could e-mail me a copy? Would really apprecaite it.
- on May 16, 2013 at 11:48 pm | Reply Muhammad Imran
  
  Hi Kyle,
  
  Will send you the file shortly.
  
  Thanks
  
  Imran
  - on January 24, 2017 at 8:54 am Urvesh Vibhakar
    
    Can you please send me the DLL, I cannot download the file.
    Thanks,
    Urvesh
- on May 17, 2013 at 9:08 pm | Reply Kyle Gammill
  
  Thank you so much!
  
  Worked like a charm.
  
  THIS IS A FANTASTIC ARTICLE!
  
  Really appreciate your post.
on May 16, 2013 at 11:43 pm | Reply SQL SERVER – Excel Financial functions – PMT | SQL Server Portal

[…] 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 […]
on August 28, 2013 at 8:38 pm | Reply Ben Loveless

I’m also having trouble getting the file to download and extract. Can you check the linked file, or email it to me as well? Thanks so much for providing this method – I’m looking forward to seeing how this works.
- on August 30, 2013 at 12:47 am | Reply Muhammad Imran
  
  Sent the file to you, let me know if you face any issues.
  
  Imran
on September 4, 2013 at 12:55 am | Reply Tony

Hi, it looks really interesting. However I had troubles downloading the files. Could you email me the files, please? Thanks!
on October 18, 2013 at 7:50 am | Reply Roly

This is exactly what I am looking for. Any chance of fixing the download files as they are not recognised as zip files (after the rename). Even better if they could be emailed to me I would be very grateful.
on January 8, 2014 at 8:04 pm | Reply Sean

Hi, can you send me the CLR zip file since I cannot open the file. Very much appreciate that. Thanks.
- on January 10, 2014 at 1:56 pm | Reply Muhammad Imran
  
  Hi Sean,
  I sent it. Please check.
  
  Imran
on January 25, 2014 at 10:14 pm | Reply Jeff

also having trouble downloading. can you please email to me?
- on January 25, 2014 at 10:48 pm | Reply Muhammad Imran
  
  Sent
on March 25, 2014 at 1:08 pm | Reply Jan

Exact what I was looking for.
Do not rename to .ZIP but to .RAR and it will be recognized as such (because it is compressed as RAR).
- on March 28, 2014 at 3:40 pm | Reply Muhammad Imran
  
  Hi Jan,
  Thanks for your update. Will rename it to RAR.
  
  Imran
on April 17, 2014 at 7:03 am | Reply Jonathan Stern

I am trying the RATE function and I cannot get it to replicate Excel in a specific instance. I am trying to get back a value from the function when I pass in no pmt value. That is, for calculating a Canadian Mortgage, the interest is compounded semi-annually which means I cannot just divide the rate out by 12. I have the formula, is it as follows: =PMT(RATE(12/2,,-1,1+YearlyRate/2,0),12*amTerm,-principle)
Note, the pmt portion of the rate function is empty. This formula in Excel will return what an online mortgage calculator returns. i am trying to replicate this in SQL server.
Thanks in advance
- on April 19, 2014 at 3:33 pm | Reply Muhammad Imran
  
  Hi Jonathan,
  
  Do you want to develop it via CLR or a user defined function in SQL ?
  
  Thanks
  
  Imran
  - on April 19, 2014 at 7:04 pm Jonathan Stern
    
    It does not matter so long as it works. I see you are referencing the .net financial namespace. I was looking for the differences between this function and what Excel does. If it can be done in SQL, that is good. It is a little easier to maintain SQL code vs CLR code.
    
    Do you have the logic?
    
    Thanks!
  - on April 19, 2014 at 7:06 pm Muhammad Imran
    
    Thanks Jonathan. I will review it in next couple of day and update you.
    
    Imran
on December 24, 2014 at 4:49 am | Reply JB

This is great, but when I download the file and rename to .zip it doesnt allow me to extract it, because the contents of the zipped folder are empty. Seems that others encountered the same issue. Any way you can please email me the file? jbernal@insightinvestments.com

Thank you
on December 24, 2014 at 10:42 pm | Reply Julio

Would you please email me the Visual Studio files? When I download them and rename to .zip I get an error indicating the the zipped folder is empty. I did read somewhere else that others had the same issue.
Thanks
- on December 24, 2014 at 11:50 pm | Reply Muhammad Imran
  
  Thanks. Please download it from here (https://app.box.com/s/9o3ut7j7w6v8fusta58p)
on January 13, 2015 at 5:56 pm | Reply Teodoro Marinucci

Hi,
everything works perfectly, but … I need to utilise a statistical function (namely the TINV). Unfortunately statistical functions are not in Microsoft.VisualBasic.Financial (or anywhere else in Microsoft.VisualBasic).
Is there any way to solve this problem ?
Thanks
on June 24, 2015 at 8:13 pm | Reply andrew

The IPMT needs fixing. It’s missing the “per” parameter. You have to change your VB code.
First line should be:
Public Shared Function IPMT(ByVal Rate As Double, ByVal Per As Double, ByVal NPer As Double, ByVal PV As Double, Optional ByVal FV As Double = 0.0, Optional ByVal Type As Boolean = False) As SqlDouble

and change the line in the if statement to:
IPMTValue = Math.Round(Fin.IPmt(Rate, Per, NPer, PV, FV, DueDate.BegOfPeriod), 2)
on January 14, 2016 at 11:25 am | Reply vladimirschuka

Hello, in function IPMT wrong parameter names,
@Rate [float], @NPer [float], @PV [float], @FV [float], @Type [bit] , but must
@Rate [float],@PER, @NPer [float], @PV [float] , ? – last unknown parameter i use 0 ant it dont metter for me.
Thank you for cool DLL.
on November 16, 2016 at 1:29 pm | Reply DSC

Hi,

I am using the Rate function – can I drop in field names instead of using numerical values?

Thanks
on January 18, 2017 at 8:37 pm | Reply mauroldanr

hello….I did my assemby, I use the library, using Excel = Microsoft.Office.Interop.Excel;

I bulid my project in vs y copie the dll in a folder which I refer when I create the assembly. I set up the property TRUSTWORTHY of my database on too.
when I tried to exec my function in sql, I get the error:
Could not load type ‘Microsoft.Office.Interop.Excel._Application’ from assembly ‘test, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’. The type is marked as eligible for type equivalence, but the containing assembly is not loaded as fully trusted.

could anyone help me how fix this, please?
thank you 🙂