Financial.IRR not calculated in C#

Question

I am not able to calculate IRR. I am usign Microsoft.VisualBasic to calculate IRR. Here is a case:

using Microsoft.VisualBasic;

...

static void Main(string[] args)
        {
            double[] tmpCashflows = new double[] {
                -480117.0,
                4471.5158140594149,
                6841.5950239895265,
                6550.383550359461,
                6295.8611873818609,
                6074.6070899770129,
                5883.532880960287,
                6006.9907860976427,
                6133.1633945923877
                ,6262.1156759885489
                //,6393.9143799520116            
            };

            decimal irr = 0;
            try
            {
                double tmpIrr = Financial.IRR(ref tmpCashflows);                
                ...
            }
            catch (Exception ex)
            {
                irr = 0;
            }


        }

It gives exception of type "Argument is not valid" (at Microsoft.VisualBasic.Financial.IRR(Double[]& ValueArray, Double Guess)). However, this doesn't show any errors if I do calculations inside Excel.

Rimose

Answer 1

You need to provide a good Guess value as the 2nd parameter to this API. The default value of 0.1 fails for your inputs.

Try this:

double tmpIrr = Financial.IRR(ref tmpCashflows, 0.3);

and you should get back an IRR of -0.2987

It looks like the API handles only specific cases of inputs and fails on others for any guess. This is an IRR API Bug for Microsoft acknowledged here.

You're better off calculating IRR using a 3rd party reliable library,if Guess becomes an issue.

http://connect.microsoft.com/VisualStudio/feedback/details/781299/microsoft-visualbasic-financial-irr-doesnt-solve-for-irr-correctly-in-certain-cases-with-any-guess

The reason you get an Argument is not valid exception is because the IRR cannot be calculated for the default Guess value of 0.1. Note that the given values satisfy the condition of one negative value (payment) and one positive value. (receipt)

Internally, **Microsoft.VisualBasic.Financial.IRR** goes through the input values and tries to calculate the IRR over 40 iterations. It never encounters the breaking condition for valid IRR and hence finally throws this error.

Note that Excel gives a value of -30%.

At the same time, trying the IRR for an array like

tmpCashflows = new double[] { -100, 200, 300 };
double tmpIrr = Microsoft.VisualBasic.Financial.IRR(ref tmpCashflows);

gives an IRR of 2.0 or 200%. (both via code and Excel)

Gambier

these actually are correct data. IRR becomes positive after 60 months. Unfourtunately this function is not able to show me the positive value. It always gives me error if first 10 items are as you see above. – Rimose

little more research and code found the Microsoft bug details – Gambier

Answer 2

public static class Interpolation
{
    public static double IRR(this Dictionary<double, double> cashFlow)  //term in years, amount
    {
        int count = 0;
        double r1=0.1d, r2=0.05d, v1, v2, r = 0, v;
        v1 = cashFlow.DiscountedValue(r1);
        v2 = cashFlow.DiscountedValue(r2); 
        while (Math.Abs(v2 - v1) > .001 && count < 1000)
        {
            count++;
            r = (0 - v1) / (v1-v2) * (r1 - r2) + r1;
            v = cashFlow.DiscountedValue(r);
            v1 = v2;
            r1 = r2;
            v2 = v;
            r2 = r;
        }
        if (count == 1000) return -1;
        return r;
    }



    public static double DiscountedValue(this Dictionary<double, double> cashFlow, double rate)
    {
        double dv = 0d;
        for (int i = 0; i < cashFlow.Count; i++)
        {
            var element = cashFlow.ElementAt(i);
            dv += element.Value * Math.Pow(1 + rate, -(element.Key));
        }
        return dv;   
    }
}

Accident

This generally works for "normal" cashflows. You can use any duration for each cashflow item including fractions of a year. – Accident