Using goal seek in excel.

Question

Hi I don't know if this is an appropriate question for this page but I thought it was worth a shot and if nothing else a learning experience.

So I am studying actuarial mathematics and I am currently working on problem involving calculating the yield of an investment.

I have been working through these questions alongside prerecorded lectures and have been fairly understanding the concept and how to use it however my lecturer constantly says that these answers are not that exact or precise but would be a lot better when worked out using Goal seek in excel.

However my problem is that he has never shown us goal seek or mentioned any further on the topic.

Hence I was wondering if anyone could explain this too me and/or how to use it as I'm getting answers to questions wrong due to the situation.

Thanks in advance.

Adding from comments:

The question I was given was: You invest £10,000 in a project and receive back £700.87 per annum payable half-yearly in arrears for the next 25 years. Calculate the yield on this investment.

So from this we worked out that the money coming back at the end of this question is £17521.75 and the average time for this question is 12.75 $(last time period-first time period) /2$ which is $(25.5)/(2)$

And from this we do the calculation

$(17521.75/10000)^(1/12.75)$

And we get 4.5% which my lecturer then states in his lecture that this isn't a very good answer as the real answer is 4.98% but doesn't proceed to tell us how to obtain this answer.

Answer 1

After $n$ years at an interest rate of $p\,\%$, an initial amount $A$ becomes $A\left(1+\frac p{100}\right)^n$, of course. Can we find the interest rate that would allow us to double our amount in $n$ years? Still easy: Equate the above with $2A$ and transform in straightforward fashion to arrive at $p=100(\sqrt[n]2-1)$.

But things get complicated when we have more complex situations, e.g., annual payments, perhaps even of varying size. We can still easily compute forward to find the final amount in such a scenario. But the backwards computation, e.g., to find the interest rate or the annual payment that produces a desired final amount, is way more complicated and typically it is not possible to compute it exactly in closed form.

Enter the goal seeker of any modern spreadsheet software. What it does is the following: It repeatedly varies the value of a cell of your choice, computes everything else "forward", and compares another cell with the desired goal value. Once the difference from the goal value is small enough (hopefully about the order of magnitude of rounding errors), it stops. Of course, they do not try random values until they make a lucky discovery. Instead they use some numerical methods that usually converge nicely to a solution (at least if one starts with an initial guess that is not too far off). For example, if setting the input cell to $6$ produces $2.5$ in the output cell, and $7$ produces $-0.8$, then (at least for a continuous function dependency) we expect a solution between $6$ and $7$ and might do our next trial computation with $6.5$. Actually, the values suggest that the solution might be closer to $7$ than to $6$, so perhaps our next try should rather be $6,75$. If computing with $6.75$ produces output $0.04$, we now know that we should search only between $6.75$ and $7$ and perhaps try $6.77$ next, and so on.

This simplified description leaves aside a lot of problems that are in the fine details. And as a user, you should be aware of possible problems as well.

• no good solution might be found (typically detectable by a still large-ish difference from the goal value)
• in spite of a small difference from the goal value, the nearest solution is quite far away
• or it might even be very far away because the algorithm git "trapped" near some almost-solution
• if there are several solutions, you find only one
• ...

Nevertheless, goal seek can very often be a quick and easy helper in solving such problems with good-enough accuracy.

Answer 2

Not an answer, but far too much for a comment.

Computing in gold standard with no interest rates, the question is easy:

You invest 10k£ and earn £700.87 for the forthcoming 25 years. So you have the investment and the yield (easy to the percentage) and independent if the payments are per year or per half year.

It gets more complicated if interests are of any concern. Money you have access to now is worth more than a promise of money some-when., even when the risk or default is not to be considered.

Please amend your question.

Answer 3

As at least one of the comments suggested, it is reasonable to understand the math.

The following approach will require a calculator that can (for example) readily compute $v^{(50)}$, for $0 < v < 1.$

The easiest way to determine the annual interest rate $i$ is to first solve for the semi annual interest rate $j$.

Then you will have that

$$1 \to (1 + j) \to (1 + j)^2 = (1 + i) \implies i = 2j + j^2.$$

Therefore, the problem reduces to solving for $j$.

Let $v = \frac{1}{1 + j}.$

Then $v^k$ represents the present value of a payment of $1$ that will be made $[k \times (6 ~\text{months})]$ from now.

Therefore, 50 payments of $1$, every 6 months, starting 6 months from now have a present value of

$$\sum_{k=1}^{50} ~v^k ~~=~~ \frac{v \times [1 - v^{50}]}{1 - v}.$$

Therefore

$$10000 = 350.435 \times \frac{v \times [1 - v^{50}]}{1 - v}.$$

Let $m = \frac{10000}{350.435} \approx 28.536.$

Then $$m ~\text{is slightly less than} ~\frac{v}{1 - v}.$$

If you were to presume that

$$m = \frac{v}{1-v} ~~~\text{then} ~~~m(1 - v) = v \implies v = \frac{m}{m+1} \approx 0.96614.$$

Proceeding iteratively, and using $0.96614$ as the first estimate of $v$ gives

$$\left[1 - v^{(50)}\right] \approx 0.8213.$$

Refining the first estimate:

$$m \approx \frac{v(0.8213)}{1-v} \implies m(1 - v) = v(0.8213) \implies v = \frac{m}{m+(0.8213)} \approx 0.97202.$$

Using the second estimate of $v = 0.97202$ gives

$$1 - v^{(50)} \approx 0.758.$$

Refining the second estimate:

$$m \approx \frac{v(0.758)}{1-v} \implies m(1 - v) = v(0.758) \implies v = \frac{m}{m+(0.758)} \approx 0.9741.$$

Using the third estimate of $v = 0.9741$ gives

$$1 - v^{(50)} \approx 0.7304.$$

Refining the third estimate:

$$m \approx \frac{v(0.7304)}{1-v} \implies m(1 - v) = v(0.7304) \implies v = \frac{m}{m+(0.7304)} \approx 0.9750.$$

Obviously, you could continue this process with a hand calculator and get an estimate for $v$ accurate to within 7 or 8 decimal places in less than an hour. I suspect that the spreadsheet is doing something very similar.

Anyway, if you presume that $~v = 0.9750~$, then

$$1 + j = \frac{1}{v} \approx \frac{1}{0.9750} \implies j \approx 0.256 \implies i = 2j + j^2 \approx 0.519.$$

For an intuitive explanation of why this estimate is on the high side, notice that each refinement of $v$ yielded a slightly higher estimate than the previous refinement. The refinements stopped after the fourth estimate of $v$.

Answer 4

Starting from @user2661923's answer, you need to solve for $v$ the equation $$k= \frac{v \times [1 - v^{50}]}{1 - v}$$ and we know that $v$ is close to $1$.

Using series expansion, the rhs is $$50+1275 (v-1)+20825 (v-1)^2+249900 (v-1)^3+\cdots$$ Using series reversion $$v=1+t-\frac{49 }{3}t^2+\frac{3038}{9} t^3-\frac{1029049 }{135}t^4+\frac{72902494 }{405}t^5+O\left(t^6\right)$$ where $t=\frac{k-50}{1275}$.

Using your number $k= \frac{10000}{350.435}$, this gives as an estimate $v \sim 0.976071$ while the "exact" solution is $0.975900$.