Solve internal rate of return but with a simple interest rate in Excel

Question

How to solve $\sum_{i=1}^{n} \frac{P_i}{1+(d_i-d_1)x/365} = 0$ in spreadsheet?

We have already known that in Excel,

XIRR() find the root of the equation: $\sum_{i=1}^{n} \frac{P_i}{(1+x)^{(d_i-d_1)/365}} = 0$, which is the IRR (Internal Rate of Return) of a series of compounding cash flow.

However, what if the interest rate is flat rather than compounded? that is, solve $\sum_{i=1}^{n} \frac{P_i}{1+(d_i-d_1)x/365} = 0$

I have used approximations like

$1/(1+\tau x)=1-\tau x$

or

$1/(1+\tau x)=(1+x)^{\tau}$

where $\tau=(d_i-d_1)/365$

But I am not sure the error is negligible.

I hope that I could have a nice numerical result in excel.

Thanks!

Answer 1

You want to find the zero of function $$g(x)=\sum_{i=1}^{n} \frac{P_i}{1+\tau_i\, x}$$

Expanding $g(x)$ as a series built around $x=0$ and truncating to any order $O(x^{p+1})$ gives a polynomial of degree $p$ in $x$ with very nasty coefficients (then probably losses of accuracy).

It could be better to use Newton method using $$x_{n+1}=x_n- \frac{g(x)}{g'(x)} \qquad \text{where} \qquad g'(x)=-\sum_{i=1}^{n} \frac{P_i\, \tau_i }{(1+\tau_i\, x)^2}$$

The problem is to find a good $x_0$ to start this simple process. The simplest one is given by the first iteration of Newton method $$x_0=\frac{\sum_{i=1}^{n} P_i}{\sum_{i=1}^{n}P_i\, \tau_i}$$

This is very simple to do. You will not have any loss of accuracy, it will converge very fast and you can control easily the convergence of the process using $|x_{n+1}-x_n| \leq \epsilon$.

Try with any of your test cases and, please, let me know.

Answer 2

I am the author of this post. Turns out I successfully with Taylor series and polynomial solver in spreadsheet:

Apply $ \frac{1}{1+t} = 1-t+t^2-t^3...$ into $g(x)=\sum_{i=1}^{n} \frac{P_i}{1+\tau x}$,

$g(x) = P_i (\sum {\tau_i} - \sum {\tau_i^2} x + \sum {\tau_i^3} x^2 ...) $

I write g(x) into a degree 5 poloynomial and solve it by the polynomial solver.

I validate it in n=2 case and the error is small.

I am curious if there are any better solutions.

Thank you all!