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.