Functions for SQLServer

This is a VisualStudio 2017 project , implementing financial functions inside a MS SQL Server. ( thanks to CLR inside the MS SQL Server ).

Financial library for SQL Server

Collection of User Defined Functions (SQL compliant) for some math or financial purposes.

Mandatory signed assemblies

For security reason, (have a look on CLR Strict security) any libraries we want to use need to be signed by a recognized certificat.

A PFX File (complete certificat) must be generated by Visual Studio How to: Sign an Assembly with a Strong Name

and this certificate needs to be split into public/private keys using the PVKConverter

for getting 2 files : certificatename .cert and certificatename .pvk

The project is configured to deploy the certificate into MS SQL Server, at the condition, there are the 3 available files in the solution:

• in SQLServerFunctions project path: for example SQLServerPK.pfx
• in Security folder: the public part : for example SQLServerPK.pfx_1.cer
• in Security folder: the private part: for example SQLServerPK.pfx_1.pvk

SQL Functions for Internal Rate of Return

1. IRR

• Syntax 1:

IRR(values, [guess])

WITH #Cash AS (
  select <column> from <Table> where <event> in (...) 
)
SELECT dbo.IRR(#Cash)
GO

• Syntax 2 IRR(values, floatingPoints, )

2. XIRR

Financial C# functions

A pure C# project is associated to the SQL Server project to implement the interface with Datastructure, and some controls

Interest Rate Of Return

IRR

Returns the internal rate of return for a series of cash flows represented by the numbers in values.

XIRR / Money-Weighted Rate of Return

XIRR or Money-Weighted Return is the rate given by the equation

$$\sum_n \cfrac{CashFlow_n}{(1 + XIRR)^\frac{\#days_n}{365}} = 0$$

$$\sum_n \cfrac{CashFlow_n}{(1 + XIRR)^\frac{#days_n}{365}} = 0$$

Use XIRR to calculate an internal rate of return for a series of cash flows on different dates.

Signature of the method is public static double XIRR(List<Double> cashflows, List<DateTime> dates, int maxFloatingPoints = 3, double maxRate = 100000)

Example:

var cashFlows = new List<Double>() { -10000, 2750, 4250, 3250, 10000 };
var cashFlowsDates = new List<DateTime>() { DateTime.Parse("01/01/2008"), DateTime.Parse("01/03/2008"), DateTime.Parse("30/10/2008"), DateTime.Parse("15/02/2009"), DateTime.Parse("01/04/2009") };
var xirr = Financial.XIRR(cashFlows,cashFlowsDates, 6);

Time-Weighted Rate of Return

This method requires to get for each equal sub-period : the value and if an external flow occurs, the flow (at the beginning or at the end of the sub-period)

from GIPS

Valuing the portfolio and calculating interim returns each time there is an external cash flow results in the most accurate method to calculate the time-weighted rates of return.

In each period of time, the time-weighted portfolio return is:

When an external cash flow occurs at the beginning of the period :

$$r_i = \cfrac{V_i^{End} - V_i^{Begin} - C_i}{V_i^{Begin} + C_i}$$

$$r_i = \cfrac{V_i^{End} - ( V_i^{Begin} + C_i)}{V_i^{Begin} + C_i}$$

When an external cash flow occurs at the end of the period :

$$r_i = \cfrac{V_i^{End} - C_i - V_i^{Begin}}{V_i^{Begin}}$$

$$r_i = \cfrac{V_i^{End} - C_i - V_i^{Begin}}{V_i^{Begin}}$$

$$Where r_i = the return for period i V_i^{End} = the ending value of the portfolio for period i V_i^{Begin} = the beginning value of the portfolio for period i C_i = an external cash flow at the beginning of the period$$

When a portfolio experiences external cash flows during a period, the most accurate return is calculated by valuing the portfolio at the time of the external cash flow, calculating the time-weighted return for each sub-period (defined as the period between external cash flows), and then geometrically linking the sub-period returns using the following formula:

$$r_t^{TWR} = \bigg[ ( 1 + r_1 ) ( 1+ r_2 ) ... (1 + r_I ) \bigg] - 1 where r_t^{TWR} is the time-weighted return for period t and period t consists of I sub-periods.$$

$$r_t^{TWR} = \bigg[ ( 1 + r_1 ) ( 1+ r_2 ) ... (1 + r_I ) \bigg] - 1$$

Please refer to https://www.gipsstandards.org/standards/Documents/Guidance/gs_calculation_methodology_clean.pdf https://en.wikipedia.org/wiki/Time-weighted_return

TWROR

True time-weighted rate of return

Modified Dietz method (approximate the TWR)

$$Modified Dietz Return = \cfrac{V^{End} - V^{Begin} - \sum CashFlow}{V_^{Begin} + \sum (Cashflow*weighting_Factor)}$$

$$Modified Dietz Return = \cfrac{V^{End} - V^{Begin} - \sum CashFlow}{V_^{Begin} + \sum (Cashflow*weighting_Factor)}$$

where weighting_Factor is the ratio of number of days cash flows appears on total number of days

weighting_Factor =

to follow the same functionnalities than http://westclintech.com/SQL-Server-Financial-Functions/SQL-Server-XIRR-function https://www.spreadsheetml.com/financialmodeling/time_weighted_rate_of_return.shtml

• Rates of Return - IRR, XIRR, NPV, RATE, DIETZ, etc.
• Bond Figurations - ACCRINT, PRICE and YIELD for ODD-First and ODD-Last coupons, etc.
• Capital Asset Pricing Model - INFORATIO, SHARPE, SORTINO, TREYNOR, etc.
• Loans - PMT, IPMT, CUMPRINC, AMORTSCHED, etc.
• Depreciation - DB, DDB, SLN, SYD,VDB
• Yield Curve Construction - SWAPCURVE, NELSONSIEGEL, INTERPDFACT, ZEROCOUPON, etc.
• Business Dates - EOMONTH, FIRSTWEEKDAY, BUSDAYS, DAYS360, YEARFRAC, TENOR2DATE, etc.