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
      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.


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.

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