In a comment on Using LINEST for non-linear curve fitting Lori Miller posted a formula that found the coefficients fitting a cubic polynomial curve to a set of data, then solved the resulting equation to find the X value for a given Y value:
=IRR(LINEST(Yrange,Xrange^{1,2,3})-{0,0,0,1}*YValue)*1+1
which recently raised a question of how it works.
The Linest part is straightforward, as explained in the body of the post, it returns the coefficients of the cubic curve of best fit through the points specified in XRange and YRange: y = ax3 + bx2 + cx +d
These values are returned as an array, and subtraction of the array {0,0,0,1}*YValue subtracts Y from the d value, resulting in ax3 + bx2 + cx +d – y = 0
The IRR part requires some background, including definition of Net Present Value (NPV) and Internal Rate of Return (IRR).
If a series of cash flows are made at regular intervals, the present value of those cash flows may be found by applying a discount to those occurring in the future:
where: CF0, CF1, … CFt are the cash flows, and r is the discount rate over each time step.
The Internal Rate of Return (IRR) is then defined as the discount rate that will result in the cash flows having a NPV equal to zero:
Which may be written as:
Multiplying both sides by the denominator:
Which may be written as:
where a0 to an are the coefficients of the polynomial equation, and x = 1+IRR.
It follows that the function IRR({a, b, c, d – y}) + 1 is the desired solution to the equation:
ax3 + bx2 + cx +d – y = 0
As stated in the original comment, this approach is easily adapted to higher powers, although the use of cubic splines will often give more stable results.
Finally for those wanting to solve high order polynomials also consider the use of the Jenkins-Traub method, which has been incorporated in a user defined function, which is described in: Evaluating higher order polynomials, which will work in situations where the IRR method will not return a result (such as roots with negative value), often provides better precision, and will return complex roots.
Note: the derivation of the polynomial solution from the definition of IRR was taken from:
Newton Excel Bach, not (just) an Excel Blog 
Sometimes IRR solutions are multiple – will the IRR equation above still work if that is the case?
Thanks!
LikeLike
David – IRR has an optional “guess” argument, and if you enter avalue for this it will return the solution closest to the guess value.
“Guess Optional. A number that you guess is close to the result of IRR.
Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can’t find a result that works after 20 tries, the #NUM! error value is returned.
In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent). “
LikeLike
Pingback: How To Calculate An Internal Rate of Return (IRR) | Borrowing Money: Your Handy Guide