Transform $\sum_i c_i (B_i + \alpha)^{t_i}$ to $\sum_i \beta_i(1 + r)^{t_i}$ to compute Excess IRR

Question

Is it possible to transform something of the form

$$\sum_i c_i \bigl((1+b_i)^{1/t_i} + \alpha\bigr)^{t_i} = \sum_i c_i (B_i + \alpha)^{t_i}$$

into the form

$$\sum_i \beta_i(1 + r)^{t_i}$$

where $r$ is not dependent on $i$ and $b_i$ is independent of $\alpha$?

---

My motivation is that I'd like to know if it's possible to calculate an Excess IRR given that I have a function that solves a standard IRR.

Many languages have built-in functions like Excel's XIRR, but don't have something readily available for an excess IRR. Is it possible to somehow use the XIRR function to compute an excess IRR instead of having to resort to an iterative root finding approach?

Answer 1

I didn't figure out how to prove it rigurously but I have a strong suspicion it isn't possible. I tried to find the solution for the simplest case, only one period with an investiment at the beginning and a single payoff at the end. In this case the problem would be

Find $\alpha$ such that $$ -I+ \frac{P}{1+b_{1}+\alpha} = 0 $$ where $I$=investment, $P$=payoff, $b_{1}$=index return, and $\alpha$=excess return.

There's a simple solution by setting $r = b_{1} + \alpha$, but this is not valid because $r$ is dependent on $b_{1}$. I can't see a way to transform it as you want.

You can simplify the problem by using continuous compounding because the problem is reduced to

Find $\alpha$ such that $$ -I+ Pe^{-(b_{1}+\alpha)} = -I+ Pe^{-b_{1}}e^{-\alpha} = 0 $$ There you have it factorized as you wanted, the only problem is XIRR doesn't calculte IRR's with continuous compounding, but you can use the equivalent rate of return $(b_{i}^{'} = e^{b_{i}}-1 \enspace \forall i)$ using the cashflows $P_{i}e^{-b_{i}} \enspace \forall i$.

This won't give you the exact result as the original problem, because the original problem is the degenerate version of the problem

Find $\alpha$ such that $$ -I+ P\left(\frac{1}{1+b_{1}}\right)\left(\frac{1}{1+\alpha}\right) = 0 $$ $$ -I+ P\left(\frac{1}{1+b_{1}+\alpha+\color{red}{b_{1}\alpha}}\right) = 0 $$ which is the equivalent problem of the continuous case. The good news is that if the numbers are small, the cross term will be even smaller and it would serve as a pretty good approximation. If you want to be safe better use an iterative root finding algorithm.

Answer 2

The answer is simple. You cannot use the EXCEL's built in XIRR to find the EXCESS IRR. The good news is I have created an EXCEL spreadsheet with a macro to find the EXCESS IRR. It uses SECANT METHOD TO FIND THE alpha using the cashflow dates, cashflows and Index perfomance and finds the the alpha. It is a standalone EXCEL sheet but you could make it as an add-inn if you want. The image of this is shown in the image below. If you could give the email address, I shall send it to you for your use and your company use. The validation is done using EXCEL's goal seek.