*Go to the Prior Tip*** For Forecasts, Bet on Monte Carlo Methods**

Return to MaxValue Home Page

Back in the dark ages, c. 1983, I worked up an interpolation method that performs a Goal Seek operation, similar to what Microsoft^{®} Excel^{®} offers. I've used the formula many times over the years, including this month. It seems to work in all situations with a monotonically-increasing or -decreasing function, i.e., a smooth curve that doesn't change slope sign (at least in the range of interest).

We often want to find a value (C) that results in a target value (T) calculation.

An example is when an internal rate of return (IRR) function is not available or is unsuited to the timing of a specific cashflow sequence.

In the case of *IRR*, we want to find the PV discount rate (*i*) rate that results in *NPV* = $0. Most often, there is only one solution.

Suppose we have this net cash flow (*NCF*) stream: -1200, 300, 1000, 800, 600, 400, 200 $k

For simplicity, I'm assuming uniform cashflow in each full calendar year and mid-year discounting.

Total net cashflow = $2100k

Excel's NPV function, discounting at .10/yr and adjusted to mid-year = $1212.1k

Excel's IRR function = .4329/yr (which is correct in this case with uniform time periods)

Here is my Goal Seek formula:

where

*C*1 and *C*2 are *IRR* approximations (ideally, they will bracket the solution *IRR*).

*V*1 and *V*2 are *NPV*s calculated using *C*1 and *C*2.

*T* is the target value, *NPV* = $0 in the case of solving for *IRR*.

*C* is the next approximation.

Application:

- 1. Set up to solve for your target value (i.e.,
*IRR*) as a function of a driver variable (in this case, the PV discount rate,*i*). - 2. Pick two reasonable values for
*i*,*C*1 and*C*2. Preferably, they encompass the anticipated solution.

(If they are too far away from the solution, the formula will sometimes give ridiculous results. I sometimes sweep a wide range until I find two values,*C*1 and*C*2, such that their corresponding*V*1 and*V*2 values are on opposite sides of*T*.) - 3. Calculate the
*NPV*values,*V*1 and*V*2, that correspond to*i*=*C*1 and*C*2. - 4. Solve for the new approximation,
*C*, using the formula, above. - 5. Calculate
*V*using*C*. - 6. If
*V*is very close to*T*, then declare*C*as the*IRR*solution and exit. - 7. Determine which two of
*V*1,*V*2, and*V*are closest to the goal seek target (*T*)*NPV*= $0. Keep those two*V*s and the*C*s used in their calculation. - 8. The two noted
*C*s are now*C*1 and*C*2.

- 9. Rearrange, if needed, so that
*C*1 <*C*2; the corresponding*V*s are*V*1 and*V*2. - 10. Loop back to Step 4 for another iteration.

This schedule shows a high precision *IRR* calculation achieved in just seven iterations:

The encircled *V* pair are the two *NPV* values closest to *T*=$0 *NPV*, and the next interpolation

uses their corresponding *C* values (representing NPV discount rates, *i*).

The arrows on the right-hand-side for *C*s show the progression pairings of the *C*s corresponding to the two best *V*s

This next chart shows the progression to solution:

The Initial Span endpoints show *NPV*s at .20 and .80 initial PV discount rates.

Points labeled 1,2,3, and 4 are successive iterations. Interation points 5,6, and 7 pile up just left of point 4.

This method is simple and converges reasonably fast. If the calculation is computationally expensive, then Newton's method (only a bit more complicated; see Wikipedia article) will converge in about 1/3 fewer iterations.

—John Schuyler, September 2017. Revised October 2017.

Copyright © 2017 by John R. Schuyler. All rights reserved. Permission to copy with reproduction of this notice.