Solve internal rate of return but with a simple interest rate in Excel 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!
 A: 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!
A: 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.
