A Math Puzzle - IRR


merjet

Recommended Posts

Internal rate of return or IRR is probably the most used math formula in finance/investing. Spreadsheets (like Microsoft Excel, Google, LibreOffice) offer IRR and the similar XIRR as built-in functions. These functions seem to give an anomalous result for the short sale of a stock. Assume an investor does a short sale. The investor borrows a stock, immediately sells it, and later buys it back (and pays any interest) when the price is lower as the investor hoped. Assume the following specific numbers, where XIRR and IRR show the results from a spreadsheet.       

Date           Cash 
08/04/17    100
08/04/18    -95
  XIRR:         -5.00%
   IRR:          -5.00%

XIRR uses the dates and cash as inputs. IRR uses only the cash inputs and assumes they are one period (e.g. one year) apart.

Does the result, -5.00%, make sense?  The investor's dollar return of $5 (=$100 - $95) is positive. So why do XIRR and IRR show negative results? 

Link to comment
Share on other sites

3 hours ago, merjet said:

Internal rate of return or IRR is probably the most used math formula in finance/investing. Spreadsheets (like Microsoft Excel, Google, LibreOffice) offer IRR and the similar XIRR as built-in functions. These functions seem to give an anomalous result for the short sale of a stock. Assume an investor does a short sale. The investor borrows a stock, immediately sells it, and later buys it back (and pays any interest) when the price is lower as the investor hoped. Assume the following specific numbers, where XIRR and IRR show the results from a spreadsheet.       

Date           Cash 
08/04/17    100
08/04/18    -95
  XIRR:         -5.00%
   IRR:          -5.00%

XIRR uses the dates and cash as inputs. IRR uses only the cash inputs and assumes they are one period (e.g. one year) apart.

Does the result, -5.00%, make sense?  The investor's dollar return of $5 (=$100 - $95) is positive. So why do XIRR and IRR show negative results? 

I would have to see the algorithm they are using  before I expressed an opinion.  I have no idea  what -5.00 % could possibly mean.

Link to comment
Share on other sites

29 minutes ago, BaalChatzaf said:

I would have to see the algorithm they are using  before I expressed an opinion.  I have no idea  what -5.00 % could possibly mean.

See the Example section in the Wikipedia page I linked. Akin to it, r = -0.05 = -5.00%  is the solution to the equation:

NPV = 100 - 95/(1+r) = 0.

 

Link to comment
Share on other sites

This isn't a proper application of IRR.  You may as well use IRR to calculate the return you earn when you're paid a salary.  There is no investment going in. 

Shorting a stock means taking on a risk, but you don't have a cash outlay (beyond a nominal transaction cost). 

Link to comment
Share on other sites

12 minutes ago, 9thdoctor said:

This isn't a proper application of IRR.  You may as well use IRR to calculate the return you earn when you're paid a salary.  There is no investment going in. 

Shorting a stock means taking on a risk, but you don't have a cash outlay (beyond a nominal transaction cost). 

I don't agree but won't explain why for now. My explaining it would answer the puzzle. Also, there is a cash outlay when the stock is bought to close the transaction (pay back the loan). 

Link to comment
Share on other sites

35 minutes ago, merjet said:

I don't agree but won't explain why for now. My explaining it would answer the puzzle. Also, there is a cash outlay when the stock is bought to close the transaction (pay back the loan). 

Did you have custody of the $100 starting day one?  Did you actually pay the $95 at the end, or did you call your broker to have the position closed out?  After which you're account balance was $5 higher.

Link to comment
Share on other sites

21 minutes ago, 9thdoctor said:

Did you have custody of the $100 starting day one?  Did you actually pay the $95 at the end, or did you call your broker to have the position closed out?  After which you're account balance was $5 higher.

I created an example. But if I were to do such a short sale,  $100 cash would be credited to my brokerage account upon selling the stock, and $95 cash would be debited to my brokerage account upon buying the stock to close the transaction (pay back the loan).

Link to comment
Share on other sites

1 hour ago, merjet said:

See the Example section in the Wikipedia page I linked. Akin to it, r = -0.05 = -5.00%  is the solution to the equation:

NPV = 100 - 95/(1+r) = 0.

 

-95/(1 +r) = -100

1/1+r = 100/95

therefore  1+ r = 95/100

so r = -0.05  is correct

I am not sure if the formula  makes sense.

Link to comment
Share on other sites

I offer two explanations why I believe the -5% XIRR/IRR for the short sale makes sense.

#1. Suppose an investor buys a stock at $100 and later sells it for $95. Then the spreadsheet looks like this:

Date             Cash
08/04/17     -100
08/04/18        95
    XIRR:      -5.00%
    IRR:         -5.00%

That makes sense, doesn’t it? The investor lost 5%. Also, in this case the equation solved is -100 + 95/(1+r) = 0. r = -0.05. Multiply the equation by -1, and it becomes the equation for the short sale, 100 - 95/(1+r) = 0. Multiplying an equation by -1 does not change the solution, so it logically follows that the solution for the short sale is also -5%.

#2. Suppose somebody borrows $100 and pays back the $100 plus $5 interest one year later. Then the spreadsheet looks like this:

Date              Cash
08/04/17      100
08/04/18     -105
    XIRR:         5.00%
    IRR:           5.00%

The borrower pays +5% interest. Note that the cash amounts for the lender are -100 and +105, and XIRR and IRR would likewise be 5.00%.

Suppose instead the borrower partly defaults and pays back only $95. Then the cash amounts would be identical to the short sale:

Date            Cash
08/04/17     100
08/04/18      -95
  XIRR:        -5.00%
  IRR:          -5.00%

Regard this as the borrower “pays -5% interest,” which translates to “receives +5% interest.” “Receives +5% interest” fits the short seller, too. This short seller borrows stock and receives "interest" rather than paying "interest." Two different situations, inputs are identical, so the results (IRR and XIRR) are identical. IRR’s and XIRR’s handling of the two different situations is wholly consistent.  A third situation, numerically the same, would be a bank paying a negative interest rate on deposits. In math consistency counts a lot.

Link to comment
Share on other sites

Here is another IRR puzzle. The problem for XIRR and IRR and Excel’s (also Google Drive’s) answers are:

Date         Cash

08/04/17      +100
08/04/18       -190

08/04/19      +100   
   XIRR:      #NUM    
    IRR:       #NUM

Why #NUM? Excel tries to solve 100 – 190/(1+r) + 100/(1+r)2 = 0, but can't. Substituting X for (1+r)-1, Excel in effect tries to solve 100*X^2 – 190*X + 100 = 0. The general solution for this quadratic equation includes the square root of 190^2 – 4*100*100. Since the result is less than 0, X is an imaginary number, which makes no sense in finance.

Regardless, is there some real rate of return which makes sense? Such a set of cash flows is likely rare in finance, but not impossible. The investor has a net positive cash flow, $200 – $190 = $10, so it seems there should be some reasonable positive return. But what is it? Hint: divide and conquer and revisit the prior puzzle.

Link to comment
Share on other sites

Nobody has replied. Oh, well. This is mainly a philosophy and politics forum. Anyway, my divide and conquer approach follows.

Make it two problems and I include their solutions:

Date         Cash             Date          Cash

07/24/17   100

07/24/18    -95         07/24/18      -95

                                    07/24/19     100

    XIRR: -5.00%             XIRR: 5.26%

      IRR: -5.00%                IRR: 5.26%

Taking the –5.00% as “really” +5.00%, the solutions for the two don’t quite match. However, a little tweaking on the negative cash amounts makes them match:

Date            Cash             Date              Cash

07/24/17    100

07/24/18    -94.875       07/24/18    -95.125

                                         07/24/19       100

     XIRR: -5.125%              XIRR: 5.125%

       IRR: -5.125%                 IRR: 5.125%

Another approach is treat the first +100 as being placed in an escrow account and combine it with the -190. In other words:

Date                Cash

07/24/18          -90

07/24/19         100

XIRR:        11.1111%

IRR:          11.1111%

The 11.1111% is more than twice the 5.125%. However, a two-year problem was squeezed to a one-year problem. Unsqueeze it: 1.111111^0.5 – 1 = .0541 or 5.41%.

That gives two answers for the real return that appear reasonable and are in the same neighborhood.

Link to comment
Share on other sites

3 hours ago, merjet said:

Nobody has replied. Oh, well. This is mainly a philosophy and politics forum. Anyway, my divide and conquer approach follows.

Make it two problems and I include their solutions:

Date         Cash             Date          Cash

07/24/17   100

07/24/18    -95         07/24/18      -95

                                    07/24/19     100

    XIRR: -5.00%             XIRR: 5.26%

      IRR: -5.00%                IRR: 5.26%

Taking the –5.00% as “really” +5.00%, the solutions for the two don’t quite match. However, a little tweaking on the negative cash amounts makes them match:

Date            Cash             Date              Cash

07/24/17    100

07/24/18    -94.875       07/24/18    -95.125

                                         07/24/19       100

     XIRR: -5.125%              XIRR: 5.125%

       IRR: -5.125%                 IRR: 5.125%

Another approach is treat the first +100 as being placed in an escrow account and combine it with the -190. In other words:

Date                Cash

07/24/18          -90

07/24/19         100

XIRR:        11.1111%

IRR:          11.1111%

The 11.1111% is more than twice the 5.125%. However, a two-year problem was squeezed to a one-year problem. Unsqueeze it: 1.111111^0.5 – 1 = .0541 or 5.41%.

That gives two answers for the real return that appear reasonable and are in the same neighborhood.

The algebra is trivial.  I simply do not quite grasp there terminology and that which it refers to. 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now