Alaric wrote:It's zeroes of polynomials.
Find the value of x such that
A(0) + A(1) * x + A(2) * x^2 + A(3) * x^3 + ...... A(n) * x^n = 0
x is 1/ ( 1+ rate of return)
Yes, though in a "how has this portfolio done?" context I would normally find it easier to think about y = 1/x = 1 + rate of return. Substituting that into the formula and multiplying through by y^n gives:
A(0)*y^n + A(1)*y^(n-1) + A(2)*y^(n-2) + ... + A(n-1)*y + A(n) = 0
That has a very natural interpretation: in the equivalent bank account, at the end of the period, the sum of money represented by A(0) has had the interest applied to it for all n years (or whatever other interval one is working with), and has been multiplied by y in each of them, so overall by y^n, and so its contribution to the final balance is A(0)*y^n. Similarly, the sum of money represented by A(1) has had interest applied to it for n-1 of the n years, so contributes A(1)*y^(n-1) to the final balance, etc, up to the sum of money represented by A(n) having not had interest applied to it at all, so just contributing A(n). So the polynomial is just a calculation of the final account balance, so its zeros are just saying we're interested in the rates of return that produce a final balance of zero.
That's viewing it from a "past performance" point of view, i.e. looking at the case of being interested in what rate of return ends up with a zero balance (= the final portfolio value before adding the 'fake liquidation' cashflow at the end). Alaric's polynomial similarly looks at it from a "net present value" point of view, at the start of the period, saying that the net present value of A(0) is just A(0), the net present value of A(1) in one year's time is A(1)*x, etc, and so the polynomial is the total net present value and its zeros indicate the discount rates that produce zero net present value. In that context, x = 1/(1+discount rate), which is probably a somewhat more recognisable quantity than 1/(1+rate of return).
The two ways of looking at it are of course completely equivalent mathematically - that's proved by the substitution y = 1/x. But depending on context, one or the other of them will probably be more natural.
Alaric wrote:Be aware that if the values of A(i) change sign, there can be more than one solution for x.
Also be aware that in practice the values of A(i) almost always
do change signs - the only practical case where they don't once you've remembered to include the 'fake liquidation' cashflow at the end is if you've only ever put money into a portfolio and everything in it has become worthless. And generally that only applies in practice when the 'portfolio' you're measuring is a single investment (which can be thought of as a portfolio containing only one holding) that has gone bust, or when one buys a badly-undiversified portfolio of particularly risky investments and have been unlucky enough to have them all go bust.
And in practice, there very often are multiple mathematical solutions: generally the issue is one of what realistic solutions there are in practice. For histories of well-behaved portfolios, there's usually only one: all the other mathematical solutions for y (or x) are complex numbers or nowhere near being a realistic rate of return (or discount rate). For instance, even as simple an investment history as my earlier example of £100 invested, a further £100 invested after a year and a final value of £231 (so cashflows of +£100, +£100, -£231 at yearly intervals) corresponds to the quadratic equation 100y^2 + 100y - 231 = 0. Solving that equation exactly by the standard formula says that its roots are y = 1.1 and y = -2.1, which correspond to rates of return of 10% and -310%. And if you work through the mathematical calculations, at -310% interest, £100 earns -£310 of interest in the first year, resulting in a balance of -£210. Adding another £100 at that point alters the balance to -£110, which then earns interest at -310% of £341 in the second year, altering the balance to £231. So an interest rate of -310% works out perfectly mathematically as producing a final £231 - but is of course totally ludicrous as a result for an investment rate of return (*) or a discount rate! So only the 10% rate of return makes any sense.
But it is possible for a portfolio history to produce multiple not-totally-unreasonable solutions. For instance, imagine a single-share 'portfolio' that you invest £1,000 into. A year later, you review it: it has grown massively and you decide that it's simply too big a holding to risk, so you sell half of it, realising £3,200. In the next year, it plummets and when you review it at the end of year 2, you decide that the share now looks undervalued, indeed a really good prospect,
and your holding now looks very undervalued, so you invest all that you can afford in it, which is £3,390. Unfortunately, it plummets some more during year 3, with the result being that your holding ends up as being worth £1,188 at the year 3 review. What's your rate of return so far?
So the polynomial equation to be solved is the cubic 1000y^3 - 3200y^2 + 3390y - 1188 = 0. Cubic equations can be solved exactly mathematically, but the solution is messy and unmemorable... However, I have contrived this example so that its three exact solutions are y = 0.9, y = 1.1 and y = 1.2, corresponding to rates of return of -10%, +10% and +20%. So which of those three is the rate of return you've experienced so far???
Feeding XIRR() with the corresponding sequence of cash flows +£1,000, -£3,200, +£3,390, -£1,188 at yearly intervals (making each gap exactly 365 days - take care about leap years!) produces a result of -10.00% (**). But actually, given that one has put £1,000+£3,390 = £4,390 into the portfolio and have had £3,200 out of it and could take a further £1,188 out, making £4,388 in total, the practical reality is that one is £2 down on the portfolio history. So the practical answer is "just below but very close to a rate of return of 0.00%", and so a good distance away from
any of the mathematically exact answers!
That sort of thing tends to be associated with portfolio histories that contain wild price swings like the huge rise and plummets in that example. Portfolios such as HYPs that behave fairly tamely will tend to have only one realistic rate of return, which can be calculated pretty reliably by XIRR(). On the other hand, a portfolio of tech shares run from March 1999 to March 2002 might easily have behaved like that example, with a huge gain in the first year and serious plummets in the second and third. Whether an investor caught up in the tech boom would have decided to sell half their holdings in March 2000 is of course very open to question, but no doubt
some did...
So basically, XIRR() has distinct limitations if used to evaluate the performance of portfolios that have behaved too wildly in the past.
(*) Rates of return below -100% (corresponding to negative values of y) only occur for unlimited-liability investments and geared investments. Anyone who takes such investments on had better know what they're doing far better than to need to pay attention to this post!
I will also note that when such investments are involved in a portfolio, it also becomes possible for the final value of the portfolio to be negative. This basically means that both A(0) and A(n) can be positive, whereas normally A(0) is positive for the initial deposit cashflow and A(n) is negative for the 'fake liquidation' cashflow. This makes it possible for
all of the mathematical solutions of the polynomial to be complex or negative, so that none of them corresponds to a realistic rate of return.
(**) Which is actually a bit odd, as the algorithm that XIRR() uses is an iterative one that starts with a guess and tries to improve it, and then similarly to improve the improved guess, and so on until either very little improvement is happening (producing an answer) or it's repeated the process a large number of times and the improvements haven't become small (producing an error). The oddity is that the default guess is 0.1 = +10%, so at the first step it must be deciding incorrectly that an already-perfect guess requires a significant 'improvement'! I do have a decent idea how one might go about investigating how this happens, but that would require quite a bit of technical work using the mathematical subject of the numerical analysis of algorithms. I don't think I'm going to bother - and even if I were to do so, this would not be an appropriate place to write up the results!
Gengulphus