merjet Posted August 4, 2017 Share Posted August 4, 2017 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 More sharing options...
BaalChatzaf Posted August 4, 2017 Share Posted August 4, 2017 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 More sharing options...
merjet Posted August 4, 2017 Author Share Posted August 4, 2017 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 More sharing options...
9thdoctor Posted August 4, 2017 Share Posted August 4, 2017 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 More sharing options...
merjet Posted August 4, 2017 Author Share Posted August 4, 2017 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 More sharing options...
9thdoctor Posted August 4, 2017 Share Posted August 4, 2017 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 More sharing options...
merjet Posted August 4, 2017 Author Share Posted August 4, 2017 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 More sharing options...
BaalChatzaf Posted August 4, 2017 Share Posted August 4, 2017 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 More sharing options...
merjet Posted August 5, 2017 Author Share Posted August 5, 2017 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 More sharing options...
merjet Posted August 7, 2017 Author Share Posted August 7, 2017 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 -19008/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 More sharing options...
merjet Posted August 9, 2017 Author Share Posted August 9, 2017 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 More sharing options...
BaalChatzaf Posted August 9, 2017 Share Posted August 9, 2017 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now