## Function roots with the Inverse Quadratic Method

An earlier post presented various methods for finding the roots of polynomial functions based on the use of linear interpolation.  It is sometimes advantageous to use a quadratic interpolation function, and methods using this approach will be presented in this and following posts.  The earlier functions and the new quadratic interpolation functions have been incorporated in the spreadsheet ItSolve Functions.xls

Any three points on a plane (other than co-linear points) will define a unique quadratic curve of the form ax2 + bx + c = 0.  The User Defined Function (UDF) FITQUAD returns an array of the three coefficients, a, b and c:

A = ((Y2 – Y1) * (X1 – X3) + (Y3 – Y1) * (X2 – X1)) / ((X1 – X3) * (X2 ^ 2 – X1 ^ 2) + (X2 – X1) * (X3 ^ 2 – X1 ^ 2))
B = ((Y2 – Y1) – A * (X2 ^ 2 – X1 ^ 2)) / (X2 – X1)
C = Y1 – A * X1 ^ 2 – B * X1

This quadratic equation may then be solved in the usual way to find the x values for which it evaluates to zero.  The procedure for applying this method to evaluating the roots of higher order functions is:

• Select three known points on the function to be solved, in the region of the solution of interest, with at least one point on each side of the x-axis
• Fit a quadratic curve to these three points
• Find the root of the quadratic curve in the range of interest
• Find the y value of the higher order function at this x value.
• Replace the furthest outlying of the three trial points with this new point.
• Repeat until the error is acceptably small.

The screenshot below shows this procedure carried out on the spreadsheet, using the FitQuad function:

The process is automated using the UDF QuadSolve():

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

### 5 Responses to Function roots with the Inverse Quadratic Method

1. Lori Miller says:

Interesting analysis, although it does require more assumptions for convergence than Newton’s method. A next step might be to incorporate in Brent’s method which is popular in quant finance eg for finding implied volatility.

For a worksheet function approach to find the root you can try:
=IRR(LINEST(C13:C15,B13:B15^{1,2}))*1+1

Like

2. dougaj4 says:

Hi Lori.

Brent’s method is planned for the 3rd and final post in this series.

Like

3. Lori Miller says:

Suspected that’s where you might be heading, it’s not straightforward so looking forward to the final post.

Just one simplification you’re probably aware of: instead of finding the root of the quadratic y(x), you can solve for the inverse quadratic x(y) with y=f(x)=0 (cf. wikipedia). This just amounts to swapping x’s and y’s in the formulas posted and setting c=0 which should be a bit more efficient.

An equivalent for this is =TREND(B13:B15,C13:C15^{1,2},{0,0})

Like

4. dougaj4 says:

Lori – see latest post 🙂