I've used Excel's XIRR function in hundreds of formulae.I recently came across an instance in which Excel's default guess of .1 does not yield a plausible result.This is with Excel 2003.Opening the workbook with Open Office 2.3' Calc does yield a plausible result.If I explicitly give th function a starting guess of -.1, I get a plausible result, same result as in Calc without specifying a guess.So, my question is "What's the best/good way to determine a guess value.Does the following make sense?=XIRR(B3:B44, A3:A44, SIGN(SUM(B3:B44)) * .1)Here's an example without the guess.

Yeah - there will be cases where Excel's default guess doesn't get you to the right answer - I guess it isn't optimised for cases where the IRR is negative. It may even be the case that it doesn't work properly - I notice that XNPV doesn't work in the case you sent (if we change the discount rate to -9.8%). Your approach seems ok, but I'd probably want to get a better understanding of the XIRR methodology before being sure that it will always work.

QuoteOriginally posted by: gjlipmanYeah - there will be cases where Excel's default guess doesn't get you to the right answer - I guess it isn't optimised for cases where the IRR is negative. It may even be the case that it doesn't work properly - I notice that XNPV doesn't work in the case you sent (if we change the discount rate to -9.8%). Your approach seems ok, but I'd probably want to get a better understanding of the XIRR methodology before being sure that it will always work.Given the market recently, I find that XIRR does produce negative numbers, of course, I have no way of knowing whether they are correct.The problem is that I am going to programmatically create an Excel workbook,including the XIRR formulae.This would be a template into which the user would provide the relevantdata.As far as I know, I've only run into this problem in the case I posted.I guess that I'll stick with the defaults.Another possibility for a guess might be B44/SUM(B3:B43))-1.Note that Calc in Open Office 2.3 produced a plausible result.

I just found a 2nd example of the problem.For both cases, Calc in Open Office 2.3 gives plausible results.In the example I posted, the cash flow is in a retirement accout that had a 0 balance on 26 Dec 2006, with subsequent contributions about every 2 weeks.THe other case is a sincgle mutual fund withing th portfolio, using part of the contributions, and, as I recall, one transfer out to another fund.Given the market fluctuations since 26 Dec 2006, I guess Excel's implementation of the XIRR algorithm just cannot handle this.Seems that I better put in a guess when I create the workbook.

The following seems to work for positive and negative returns:=XIRR(F3:F45, E3:E45,SUM(F3:F44)/F45+1)But I do not think that it is right, e.g., if F45 were 0 or negative.Heck, I'm just gonna stick with the default and blame it on Microsoft.

If this is a problem for you, why don't you code up your own IRR that uses best practice logic (which I admit I don't know what it is). Maybe even try and get hold of the Open Office algorithm. I mean, XIRR is just an addin function to Excel anyway - to use if and only if you find it useful.

The number of solutions to the internal rate of return equation equals to the number of times the cash flows change sign.So, if your cash flows look like this:You buy a bond at a price not very far from par (negative cash flow)You collect coupons (positive cash flows)You get the principal at maturity (positive cash flow)Then the cash flows change sign only once, and there's just one internal rate of return.XIRR will return the unique solution no matter what your initial guess is.if you have negative cash flows in the middle of your time series, then the solution is not unique; and indeed multiple solutions may be "plausible".

I agree with you, AlphaNumericus, but in this particular case the solution Excel was giving wasn't even a solution, which I guess is more of a concern.

QuoteOriginally posted by: gjlipmanIf this is a problem for you, why don't you code up your own IRR that uses best practice logic (which I admit I don't know what it is). Maybe even try and get hold of the Open Office algorithm. I mean, XIRR is just an addin function to Excel anyway - to use if and only if you find it useful.In Excel 2007, XIRR is not an addin, it is part of the native functions.In my case, it would be inappropriate for me to code my own XIRR, but I cannot go into details why at this time.

I used XNPV to verify that the NPV of the stream is not 0 using the return given by XIRR.I then tried to use Goal Seek (never used it before).Given =XIRR(D3:D44, C3:C44), which returns -0.09833663106 in H2, =XNPV(H2,D3:D44, C3:C44) returns -2820.37.I then tried Goal Seek to determine the right value for H2, so I copied the raw value to H11 and used Goal Seek with =XNPV(H11,D3:D44, C3:C44) which results in error "Formula in cell must result in a number".Goal Seek returns an error if the rate in H11 is negative.Am I doing anything wrong?

Two things for a start - you're using IRR rather than NPV and some of your examples are so pathological that there might be multiple answersAnd a third thing - you're blaming Excel - all optimisation problems require sensible starting points and some checking to ensure that you end up with a global not just a local solution

Excel may be using inferior algorithms, Calc gives the right answer for both XIRR and XNPV.

Excel may be using inferior algorithms, Calc gives the right answer for both XIRR and XNPV.

If you're suggesting that the algorithm that Excel gives away for free to every Excel user isn't the optimum algorithm for every scenario, I don't think anyone will be surprised. It is designed to be a "jack of all trades", but even Microsoft acknowledge that there'll be times when you need specialist addins, or even other programmes altogether.If you're not happy with Excel's XIRR or XNPV, don't use them - spend your effort convincing your boss to let you use something up to your requirements, rather than trying to convince us of something we already know.

This app has to use Excel.I might consider writing my own XIRR if I could find a book, or published source, describing the numerical analysis details of the algorithm.