XIRR

Marhefkova 

This is an __Excel__ forum, not about Google Sheets.

Oddly, when I open the Google Sheets file, I do not see any XIRR error.

And with a new Google Sheets of my own, I confirm that GS XIRR is able to calculate the IRR even without a "guess".  My own VBA user-defined function also can calculate the IRR, but only with a "guess".

So, it might be better if you upload the Excel file to a file-sharing website instead of copying(?) the data into a Google Sheets file.

-----

When I initially download the Google Sheets file to Excel, Excel XIRR displays a #NUM error initially.  But after recalculation, it turns into a #VALUE error.

It is unclear whether some of the "mistakes" below are real, or just a side-effect of downloading Google Sheets into Excel with different regional/language settings.

For what it is worth, these are the mistakes that I see.

1. The form of the dates in N29 and N30 cannot be recognized by Excel -- at least, not with my regional/language settings.  Change N29 and N30 to the same form that you entered in N3:N28.

2.  The sign of the first cash flow (O3) is the same as the last cash flow (O28, by date).  That is "unusual", since the last cash flow should be the net FV of the cash flows; that is, the final balance __before__ any withdrawal -- a positive value, when PV is negative.

Usually, that reflects a misunderstanding and mistakes in how the cash flows are created.

However, I have seen both negative PV and negative FV in some cases where the "cash flows" are net earnings, which could go negative (business loss) at the end.

So, please explain the nature of the cash flows.  Specifically, what do the first and "last" (12/28/2021 in row 28) cash flows represent?  What do the in-between cash flows represent?

3. After correcting #1 above,  I am able to calculate an IRR of -78.7196578523888% by using Solver.  That is about the same result that I see in O2 in Google Sheets (and in my GS file).

To use Solver to find the IRR, I did the following.

a. In U8, enter the formula

=SUMPRODUCT($O$3:$O$30/(1+T8)^(($N$3:$N$30-$N$3)/365))

b. In Solver:

Set Objective: U8

To Value Of: 0

By Changing: T8

Subject to Constraints:

    T8 <= -75%

    T8 >= -80%

Options:

    All Methods > Constraint Precision: 1E-12

    GRG Nonlinear > Convergence: 1E-12

Method: GRG Nonlinear

The constraints are necessary so that Solver does not encounter an error during iterations.

I determined the constraints by calculating the NPV for a range of discount percentages from -99.99% to 100%; that is:  -99.99%, -90%, -85%, etc.  With the range of discount percentages starting in Q3, the NPV formula is:

=SUMPRODUCT($O$3:$O$30/(1+Q3)^(($N$3:$N$30-$N$3)/365))

formatted as Scientific.  The IRR(s) might be between discount percentages where the NPV changes sign.

I see one sign change between -80% and -75%. 

FYI, I use SUMPRODUCT instead of XNPV because Excel XNPV does not permit negative discount percentages, for no good reason.  It is a design flaw.

4. Thus, XIRR should work with a "guess" that is in between; for example, -77.5%.  But again, Excel XIRR still fails with a #NUM error, inexplicably.

Even if the formula in O2 is =XIRR(O3:O30,N3:N30,-78.72%) , that does not work (in Excel 2010).

The problem might be:  the unreliable implementation of Excel XIRR does not work well with the steep "gradient" (rate of change) of the NPV between -80% (-8.64E+04) and -75% (2.01E+05).