Goal Seek Method for Fast IRR Solutions

Tip of the Week #133 Tip Index

Go to the Prior Tip For Forecasts, Bet on Monte Carlo Methods
Go to the Next Tip Tim’s Yacht—Betting for a potential huge payoff
Return to MaxValue Home Page

Goal Seek Solution for Fast IRR Solutions: Interpolation Method

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:    Interpolation Formula
   where
      C1 and C2 are IRR approximations (ideally, they will bracket the solution IRR).
      V1 and V2 are NPVs calculated using C1 and C2.
      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, C1 and C2. 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, C1 and C2, such that their corresponding V1 and V2 values are on opposite sides of T.)
3. Calculate the NPV values, V1 and V2, that correspond to i = C1 and C2.
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 V1, V2, and V are closest to the goal seek target (T) NPV = $0. Keep those two Vs and the Cs used in their calculation.
8. The two noted Cs are now C1 and C2.
9. Rearrange, if needed, so that C1 < C2; the corresponding Vs are V1 and V2.
10. Loop back to Step 4 for another iteration.

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

Interpolation Schedule

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 Cs show the progression pairings of the Cs corresponding to the two best Vs

This next chart shows the progression to solution:

Interpolation Chart

The Initial Span endpoints show NPVs 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.