GRG Nonlinear for Variance Matching — How Excel Solver Actually Finds the Optimal Weights

In fixed-income risk, when you're trying to represent a bond (or portfolio) using two reference instruments, say a 2Y and a 10Y benchmark, you need weights W1 and W2 such that the variance of the combined position matches the variance of the actual position. This isn't a linear problem, because variance involves squared terms and cross-covariance terms:

σ²_portfolio = W1²·σ1² + W2²·σ2² + 2·W1·W2·σ1·σ2·ρ

You want this to equal your target variance (say, the actual bond's variance), subject to a constraint like W1 + W2 = 1. Since the objective is quadratic (nonlinear) in the decision variables, you can't just invert a matrix like you would in OLS. You need an iterative numerical solver. That's where GRG Nonlinear (Generalized Reduced Gradient) comes in — it's the engine behind Excel Solver's nonlinear mode.

GRG stands for Generalized Reduced Gradient.

1. Generalized: The algorithm handles general nonlinear problems. It doesn't require the objective function to be linear or quadratic; it works for any smooth, differentiable function. Our variance function is quadratic in the weights, which is a special case of a smooth nonlinear function. GRG handles it naturally.

2. Reduced: The algorithm reduces the dimensionality of the search by exploiting the constraints. In our problem, the constraint W1 + W2 = 1 implies that there is only one truly free variable; once W1 is fixed, W2 is determined. GRG recognizes this and searches in the reduced space of free variables rather than treating W1 and W2 as independently free.

3. Gradient: The algorithm uses the gradient of the objective function, the vector of partial derivatives, to determine the direction of improvement at each step.

Why "Reduced" Gradient?

The word "reduced" is the key insight. You have two decision variables, W1 and W2, but they aren't independent; the constraint W1 + W2 = 1 ties them together. Instead of treating this as a constrained 2-variable problem, GRG eliminates the constraint algebraically: W2 = 1 - W1. Now you're optimizing over a single free variable, W1, and W2 just rides along automatically. This is the "reduction" in dimensionality; you've reduced a constrained 2D problem to an unconstrained 1D search.

The Step-by-Step Mechanics:

1. Read Current Weights: Solver starts from whatever values are sitting in the weight cells — either your initial guess (e.g., 50/50), linearly interpolated, or wherever the last iteration left off.

2. Reduce Dimensionality: W1 is designated the basic (free) variable. W2 becomes nonbasic — algebraically dependent on W1 via the constraint. This is done once, conceptually, at the start of the algorithm's setup, and applied at every step.

3. Compute the Numerical Gradient: Since there's no closed-form derivative being fed to Solver, GRG perturbs W1 by a tiny amount (finite difference — forward or central difference), recalculates the error (the objective function — usually the squared difference between portfolio variance and target variance), and estimates:

∂Error/∂W1 ≈ [Error(W1 + h) − Error(W1)] / h

This tells Solver the slope of the error surface at the current point.

4. Determine the Search Direction: If the gradient is positive, increasing W1 increases the error — so Solver should move in the negative direction (steepest descent), or a smarter direction if using conjugate gradient / quasi-Newton updates (Excel's GRG uses a variant of this for faster convergence than plain steepest descent).

5. Line Search: Rather than picking an arbitrary step size, Solver searches along the chosen direction to find the α that minimizes error the most — effectively solving a 1D sub-optimization: "how far should I move along this line before the error stops improving?" This usually involves a few trial evaluations (quadratic or golden-section interpolation) to home in on the best α without overshooting.

6. Update the Decision Variables: W1 (new) = W1 (old) + α × direction. Because W2 is algebraically tied to W1, it updates automatically — no separate step needed. This is exactly why the constraint elimination in Step 2 pays off: you never need to project back onto the constraint surface; you're always on it by construction.

7. Check Convergence: Two checks typically run in parallel:

   1. Error Tolerance: Has the objective function (e.g., squared variance error) dropped below a threshold like 10⁻⁸?

   2. Gradient Magnitude: Has the slope flattened out below a tolerance, indicating you're near a stationary point (local minimum)?

      
      

      Either condition (or both, depending on Solver settings) signals convergence.

8. Iterate or terminate. If not converged, go back to Step 3, recompute the gradient at the new point, and repeat. If converged, the Solver reports the final W1 (and derived W2) as the optimal weights.

Key Intuitions:

• Why not solve it analytically? You technically could take the derivative of the error function with respect to W1 directly (since W2 = 1 - W1 makes it a true 1-variable quadratic), set it to zero, and solve algebraically. GRG's numerical approach is more general — it works even when the objective function isn't cleanly differentiable by hand, or when you have many more instruments and constraints layered on top (multiple benchmarks, bounds on weights, etc.). The mechanics scale to complexity where hand-derivation breaks down.

  
  

• The finite-difference gradient is an approximation. The step size "h" used in Step 3 matters — too large and you get an inaccurate slope; too small and you risk numerical noise (floating-point precision issues) dominating the estimate. Excel Solver has internal defaults for this, but it's worth knowing this is happening under the hood every time you click the "Solve" button.

  
  

• Local vs global minimum. GRG is a local solver — it converges to whatever stationary point is closest to your starting weights, following the downhill path. For a genuinely convex problem like standard variance matching with two positively correlated instruments, this usually isn't an issue since there's typically one basin. But if you have unusual correlation structures or added constraints, it's worth testing multiple starting points to confirm you're not stuck in a local rather than global minimum.

  
  

• Constraint handling by elimination vs. penalty. Notice GRG's specific trick here — it doesn't penalize constraint violations (like penalty-method optimizers do); it eliminates the constraint by substitution wherever algebraically possible. This is cleaner and faster when constraints are simple equalities like a sum-to-one, which is exactly the variance-matching setup.