Help with XIRR too high

Off-hand, I see nothing wrong with the XIRR result insofar as XNPV does indeed return nearly zero (-9E-06 and -3E-06) for both examples.

I suspect that something is wrong with your cash flow models. It might help if you explain what the values represent in English. For example, -20,000 is the initial investment. What are 73,375 and 67,782 less than 60 days later?

Those relatively large values in such a short period of time is probably why XIRR returns such a large discount rate (rate of return).

In the first example, it appears that you invest 20,000, then withdraw 73,375 33 days later. 73375/20000 is an apparent return of 367% (!). Annualized (compounded for 365/33 periods), that would be a return of 2,522,127,160% (!).

I suspect that the mistake is: for the "initial investment", you omitted the pre-existing balance.

Moreover, as written, the cash flows suggest an ending balance of zero. I suspect that the last cash flow (4000 in Feb 2015) omits the ending balance.

A similar analysis applies to the second example.

Originally Posted by leguicri

A person grant a loan, in this example, $20000. But, the debtor do not pay at the established moment (the tenth day of each month along a year). Because of that (in this example, the debtor do not pay on mar-10-2014), the creditor charges $73375 in the cashflow, and then the debtor keeps paying 4400. [....] As you saw, the rates are too strange, but they are correct too! XIRR 175482535% is not consistent with reality

I do not agree with the last statement. As you say, they are correct, insofar as XNPV returns nearly zero.

In theory, it is possible that there is more than one IRR mathematically, and Excel XIRR did not find the one that is "representative". Not in this example, IMHO. But to explore that possibility, generate a table that represents the "NPV curve" for varying discount rates, and see if it crosses zero more than once, especially at a point that is less than 175482536%. Very tedious, considering the necessary range. Not worth the trouble, IMHO.

What does seem strange is that the lender would charge 3.5 times the outstanding balance for missing one payment. And that the debtor could afford to pay $73,375, but not $4400.

So again, I am suspicious of the cash flow model, not the Excel XIRR result. GIGO!

But if the cash flows are correct, I see nothing "strange" or "unrealistic" about the results from Excel XIRR per se. But yes, the effective interest rates seem "strange" and "out of this world". "Usurious" would be an understatement.

Thanks joeu2004!

Yes I know, It's not an usual cash flow. However, we're gonna check the datasets. We're analyzing a lot of users with this problem.
Even we develop a little system for data cleaning (the lender's systems was not too reliable)

I'd like to add more details about what are we doing here.
We need to know the IRR of these operations, but we have singular cash flows.

I will try to explain using another example (a bit simpler)
I give you 20000 and you must pay back this money in 9 payments (3718) each one. The payment is 10th of each month. In that moment (when you receive the money) you must sign a Promissory Note, but with no date or amount to be borrowed

After that, if you do not pay (it doesn't matter how many payments you miss), I fill the amount of money and the date in the Prommisory Note and execute it. The amount and the date do not respect the contractual terms (or rates agreeded).

All this unusual operation was detected because these people can not pay the Promissory Note. Furthermore, some people keep paying according to their payments calendar (those who can do it).

We must figure out:
IRR of the Promissory Note (XIRR)
IRR for investor (XIIR)
Effective annual interest rate of the Promissory Note

Mathematically, XIRR returns a lot of correct values, but the're not "from this world".

Here's another two examples:

oct-19-2013 -20000
dec-10-2013 5391---> This is the first payment (must be in nov-10-2013) --> 3718 + late fee. December payment was missed
dec-16-2013 67782.87 --> Date of Promissory Note whitout notify to debtor
feb-06-2014 6952.66---> Second payment (must be payed in dec-10-2013)
feb-06-2014 3047.34---> Third payment (must be payed in jan-10-2014)

This cash flow (from oct-19-2013 to feb-06-2014), returns this XIRR: 482085%

The Promissory Note it's not payed by debtor yet. It was suspended by authorities. This is why the cash flow shows people can't pay 3718 (in dec-10-2013) but 6 days later, "pays" 67782.87


jan-16-2015 -10000.00
feb-10-2015 1625.00 ----> First payment
mar-12-2015 35650.88 ----> Date of Promissory Note whitout notify to debtor


XIRR 846237%

In this example, debtor can't pay 1625 (on mar-12-2015). So, lender execute the Promissory Note 2 days after. That's why in the cash flow, debtor "pays" 35650.88


How can I do to get XIRR rates consistent with reality?
How can I explain those rates beyond the mathematics algorithm?

Thank you again!

Sorry, but I need more time to rethink the problem. So I retracted my previous response. But unfortunately, I might not find the time to rethink the problem.

Nevertheless, there are some important take-aways.

1. The "unrealistic" XIRR is indeed due to mistakes in your cash flow model.

2. One mistake is: you need to specify a valuation date (on or after the last cash flow), and the outstanding value of the loan as a positive value on that date. This is called the "notional value" of the debt: the amount that the debtor would need to pay if the loan were retired on the valuation date.

3. The other mistake is the treatment of the promissory note. That is the detail that I need to rethink. At the very least:

a. It is wrong to specify the value of the promissory note as a positive value on the date of issuance. And

b. You need to specify the outstanding value of the promissory note (presumably the entire value) as a positive value on the valuation date. Again, that is the "notional value" of the promissory note.

Anything else I might say at this time would be speculative and potentially misleading.

Errata.... I wrote:

Originally Posted by joeu2004

It is wrong to specify the value of the promissory note as a positive value on the date of issuance.

There is a difference in how the term "execution of a promissory note" is used. For some people (including me), "execution" is the date when the terms of the promissory are specified and agreed to (signed). Usually, that precedes the date when payment is demanded.

But for other people (including leguicri?), "execution" is when payment is demanded and, presumably, paid. I would agree that record a positive cash flow on the date(s) of payment.

-----

Originally Posted by leguicri

I give you 20000 and you must pay back this money in 9 payments (3718) each one. The payment is 10th of each month. In that moment (when you receive the money) you must sign a Promissory Note, but with no date or amount to be borrowed[.] After that, if you do not pay (it doesn't matter how many payments you miss), I fill the amount of money and the date in the Prommisory Note and execute it.

Only an idiot would sign a promissory note without knowing its terms, especially the amount.

Originally Posted by leguicri

We must figure out: [....] Effective annual interest rate of the Promissory Note

Some promissory notes do have terms that include interest. Not yours. As I understand your description, the promissory note has no intrinsic interest rate. You simply demand payment of 67782.87 on a specified date.

Originally Posted by leguicri

We must figure out:
IRR of the Promissory Note (XIRR)
IRR for investor (XIIR)

Your terminology is unclear. Do you mean: the IRR of the cash flows with and without the payment of the promissory note?

Suppose you have the following cash flows:

10/19/2013 -20,000.00
12/10/2013 5,391.00
02/06/2014 10,000.00
02/06/2014 17,359.88

The last cash flow is the "notional value" of the loan; that is, the outstanding balance.

Then the IRR is 513.06%: =XIRR(B1:B4,A1:A4).

If you demand payment of the promissory note on 12/16/2013, the cash flows are:

10/19/2013 -20,000.00
12/10/2013 5,391.00
12/16/2013 67,782.87
02/06/2014 10,000.00
02/06/2014 17,359.88

The IRR is 705151.38%: =XIRR(B1:B5,A1:A5).

Is that really so unusual? (Rhetorical.) No, because a significant portion of the total cash flows occurs just about 2 months after the initial loan.

If all the payments occurred 2 months after the loan, the 2-month IRR would be 402.67%: =SUM(B2:B5)/(-B1)-1.

But the annualized IRR would be 1613111.13%: =(1+402.67%)^6-1. That's the power of compounding.

Perhaps you don't want to compound the sub-annual IRR. Not everyone does. In that case, the annualized IRR would be 2416.01%: =6*402.67%.

(I actually use the exact result of SUM(B2:B5)/(-B1)-1 instead of 402.67%.)

-----

I'm afraid that's about all the time I have to look at this question. I hope the above is helpful.

Hello! Thanks for your time joeu2004

Execution (i'm sorry if I used an incorrect word), is the moment (in Argentina), when lender demands the payment of the Promissory Note. Sorry for that translation!

The people who sign those promissory notes, needed the money with urgency and they could'n go to a Bank. They do not pass a credit analisys from a Bank.

The date of valuation, must be the date of last payment

Those questions:

IRR of the Promissory Note (XIRR)
IRR for investor (XIIR)

Your terminology is unclear. Do you mean: the IRR of the cash flows with and without the payment of the promissory note?

Yes, I know, It's not too clear. But the question es that... It was made by lawyers..

The payment of all promissory note is suspended because the authorities detect this unusual situacion and want to know is there is an abusive contract.

First (and most important for us), is to know the profitability (for the lender) of that investment (-20000), from 10/19/2013 (-20000) to 12/16/2013 (prommisory note)
In other words, I give you 20000. You don't pay exactly on 10th each month. So I fill the prommisory note with date (12/16/2013) and amount (67,782.87). We need to know the rate inside that amount, until that date. In this example, we have payments between those dates, that's why we use XIRR.
If we don't have payments between those dates, we would use a discount.

The prommisory note, has no explicit rate. That's true. But we need to know the profitability inside the prommisory note (implicit).

I will try to test all cashflows using notional value.

The other question will be easier if we resolve this first

Thanks for your time again!

Originally Posted by leguicri

Execution (i'm sorry if I used an incorrect word), is the moment (in Argentina), when lender demands the payment of the Promissory Note. Sorry for that translation!

No translation error. No error, at all. My guess: your use of "execution of promissory note" is more common.

Originally Posted by leguicri

But the question es that... It was made by lawyers.. The payment of all promissory note is suspended because the authorities detect this unusual situacion and want to know is there is an abusive contract.

I am no expert in this matter; far from it. In fact, it is apparent that you know about this than I do.

I only claim to know about Excel XIRR and its foibles and the mathematics of IRR computation. If you have doubts about the mathematical calculation of an IRR, I might be able to help.

But I think your questions and doubts are about how to construct the cash flow model for the situation. Your questions are about financial theory and practices, not about Excel. This is an Excel forum, not a forum on financial theory.

Since this is a legal matter, I suggest that you consult experts in financial theory. If not a qualified CPA, then a business professor at a university.

Thank you again joeu2004

This thread started with an Excel question and ends with financial issues...
But all our "conversation" was about the cash flow. Definitely, the problem is the theory behind those especific and unusual cash flow and not XIRR function or some Excel limitation.
I thought maybe there was something wrong with Excel (or bug). Furthermore, Excel only returns one XIRR and not 100 XIRR rates.
But this thread was very useful to me.

I'm CPA and Data Scientist. That's why this situation is in my office right now... I work with two more people and right now, we're usign Excel because everybody knows how to use it. We're almost finishing the analysis

Thank you again for your time and dedication!!

Regards