/*
-- Installation Script

-- Enable CLR on the server
sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

-- Tell SQL Server about the CLR DLL
CREATE ASSEMBLY FinancialFunctions
FROM 'C:\My Documents\FinancialFunctions\FinancialFunctions\bin\Release\FinancialFunctions.dll'

-- Change DB to the database in which you want to install the functions
USE <INSERT DATABASE NAME HERE>
GO

-- Import the functions
CREATE Function PMT(@r FLOAT, @nper INT, @pv FLOAT, @fv FLOAT, @type INT) 
RETURNS FLOAT EXTERNAL NAME FinancialFunctions.FinancialFunctions.PMT
GO 

CREATE Function FV(@r FLOAT, @nper INT, @c FLOAT, @pv FLOAT, @type INT) 
RETURNS FLOAT EXTERNAL NAME FinancialFunctions.FinancialFunctions.FV
GO 

CREATE Function IPMT(@r FLOAT, @per INT, @nper INT, @pv FLOAT, @fv FLOAT, @type INT) 
RETURNS FLOAT EXTERNAL NAME FinancialFunctions.FinancialFunctions.IPMT
GO 

CREATE Function PPMT(@r FLOAT, @per INT, @nper INT, @pv FLOAT, @fv FLOAT, @type INT) 
RETURNS FLOAT EXTERNAL NAME FinancialFunctions.FinancialFunctions.PPMT
GO 

*/


/**** Test Script ***/

DECLARE @LoanValue decimal(38,20)
DECLARE @APR decimal(20,15)
DECLARE @FullTerm int
DECLARE @CurrentPeriod int

SET @LoanValue     = -165000
SET @APR           = 0.05  --5%
SET @FullTerm      = 300  --300 months = 25 years
SET @CurrentPeriod = 1 --For which payment period do you want the payment breakdown?

DECLARE @PMT    decimal(38,20)
DECLARE @FV     decimal(38,20)
DECLARE @IPMT   decimal(38,20)
DECLARE @PPMT   decimal(38,20)

SET @PMT =  dbo.PMT(@APR/12.0, @FullTerm, @LoanValue, 0, 0)
SET @FV =   dbo.FV(@APR/12.0, @CurrentPeriod, @PMT, @LoanValue, 0)
SET @IPMT = dbo.IPMT(@APR/12.0, @CurrentPeriod, @FullTerm, @LoanValue, 0, 0)
SET @PPMT = dbo.PPMT(@APR/12.0, @CurrentPeriod, @FullTerm, @LoanValue, 0, 0)

SELECT @PMT AS PMT, @FV AS FV, @IPMT AS IPMT, @PPMT AS PPMT