I'm trying to set up an excel spreadsheet that solves for $Y$ in the classic bond formula:
$P = \frac{C}{(1+Y)}+\frac{C}{(1+Y)^2}+\frac{C}{(1+Y)^3}+_{......}+\frac{(C + Q)}{(1+Y)^n}$
Where "C" is a constant cash flow, "P" is the present value, and "Q" is a constant par value. "Y" represents the bond yield.
$n$ will be given, but it will be different each time I input the formula.
I can't solve the algebra. I don't want to use the Solver function, because the spreadsheet needs to be flexible. I need to find an equation that either solves for "Y," or estimates it relatively closely.
Can you help?
$Y = _{....}$?
For an approximation, let $x=\frac 1 {1+Y}$ making the equation to be $$P=C\frac{ x \left(x^n-1\right)}{x-1}+Q x^n$$Now, expand the rhs as a Taylor series built at $x=1$ to get $$P=(C n+Q)+\frac{1}{2} n (x-1) (C (n+1)+2 Q)+\frac{1}{6} (n-1) n (C (n+1)+3 Q)(x-1)^2 +\frac{1}{24} (n-2) (n-1) n (C (n+1)+4 Q)(x-1)^3+O\left((x-1)^4\right)$$ Now, use series reversion to get things like $$x=1+z-\frac{ (n-1) (C (n+1)+3 Q)}{3 (C (n+1)+2 Q)}z^2+O\left(z^3\right)$$ where $$z=\frac{2 (P-Q-C n)}{n (C( n+1)+2 Q)}$$
Let us try with $P=1500$, $C=100$, $Q=1000$ and $n=20$. This would give $$z=-\frac{3}{82}\implies x=\frac{262691}{275684}\implies Y=\frac{12993}{262691}\approx 0.049$$
Now, use Newton method using this value as $Y_0$. The iterates would be $$\left( \begin{array}{cc} n & Y_n \\ 0 & 0.049461154 \\ 1 & 0.056876669 \\ 2 & 0.057341522 \\ 3 & 0.057343198 \end{array} \right)$$
You could even do better, considering the equation to be $$P=\sum_{k=1}^{n-1}\frac C {(1+Y)^k}+\frac{C+Q}{(1+Y)^n}$$ Build for the rhs the simplest Padé approximant at $Y=0$ which will be $$P=\sum_{k=1}^{n-1}\frac C {(1+Y)^k}+\frac{C+Q}{(1+Y)^n}\simeq\frac{a_0-a_1 Y }{1+b Y }$$ where $$\color{blue}{a_0=Cn+Q \qquad a_1=\frac{(n-1) \left(C^2 n (n+1)+4 C (n+1) Q+6 Q^2\right)}{6 (C (n+1)+2 Q)}}$$ $$\color{blue}{ b= \frac{(n+1) (C (n+2)+3 Q)}{3 (C (n+1)+2 Q)}}$$ and solve the resulting equation for $Y$. This would give $$\color{red}{Y=\frac{a_0-P}{a_1+b P}}$$ Applied to the worked example, this would give $Y=\frac{123}{2270}\approx 0.054185$. Using this starting value, Newton iterates would be $$\left( \begin{array}{cc} n & Y_n \\ 0 & 0.054185022 \\ 1 & 0.057267092 \\ 2 & 0.057343153 \\ 3 & 0.057343198 \end{array} \right)$$ As you can see, one single iteration is almost sufficient.