Financial Functions for .NET released ! - Luca Bolognese

Financial Functions for .NET released !

Luca -

☕ 3 min. read

Today I re­leased the fol­low­ing li­brary on CodeGallery. It is the re­sult of three months of cod­ing dur­ing my pa­ter­nity leave in Italy. You can get it from here.

What is it?
This is a .NET li­brary that pro­vides the full set of fi­nan­cial func­tions from Excel. The main goal for the li­brary is com­pat­i­bil­ity with Excel, by pro­vid­ing the same func­tions, with the same be­hav­iour. Note though that this is not a wrap­per over the Excel li­brary; the func­tions have been re-im­ple­mented in man­aged code so that you do not need to have Excel in­stalled to use this li­brary. 
** Where I can find doc­u­men­ta­tion on these func­tions?
Just open Excel and click on Formulas/Financial or go to this
I don’t think one of the func­tion is right. Excel pro­duces the wrong re­sults! Why don’t you do it right?

My goal is to repli­cate Excel re­sults (right and wrong). Feel free to con­tribute to the ef­fort by cod­ing what you think is the right so­lu­tion and I’ll add an ExcelCompliant flag to the func­tion to con­di­tion­ally in­voke your code.
How do I use the li­brary?**
Just add Financial.dll to the ref­er­ences in your pro­ject. The func­tions are pro­vided as sta­tic meth­ods on a Financial class in the System.Numeric name­space
I see the li­brary was im­ple­mented with F#. But I don’t want to re­dis­trib­ute F# along with my ap­pli­ca­tion. What should I do?
There are two ver­sions of the li­brary. One of them sta­t­i­cally links the F# li­braries so that there is no de­pen­dency on F#. However, this as­sem­bly larger, so if you have F# in­stalled, you can use the FinancialNotStandalone.dll in­stead.
How do I run the tests?**
Run FinancialTests.exe. You need Excel 12 for the tests to work be­cause they use Excel to test that the re­sults are cor­rect. You don’t need Excel 12 to use the li­brary in your own ap­pli­ca­tion.
How do I com­pile the li­brary?**
You need to have F# September CTP in­stalled (you can get it from here). There are two batch files (CreateLibraryStandalone.bat and CreateLibraryNotStandalon.bat). Run them to com­pile the dll. You might have to change the path to the F# com­piler in­side these files
How do I com­pile the tests?**
Run CreateTests.bat
Have you tested this thing?**
Yes, I do have 201,349 test­cases run­ning against it. You can eas­ily raise that num­ber sig­nif­i­cantly by adding new val­ues to test in test­def.fs. If you have a mul­ti­proc ma­chine the test­cases will run faster as I par­al­lelize their ex­e­cu­tion.
Have you run per­for­mance tests on it?**
Not at all. The only thing I checked is that all the re­cur­sive func­tions are tail re­cur­sive. Feel free to let me know if they are slow.
Are there any func­tions that be­have dif­fer­ent from Excel?**
Yes, there are two of them.
The Excel al­go­rithm seems wrong in that it does­n’t re­spect the fol­low­ing:
coupDays = coup­DaysBS + coup­DaysNC.
This equal­ity should stand. By man­u­ally count­ing the days, I’m pretty con­fi­dent that my al­go­rithm is cor­rect.My re­sult dif­fers from Excel by +/- one or two days when the date spans a leap year.
In the ex­cel ver­sion of this al­go­rithm the de­pre­ci­a­tion in the pe­riod (0,1) is not the same as the sum of the de­pre­ci­a­tions in pe­ri­ods (0,0.5) (0.5,1).
VDB(100,10,13,0,0.5,1,0) + VDB(100,10,13,0.5,1,1,0) <> VDB(100,10,13,0,1,1,0)
Notice that in Excel by us­ing 1’ (no_switch) in­stead of 0’ as the last pa­ra­me­ter every­thing works as ex­pected. The last pa­ra­me­ter should have no in­flu­ence in the cal­cu­la­tion given that in the first pe­riod there is no switch to sln de­pre­ci­a­tion.
Overall, I think my al­go­rithm is cor­rect, even if it dis­agrees with Excel when start­pe­riod is frac­tional.
Can you list the func­tions with their test­cases re­sults?**
Succeeded 1840/1840 for PV
Succeeded 2024/2024 for FV
Succeeded 2240/2240 for PMT
Succeeded 853/853 for NPER
Succeeded 5355/5355 for IPMT
Succeeded 5355/5355 for PPMT
Succeeded 208/208 for CUMIPMT
Succeeded 208/208 for CUMPRINC
Succeeded 624/624 for ISPMT
Succeeded 12/12 for FVSCHEDULE
Succeeded 9/9 for IRR
Succeeded 21/21 for NPV
Succeeded 147/147 for MIRR
Succeeded 18/18 for XIRR
Succeeded 396/396 for DB
Succeeded 24/24 for SLN
Succeeded 132/132 for SYD
Succeeded 456/456 for DDB
Succeeded 2544/2544 for VDB ex­clud­ing frac­tional start­dates
Succeeded 11520/11520 for AMORLINC
Succeeded 23040/23040 for AMORDEGRC
Succeeded 15/15 for COUPDAYS ex­clud­ing leap years
Succeeded 915/915 for COUPDAYSBS
Succeeded 915/915 for COUPDAYSNC
Succeeded 915/915 for COUPNUM
Succeeded 915/915 for COUPPCD
Succeeded 915/915 for COUPNCD
Succeeded 360/360 for ACCRINTM
Succeeded 1920/1920 for ACCRINT
Succeeded 10980/10980 for PRICE
Succeeded 1940/1940 for PRICEMAT
Succeeded 2910/2910 for YIELDMAT
Succeeded 1395/1395 for YEARFRAC
Succeeded 2745/2745 for INTRATE
Succeeded 1290/1290 for RECEIVED
Succeeded 2745/2745 for DISC
Succeeded 3660/3660 for PRICEDISC
Succeeded 2745/2745 for YIELDDISC
Succeeded 48/48 for TBILLEQ
Succeeded 69/69 for TBILLYIELD
Succeeded 81/81 for TBILLPrice
Succeeded 12/12 for DOLLARDE
Succeeded 12/12 for DOLLARFR
Succeeded 12/12 for EFFECT
Succeeded 12/12 for NOMINAL
Succeeded 5490/5490 for DURATION
Succeeded 5490/5490 for MDURATION
Succeeded 19320/19320 for ODDFPRICE
Succeeded 30600/30600 for ODDLPRICE
Succeeded 45900/45900 for ODDLYIELD
Test Cases Succeeded 201349/201349



Development in a Blink &#38;ra




Couldn't you have done this a few months ago, before I implemented my own?
Though now I have optimized functions that do FV progression tables and other interesting things so perhaps it's a good thing you didn't :)
Anyway, good job copying Excel functions. I'm sure it'll be useful for many people

Don Syme's WebLog on the F# La


The one and only Luca Bolognese has released his model .NET implementations of Excel financial functions

Iain Holder


Excellent work.  You did this at the same time as changing nappies?

Can this be built into .NET 4.0 as a standard set of functions with normal MSDN documentation and full support from Microsoft?  

Piotr Zurek


Hi Luca,
Thanks for releasing all that code. It's a great source of knowledge about F#.
Looking through the code of the common.fs file I found that:
   let sqr x = Math.Sqrt(x)
Was it really intended?

Piotr: It's a benign typo. I just checked and this is used in just one place in the code (inside the TBillEq function). BTW: sqrt already exist in F#.
Iain: yep :)
Greg: we have no plan of doing that. Sorry. If there is a lot of request for it, we might consider it.



Web 3 reasons why you should let Google host jQuery for you 链接A引发的思考 YUI Library, YUI Doc, and Github

Web3reasonswhyyoushouldletGooglehostjQueryforyou链接A引发的思考YUILibrary,YUIDo... infoDose #11 (8th Dec - 12th Dec)

Luca Bolognese's WebLog


When I wrote my Excel financial library I agonized over the decision of which numeric type to use to

ASP.NET Debugging


Have people started to use this yet?&#160 I was just introduced to it and think it has a lot of potential

0 Webmentions

These are webmentions via the IndieWeb and