XIRR

?

https://www.experts-exchange.com/questions/10329251/Calculate-XIRR.html

If funcres.xla is not the right file, try atpvbaen.xls

Patrick

sorry for the delay

but idont want such dependencies.

client maynot have the Addin or even excel for the matter

doesnt anyone have the formula or library which does this

Gerry,

In my opinion, I should be given credit for the answer.  Sometimes, the right answer is "you cannot
do what you are trying to do", even if that is the last answer the asker wants to hear.

To find the internal rate of return for an arbitrary stream of cash flows, which is what XIRR() does,
you need a complicated iterative process.  The Analysis ToolPak's formula ties this complex process
in a neat little bow.

Yes, one could spend a lot of time and come up with a routine that will run the calculations.  However,
given the nature of the problem, it is actually quite likely that a "homemade" analogue would return
different answers.  (And further complicating the issue is that, for some sets of cash flows, more than
one answer is possible!)

So, my (admittedly self-serving) recommendation is that I get the answer, with a B grade.

Regards,

Patrick

patrick ur answers are incorrect

i did find the solution elsewhere.

and will post the answer here myself.

10x anyway

Set AnalysisApp = CreateObject("Excel.Application")
Set AnalysisWkb = AnalysisApp.Workbooks.Open(MSOfficePath & "\Analysis\ATPVBAEN.XLA")
AnalysisWkb.RunAutoMacros xlAutoOpen
Result = AnalysisApp.Application.Run(AnalysisWkb.Name & "!XIRR", TrxAmt, TrxDate, 1) * 100


this is the code that i used.

and to eleminate the dependancy i bundled these files with my installer

Thank you for your assistance, PAQ_Man, and thank you Gerry for your time on Cleanup.

Regards,

Patrick

XIRR C# version.
based on a vba code I foound in the forum.

    public double XIRR(List<double> cashflow , List<DateTime> dates, double guess)
    {      
      int k,m;
      double calc, sumcalc=0, rate=0, calc2, sumcalc2=0, next_rate=guess;
      TimeSpan ts;
   
      for (; ; sumcalc2 = sumcalc = 0, rate = next_rate)
      {    
        for (k = 0; k < cashflow.Count; k++)
        {
          ts = dates[k] - dates[0];
          calc = cashflow[k] / (Math.Pow((1 + rate), (ts.Days / 365.0)));                        
          sumcalc += calc;
        }
       
        if(Math.Abs(sumcalc) < 0.000001 )
          break;

        for (m = 0; m < cashflow.Count; m++)
        {
          ts = dates[m] - dates[0];
          calc2 = -1.0 * (ts.Days / 365.0) * cashflow[m] * (Math.Pow((1 + rate), (-1 * (ts.Days / 365.0) - 1)));
            sumcalc2 +=calc2;
        }          
        next_rate = rate - sumcalc / sumcalc2;
      }
 
      return rate;
    }