Solve for "R" in classic annuity formula

Question

$$p=\frac{c}r*(1-\frac1{(r+1)^t} )$$

I'm stuck. I'm building an excel model where I will be able to put in inputs for p, c, and t... but it will need to solve for "r."

For some reason, I can't figure out the algebra.

Can you help me solve for "r"?

Answer 1

As Hello_World answered, the simplest way would be to use Newton method for finding the zero of function $$f(r) = \frac{c}r \left(1-\frac1{(r+1)^t}\right)-p$$

If you start using $r_0=0$, the first iterate would be $r_1=\frac{2 (c t-p)}{ct \left(t+1\right)}$ and you just need to continue until convergence to the desired precision.

The is another thing you can do since $r \ll 1$. Build the Taylor series at $r=0$ to get $$f(r)+p=c t-\frac{1}{2} r (c t (t+1))+\frac{1}{6} c r^2 t (t+1) (t+2)-\frac{1}{24} r^3 (c t (t+1) (t+2) (t+3))+\frac{1}{120} c r^4 t (t+1) (t+2) (t+3) (t+4)-\frac{1}{720} r^5 (c t (t+1) (t+2) (t+3) (t+4) (t+5))+O\left(r^6\right)$$ and now use series reversion to get $$\color{blue}{r=x+\frac{t+2}{3} x^2+\frac{5 t^2+17 t+14}{36} x^3+\frac{17 t^3+78 t^2+117 t+58}{270} x^4+\frac{193 t^4+1094 t^3+2301 t^2+2144 t+748 }{6480}x^5+O\left(x^{6}\right)}$$ where $\color{blue}{x=\frac{2 (c t-p)}{ct \left(t+1\right)}}$ (this is $r_1$)

Let us try using $p=100000$, $c=1000$ and $t=120$. This gives $x=\frac 1 {363}$. Using the expansion above, this leads to $r=\frac{31795409217001}{10210526568429660}\approx 0.00311398$ while the "exact" solution obtained using Newton method would be obtained after the following iterates $$\left( \begin{array}{cc} n & r_n \\ 0 & 0.000000000000 \\ 1 & 0.002754820937 \\ 2 & 0.003161445197 \\ 3 & 0.003114792911 \\ 4 & 0.003114182051 \\ 5 & 0.003114181946 \end{array} \right)$$

As you can see, without any iterative procedure we can get a very close solution of the problem (for the working case, the relative error is about $0.0065$% !).

Adding more terms will give a better result (if you want them, just ask).

Edit

Working a bit more, the blue formula may be written in a more compact form (for an even better accuracy) using a Padé approximant $$r=x \,\frac{1+a_1x+a_2x^2}{1+b_1x+b_2x^2}$$ where $$a_1=-\frac{2 \left(2 t^2+3 t+1\right)}{11 t+13}\qquad a_2=\frac{4 t^3-3 t-1}{45 (11 t+13)}$$ $$b_1=-\frac{23 t^2+53 t+32}{3 (11 t+13)}\qquad b_2=\frac{67 t^3+240 t^2+291 t+122}{60 (11 t+13)}$$ For the work example, this would give $r=\frac{13916770468}{4468838772519}\approx 0.0031141805$

Update

Asking a banker friend of mine, he mentioned an approximation he saw somewhere in the past (he does not remember when and/or where. I just found where). I is $$\color{green}{r\simeq \left(\left(1+\frac{c}{p}\right)^{\frac{1}{q}}-1\right)^q-1}\qquad \text{where} \qquad \color{green}{q=\log_2\left(1+\frac 1t \right)}$$ Applied to the worked example, this gives $0.00310743$.

Using this estimate as $r_0$, Newton iterates would be $$\left( \begin{array}{cc} n & r_n \\ 0 & 0.003107429977 \\ 1 & 0.003114180156 \\ 2 & 0.003114181946 \end{array} \right)$$ which is real fast. I suppose that one single iteration of Newton method will be more than sufficient.

You could use as a very safe solution $$r=r_0+\frac{2\,f(r_0) \,f'(r_0)}{f(r_0)\, f''(r_0)-2\, f'(r_0)^2}$$ where $r_0$ is the result of the green formula.

Applied to the worked example, this would lead to $r=0.00311418194589$ while th exact solution would be $r=0.00311418194600$

Answer 2

You could try Newton's method to solve for $r.$ Take, $$F(r) = p-\frac{c}r\cdot \left(1-\frac1{(r+1)^t}\right)$$ then your goal is to find $r$ such that $F(r)=0.$ For this you start with some choice $r_0$ and then use the following recursive definition: $$r_{n+1}= r_n - \frac{F(r_n)}{F'(r_n)}.$$ This will converge to the root of $F.$

Answer 3

I prefer to add a second answer.

Being just fascinated by David W. Cantrell's approximation $$\color{green}{r\simeq \left(\left(1+\frac{c}{p}\right)^{\frac{1}{q}}-1\right)^q-1}\qquad \text{where} \qquad \color{green}{q=\log_2\left(1+\frac 1t \right)}$$ totally inspired by it, I tried something in the same spirit $$\frac{c}r \left(1-\frac1{(r+1)^t}\right)=p\implies 1+\frac{c}{p}=1+\frac{r}{1-(r+1)^{-t}}$$ Taking logarithms of both sides $$\log \left(1+\frac{c}{p}\right)=\log \left(1+\frac{r}{1-(r+1)^{-t}}\right)$$ Now, expanding the rhs as a Taylor series at $r=0$ $$\log \left(1+\frac{c}{p}\right)=\log \left(1+\frac{1}{t}\right)+\frac{r}{2}+\frac{2t-5}{24} r^2 +O\left(r^3\right)$$ Neglecting the second order term, we the obtain the first approximation $$\color{blue}{r_1 =2 \log \left(\frac{t \,(c+p)}{p\, (t+1)}\right)}$$ Using the complete expansion to $O\left(r^3\right)$, we then have the second approximation $$\color{blue}{r_2=\frac{\sqrt{1+4\, \alpha\, r_1}-1}{2 \alpha }}\qquad \text{where}\qquad \color{blue}{\alpha=\frac {2t-5}{12}}$$

We could even avoid quadratic equations building the simplest Padé approximant instead of the Taylor series. This gives $$\log \left(1+\frac{c}{p}\right)=\frac{\log \left(1+\frac{1}{t}\right)+\frac{1}{12} \left((5-2 t) \log \left(1+\frac{1}{t}\right)+6\right) r} {1+ \frac{5-2t}{12} r }\implies \color{blue}{r_3=\frac{12\,r_1}{12+(2t-5)\,r_1}}$$

Applied to the worked example, this would give $$r_1=2 \log \left(\frac{606}{605}\right)\approx 0.00330306$$ $$r_2=\frac{2}{235} \left(\sqrt{9+1410 \log \left(\frac{606}{605}\right)}-3\right)\approx 0.00311325$$ $$r_3=\frac{12 \log \left(\frac{606}{605}\right)}{6+235 \log \left(\frac{606}{605}\right)}\approx 0.00310238$$ while the exact solution is $$r=0.00311418$$