IRR calculation for single outflow and single payment 20 days later?

[Chris227]

So I work for a hedge fund/private equity administrator. We have a client that provided us with a loan portfolio and asked us to provide a spreadsheet showing the IRR for each deal. Simple enough. But all his loans are paid back, with interest, in a single payment within weeks to months. I really don't know if IRR is relevant here. For instance, there was a loan of $1,100 on 2/1/17 and the loan was paid off on 2/21/17 for $1,287. So this loan produced a $187 profit over 20 days. Using XIRR in Excel results in a return of 1,655%! This can't be right. Am I missing something here? Should I divide by 365 and multiply by 20? That would result in an IRR of 90%! Still seems too high. What am I missing here?

Thanks in advance for any feedback

 

[shg]

I want a client like that ...

	A​	B​	C​	D​	E​
1​	Date​	Amt​	​	​	​
2​	2/1/2017​	-1100​		1655%​	D2: =XIRR(B2:B3, A2:A3)
3​	2/21/2017​	1287​		1655%​	D3: =(-B3/B2) ^ (365/(A3 - A2)) - 1

 

[joeu2004]

You do not need to estimate the IRR with Excel IRR or XIRR if there are only two cash flows. The exact IRR is simply:

=(1287/1100)^(365/(DATE(2017,2,21)-DATE(2017,2,1))) - 1

which is indeed about 1655.46%.

But that presumes compounding daily. That is not always how IRR is defined, especially for terms less than a year.

Alternatively, it can be calculated as follows:

=(365/20) * ((1287/1100) - 1)

But that result is about 310.25%, not 90%. Note that the simple return (1287/1100)-1 is 17.00%.

 

[joeu2004]

=(365/20) * ((1287/1100) - 1)

I should have written:

=(365/(DATE(2017,2,21)-DATE(2017,2,1))) * ((1287/1100) - 1)

 

[Chris227]

Thanks. Yeah, I would think it would be around 17%. There are some loans that pay back in installments, in irregular amounts, and others that are like the example above. So I thought it best to use XIRR so I don't have to manually change anything. For instance, my formula is:

=IF(F397<=0,0,XIRR(OFFSET(F$1,MATCH(TRUE,F$2:F396<>0,0),0):F396,OFFSET(OFFSET(F$1,MATCH(TRUE,F$2:F396<>0,0),0),0,-COUNT($A1:F1)):$A396))

Row 1 shows the number of each deal (1 - 100)
Row 2 - 396 is each day of the year from 1/31/2017 - 2/28/2018

Column A is where the dates are located

So the COUNT in my formula is used to refer back to column A.

The OFFSET/MATCH is used to find the 1st non zero number in each column.

It calculates correctly, except in cases like the example above. So it's a brilliant formula that is basically useless I suppose.

 

[joeu2004]

Yeah, I would think it would be around 17%.

And I misspoke, somewhat. Although it is true that multiplying the simple 20-day return by 365/20 is one way to calculate the annual rate for terms less than a years, notably in the US, common practice is to simply state the simple return for the term, properly qualified. In other words, a "20-day rate of 17%".

There are some loans that pay back in installments, in irregular amounts, and others that are like the example above. So I thought it best to use XIRR

For a less-than-year loan (e.g. 60 days), you might specify a 60-day rate of:

=60 * ((1+XIRR(...))^(1/365) - 1)

my formula is:
=IF(F397<=0,0,XIRR(OFFSET(F$1,MATCH(TRUE,F$2:F396<>0,0),0):F396,OFFSET(OFFSET(F$1,MATCH(TRUE,F$2:F396<>0,0),0),0,-COUNT($A1:F1)):$A396))

I won't try to digest that. But a general comment: it is better, when reasonable, to avoid the use of OFFSET(). Since it is a volatile formula, it is recalculated every time any cell in the workbook is modified. A few such formulas should be okay. But if you have 100s or 1000s, that can be time-consuming, especially with MATCH(...,0). It might be better to use INDEX().