## Linest, NPV, IRR and solving polynomials

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:

NPV and IRR by Dan Saunders

This entry was posted in Excel, Maths, Newton, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

### 3 Responses to Linest, NPV, IRR and solving polynomials

1. david k waltz says:

Sometimes IRR solutions are multiple – will the IRR equation above still work if that is the case?

Thanks!

Like

• dougaj4 says:

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

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.