Skip to main content

Excel XIRR and C#

I have spend that last couple days trying to figure out how to run and Excel XIRR function in a C# application. This process has been more painful that I thought it would have been when started. To save others (or myself the pain in the future if I have to do it again) I thought I would right a post about this (as post about XIRR in C# have been hard to come by).

Lets start with the easy part first. In order to make this call you need to use the Microsoft.Office.Interop.Excel dll. When you use this dll take note of what version of the dll you are using. If you are using a version less then 12 (at the time of this writing 12 was the highest version) you will not have an XIRR function call. This does not mean you cannot still do XIRR though. As of version 12 (a.k.a Office 2007) the XIRR function is a built in function to Excel. Prior version need an add-in to use this function. Even if you have version 12 of the interop though it does not mean you will be able to use the function. The ability to use the function is determined by the version of Office install on the machine hosting the application. So if your interop dll version is 12 but the host machine only has version 11 installed you cannot directly call the XIRR function even though the Interop dll gives you that method. If however the host machine does have office 12 installed you can call the XIRR function directly.

Ok lets get into the details of doing it. First add the Microsoft.Office.Interop.Excel.dll into your project references (It is best to import the latest version of the dll that you have).  Now create your excel object as in the code below:

// value to store the results
double xirrValue;

// create the new excel interop object 
ApplicationClass xlApp = new ApplicationClass();

Now we get to the tricky part (or at least it was for me). There are really two things we are going to check here. First we need to check what version of office is installed. If the version of office is greater than 11 the call is much easier. If the version of office is less then 11 we have more work to do.

Since versions of office before 12 don't include the XIRR function we have to register the Analysis tool box add-in. To register the add-in we make the xlApp.RegisterXLL call in the code below. Once the add-in has been register we can make a call to the XIRR macro using the Run() method.

Now there is one last issue to get by. The Run() method take something like 30 optional parameters. Now if you were doing this in VB.net this would not be an issue as you can just not pass in those extra optional parameters. However, for C# you have to pass something in for each parameter. You may think you can pass in null for these extra parameters but you would be wrong. You have to pass in System.Reflection.Missing.Value (thanks to Gabhan Berry for setting me straight on this).

// See what version of Excel is installed
if (Decimal.Parse(xlApp.Version) > 11)
{
    xirrValue = xlApp.WorksheetFunction.Xirr(_cash.ToArray(), _dates.ToArray(), 1);
}
else
{
    xlApp.RegisterXLL(xlApp.Path + @"\Library\Analysis\ANALYS32.XLL");
    xirrValue = (double)xlApp.Run("XIRR", _cash.ToArray(), _dates.ToArray(), 1,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value);
}

The rest of the code example above shows you what else needs to be done to get you there.

Comments

Anonymous said…
Are _cash and _dates List type of strings?
The Toad said…
Yes, they are both type of string
Aman said…
ToArray() method is not identified in string. my code is failing. If I use string array so as to get around ToArray() an exception is thrown "Specified cast is not valid"
The Toad said…
Sorry, I just went back and double checked my code and they are not strings. Here is the code that declares my _dates and _cash objects. I hope this helps, sorry about th confusion.

List<DateTime> _dates = new List<DateTime>();
List<Double> _cash = new List<Double>();
Anonymous said…
is there a possibility of it working with .net version 1.0

List&ltDateTime&gt _dates = new List&ltDateTime&gt();
does not work in version 1.0
thanks in advance for any help!
The Toad said…
Yes. You should just be able to create those objects as Arrays of type DateTime and Double. This is what the generic list has to be converted to before passing into the method anyway. You just have to do the work of managing the array sizes and the locaton at which each value is inserted.
Anonymous said…
I did try it but I got the same error. The code I tried was


Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass ();
Double [] i = new Double [2];
i[0] = 100;
i[1] = 200;

DateTime [] d = new DateTime [2];
d[0] = DateTime.Today;
d[1] = DateTime.Today;

xlApp.RegisterXLL(xlApp.Path + @"\Library\Analysis\ANALYS32.XLL");
double xirrValue = (double)xlApp.Run("XIRR", i , d,
1,
System.Reflection.Missing.Value,
--- filling all 30 params)

I am getting an error saying cast is not valid
The Toad said…
Not sure why that would not work. Couple of things you may want to try.

1- Create your arrays as just object types.
Object[] i = new Object[2];
i[0] = 100;
i[1] = 200;

2- Make sure you what version of the interop assembly you are using and that it in fact has an XIRR method.
Unknown said…
Hi,

I have refered excel interop 12.0 in my project reference. but when i run the project it shows the version as 11.0 . plesae can you help me to solve this problem
The Toad said…
what version of office do you have installed on your machine? It is not really about what version of the dll you reference in your project it is about what version of office is installed on the machine.
Unknown said…
I have got office 2007 installed on my machine.
When i tried to use XIRR in my program i got error : System.Runtime.InteropServices.COMException: Member not found.
then I checked the vesrion it is showing 11.0 instead of 12.0 (xlApp.version code in program)
RK said…
Thx a million for this posting. The sample code worked like a charm.
RK said…
In one scenerio the code is not working.

Please see below the list of the Dates and the Cash flow. When I do that in excel I am getting XIRR Value as -0.480460286 where as when using your code it throws an expection. But when the number of rows is around 15 it works fine

Any Idea why its failing and how can I resolve that?


3/21/2007 -75000000
7/26/2007 -71000000
9/27/2007 -75000000
11/1/2007 6161033
2/1/2008 5936172
2/19/2008 4981313
4/18/2008 12823620
5/1/2008 7069359
5/19/2008 6561657
7/18/2008 3925164
8/1/2008 4970675
8/18/2008 5381800
10/20/2008 3753937
11/3/2008 4959807
11/18/2008 3828235
1/20/2009 1970205
2/2/2009 5374056
2/18/2009 7224140
4/20/2009 5300443
5/1/2009 5797191
5/18/2009 4960104
5/31/2009 10

Thanks
RK
Nitin said…
Thanks For help..its working.

Popular posts from this blog

Experience Profile Anonymous, Unknown and Known contacts

When you first get started with Sitecore's experience profile the reporting for contacts can cause a little confusion. There are 3 terms that are thrown around, 1) Anonymous 2) Unknown 3) Known. When you read the docs they can bleed into each other a little. First, have a read through the Sitecore tracking documentation to get a feel for what Sitecore is trying to do. There are a couple key things here to first understand: Unless you call " IdentifyAs() " for request the contact is always anonymous.  Tracking of anonymous contacts is off by default.  Even if you call "IdentifyAs()" if you don't set facet values for the contact (like first name and email) the contact will still show up in your experience profile as "unknown" (because it has no facet data to display).  Enabled Anonymous contacts Notice in the picture I have two contacts marked in a red box. Those are my "known" contacts that I called "IdentifyAs"

Uniting Testing Expression Predicate with Moq

I recently was setting up a repository in a project with an interface on all repositories that took a predicate. As part of this I needed to mock out this call so I could unit test my code. The vast majority of samples out there for mocking an expression predicate just is It.IsAny<> which is not very helpful as it does not test anything other then verify it got a predicate. What if you actually want to test that you got a certain predicate though? It is actually pretty easy to do but not very straight forward. Here is what you do for the It.IsAny<> approach in case someone is looking for that. this .bindingRepository.Setup(c => c.Get(It.IsAny<Expression<Func<UserBinding, bool >>>())) .Returns( new List<UserBinding>() { defaultBinding }.AsQueryable()); This example just says to always return a collection of UserBindings that contain “defaultBinding” (which is an object I setup previously). Here is what it looks like when you want to pass in an exp

Security Config in IIS Express

I have gotten tired of always having to look this up or remember where it is at. That means it is time to post to my blog so I can find it easier and hopefully others can too. If you are having issues with IIS Express authentication errors (like the Unauthorized 401.2 error I always get) here is some help. I can never remember what the last setting was I had IIS Express set to for authorization. To change IIS Express for windows auth or anonymous auth you want to work with the applicationhost.config file. It can be found here …Documents\IISExpress\config. You want to change the settings in the following area of the config file. < authentication > < anonymousAuthentication enabled ="true" userName ="" /> < basicAuthentication enabled ="false" /> < clientCertificateMappingAuthentication enabled ="false" /> < digestAuthentication enabled ="false" />

WPF Localization - RESX Option

About a year ago I was building a WPF project in .Net 3.0 and Visual Studio 2005. I wanted to revisit this subject and see what has changed in .Net 3.5 and Visual Studio 2008. I will make a few of these posts to try and cover all the different options (RESX option, LocBaml option, Resource Dictionary Option). In this blog I will focus on using a resx file to localize an application. To show how the resx option is done I created a WPF form with three labels on it. The first label has is text set inline in XAML, the second has it text set via code behind from the resx file and the third has its text set via XAML accessing the resx file. The first thing that needs to happen to setup a project for localization is a small change to the project file. To make this change you will need to open the project file in notepad (or some other generic editor). In the first PropertyGroup section you need to add the follow XML node <UICulture>en-US</UICulture>. So the project file node w