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