Financial Functions for .NET released !

-

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.
CoupDays
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.
VDB
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

Tags

12 Comments

Comments

Development in a Blink &#38;ra

2008-12-04T18:48:30Z

PingBack from http://​dougfinke.com/​blog/?​p=508

configurator

2008-12-07T08:40:40Z

Couldn’t you have done this a few months ago, be­fore I im­ple­mented my own?
Though now I have op­ti­mized func­tions that do FV pro­gres­sion ta­bles and other in­ter­est­ing things so per­haps it’s a good thing you did­n’t :)
Anyway, good job copy­ing Excel func­tions. I’m sure it’ll be use­ful for many peo­ple

Don Syme's WebLog on the F# La

2008-12-08T07:35:04Z

The one and only Luca Bolognese has re­leased his model .NET im­ple­men­ta­tions of Excel fi­nan­cial func­tions

Iain Holder

2008-12-08T10:05:53Z

Excellent work.  You did this at the same time as chang­ing nap­pies?

greg

2008-12-08T11:55:42Z

Can this be built into .NET 4.0 as a stan­dard set of func­tions with nor­mal MSDN doc­u­men­ta­tion and full sup­port from Microsoft?  

Piotr Zurek

2008-12-09T17:38:47Z

Hi Luca,
Thanks for re­leas­ing all that code. It’s a great source of knowl­edge about F#.
Looking through the code of the com­mon.fs file I found that:
   let sqr x = Math.Sqrt(x)
Was it re­ally in­tended?

lucabol

2008-12-09T17:51:09Z

Piotr: It’s a be­nign typo. I just checked and this is used in just one place in the code (inside the TBillEq func­tion). BTW: sqrt al­ready ex­ist in F#.
Iain: yep :)
Greg: we have no plan of do­ing that. Sorry. If there is a lot of re­quest for it, we might con­sider it.

gOODiDEA.NET

2008-12-10T22:06:35Z

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

gOODiDEA

2008-12-10T22:09:30Z

Web3reasonswhyyoushouldletGooglehostjQueryforyou链接A引发的思考YUILibrary,YUIDo…

#.think.in

2008-12-14T17:14:08Z

#.think.in in­fo­Dose #11 (8th Dec - 12th Dec)

Luca Bolognese's WebLog

2009-02-05T11:47:33Z

When I wrote my Excel fi­nan­cial li­brary I ag­o­nized over the de­ci­sion of which nu­meric type to use to

ASP.NET Debugging

2009-02-11T12:20:44Z

Have peo­ple started to use this yet?&#160 I was just in­tro­duced to it and think it has a lot of po­ten­tial