FIXED_POINT
Overview
The FIXED_POINT function locates a scalar value x that satisfies f(x) = x by wrapping scipy.optimize.fixed_point. It iteratively evaluates a user-specified expression in Excel until the solution changes by less than a desired tolerance, with optional control over the acceleration method. This example function is provided as-is without any representation of accuracy.
Usage
To use the function in Excel:
=FIXED_POINT(func_expr, x_zero, [xtol], [maxiter], [fixed_point_method])func_expr(string, required): Expression in the variablexthat defines the iteration (for example,"math.cos(x)").x_zero(float, required): Initial guess for the fixed-point iteration. A 2D list with a single numeric entry is also accepted.xtol(float, optional, default=1e-8): Absolute tolerance for convergence.maxiter(int, optional, default=500): Maximum number of iterations allowed.fixed_point_method(string (enum), optional, default="del2"): Acceleration strategy. Valid options:del2(Aitken acceleration) anditeration(simple fixed-point iteration).
The function returns a scalar (float) representing the fixed point if convergence criteria are met, or an error message string if validation fails or the iteration cannot converge.
Function Expressions
The func_expr parameter accepts mathematical expressions using any of the following functions and constants. All functions are case-sensitive.
| Function/Constant | Description | Example |
|---|---|---|
sin(x) | Sine function (radians) | sin(x) |
cos(x) | Cosine function (radians) | cos(x) |
tan(x) | Tangent function (radians) | tan(x) |
asin(x) or arcsin(x) | Arc sine function (radians) | asin(x) or arcsin(x) |
acos(x) or arccos(x) | Arc cosine function (radians) | acos(x) or arccos(x) |
atan(x) or arctan(x) | Arc tangent function (radians) | atan(x) or arctan(x) |
sinh(x) | Hyperbolic sine | sinh(x) |
cosh(x) | Hyperbolic cosine | cosh(x) |
tanh(x) | Hyperbolic tangent | tanh(x) |
exp(x) | Exponential function () | exp(x) |
log(x) or ln(x) | Natural logarithm (base ) | log(x) or ln(x) |
log10(x) | Base-10 logarithm | log10(x) |
sqrt(x) | Square root | sqrt(x) |
abs(x) | Absolute value | abs(x) |
pow(x, y) | Power function (x raised to power y) | pow(x, 2) |
pi | Mathematical constant π (≈3.14159) | x * pi |
e | Mathematical constant e (≈2.71828) | e**x |
Important Notes:
- All trigonometric functions use radians, not degrees
- Use
**(double asterisk) or^(caret) for exponentiation. Both work equivalently. - Examples:
"cos(x)","sin(x) + 0.1*x","exp(-x)"
Expression Examples
Common mathematical expressions and their func_expr notation:
- (fixed point ≈ 0.739) →
"cos(x)" - →
"sin(x) + 0.1*x" - →
"exp(-x)"or"e^(-x)" - →
"1 / (2 + x)"
Examples
Example 1: Cosine Fixed Point with All Parameters
Inputs:
| func_expr | x_zero | xtol | maxiter | method |
|---|---|---|---|---|
| math.cos(x) | 0.5 | 1E-10 | 600 | del2 |
Excel formula:
=FIXED_POINT("math.cos(x)", 0.5, 1E-10, 600, "del2")Expected output:
| Result |
|---|
| 0.739 |
This example demonstrates using non-default values for all optional parameters.
Example 2: Linear Affine Iteration
Inputs:
| func_expr | x_zero |
|---|---|
| 0.25*x + 3 | 0 |
Excel formula:
=FIXED_POINT("0.25*x + 3", {0})Expected output:
| Result |
|---|
| 4.000 |
Example 3: Exponential Decay with Tight Tolerance
Inputs:
| func_expr | x_zero | xtol |
|---|---|---|
| math.exp(-x) | 0.7 | 1E-10 |
Excel formula:
=FIXED_POINT("math.exp(-x)", 0.7, 1E-10)Expected output:
| Result |
|---|
| 0.567 |
Example 4: Simple Iteration Method
Inputs:
| func_expr | x_zero | method | maxiter |
|---|---|---|---|
| 1 + 0.3*x | 2.0 | iteration | 200 |
Excel formula:
=FIXED_POINT("1 + 0.3*x", 2, , , "iteration")Expected output:
| Result |
|---|
| 1.429 |
Python Code
import math
from scipy.optimize import fixed_point as scipy_fixed_point
def fixed_point(func_expr: str, x_zero, xtol: float = 1e-8, maxiter: int = 500, fixed_point_method: str = 'del2'):
"""
Find a fixed point x such that f(x) = x for a scalar function expression.
This function wraps scipy.optimize.fixed_point to find fixed points of scalar functions.
See: https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.fixed_point.html
Args:
func_expr: String expression defining the function of x (e.g., 'math.cos(x)'). Must contain variable 'x'.
x_zero: Initial guess for the fixed-point iteration as a float.
xtol: Absolute tolerance for termination of the iteration. Must be positive. Default is 1e-8.
maxiter: Maximum number of iterations allowed. Must be a positive integer. Default is 500.
fixed_point_method: Acceleration method. Options are 'del2' (Aitken-accelerated) or 'iteration' (simple fixed-point iteration). Default is 'del2'.
Returns:
float or str: Fixed-point value on success, otherwise an error message describing the issue.
This example function is provided as-is without any representation of accuracy.
"""
# Validate func_expr
if not isinstance(func_expr, str):
return "Invalid input: func_expr must be a string."
if 'x' not in func_expr:
return "Invalid input: func_expr must contain the variable 'x'."
# Validate x_zero (accepts only float)
try:
x0 = float(x_zero)
except (TypeError, ValueError):
return "Invalid input: x_zero must be a float."
# Validate numeric parameters (ensure proper types)
if not isinstance(xtol, (int, float)):
return "Invalid input: xtol must be a numeric value."
if not isinstance(maxiter, int):
return "Invalid input: maxiter must be an integer."
xtol = float(xtol)
maxiter = int(maxiter)
if xtol <= 0:
return "Invalid input: xtol must be positive."
if maxiter <= 0:
return "Invalid input: maxiter must be positive."
# Validate method
valid_methods = ['del2', 'iteration']
if fixed_point_method not in valid_methods:
return f"Invalid method: {fixed_point_method}. Must be one of: {', '.join(valid_methods)}"
# Build function from expression
def func(x):
try:
# Limit available globals to safe operations
safe_dict = {"x": x, "math": math}
safe_dict.update(math.__dict__) # Add math functions like sin, cos, exp, etc.
return eval(func_expr, {"__builtins__": {}}, safe_dict)
except Exception as exc:
raise ValueError(f"Error evaluating function: {exc}")
try:
result = scipy_fixed_point(func, x0=x0, xtol=xtol, maxiter=maxiter, method=fixed_point_method)
except Exception as exc:
return f"Error during fixed-point iteration: {exc}"
try:
value = float(result)
except (TypeError, ValueError):
return "Fixed-point iteration returned a non-scalar result."
if math.isnan(value) or math.isinf(value):
return "Fixed-point iteration failed: result is NaN or inf."
# Verify convergence by checking |f(value) - value|
try:
residual = abs(func(value) - value)
except Exception as exc:
return f"Error evaluating residual: {exc}"
if residual > xtol:
return f"Fixed-point iteration may not have converged: residual {residual} exceeds xtol {xtol}."
return value