Emulating Solver with LAMBDA

Introduction

Excel’s Solver is a powerful optimization tool, but some companies impose restrictions on its use. A friend of mine faced this challenge when his client asked him to recreate a financial model he had previously built using Solver, but without Solver, VBA, or enabling iterative calculations. Seeking an alternative, he wondered if Excel’s LAMBDA function could provide a solution.

Recursive LAMBDA functions can solve optimization problems like this dynamically. In this article, we’ll explore APRxNPVλ, a LAMBDA function that leverages recursion to determine the APR that yields the smallest NPV.

Problem Statement

Here is a example of the worksheet needing our attention.

In the above example, we have been tasked to find the effective APR (Annual Percentage Rate) on Bank Interest (Actual/360) which we can do by finding the rate at which the Net Present Value (NPV) of a given set of cash flows and discount factors is 0, or as close as we can get. Traditional methods, such as Solver, can find the APR by iteratively adjusting it to minimize our NPV calculation until any additional adjustments make no changes between iterations. The APRxNPVλ function replicates this process using recursion within Excel’s LAMBDA framework.

How APRxNPVλ Works

1. Initialization

• The function starts by setting an initial APR of 0%.
• It calculates the initial NPV using a helper function, NPVλ (how NPVλ works is not important to this discussion).
• A large starting difference (100%) is set to ensure iteration begins.
• We then 'call' the recursive function we defined in LET step: APRxNPV (No λ symbol).

2. Recursive Refinement

• In each iteration: The APR is adjusted based on whether the NPV is positive or negative. If NPV is negative we have gone too low and we increase APR by half the previous difference. If NPV is positive we have gone to high and we decrease APR.
• This continues until the APR stops changing, meaning the optimal solution has been found.

Breakdown of the LAMBDA Components

Here is the LAMBDA.

• Like all LAMBDAs, we start by declaring arguments. These arguments are needed by NPVλ().
• We then define the Recursive LAMBDA (APRxNPV) inside our host function. The host function (APRxNPVλ with a λ symbol) is not recursive (does not call itself). The interior recursive function calls itself until convergence is achieved.
• NewAPR is the LET step that, based on NPV being positive or negative, adds or subtracts half the difference in APRs between iterations. We can see how this impacts NPV in the table at the bottom of this article.
• Exit Condition: When Excel cannot detect any difference in APRs between iterations, the routine exits.

Why Use This Approach?

? No Need for Solver – Works in environments where Solver or add-ins are prohibited.

? Fully Dynamic – Updates automatically when input data changes.

? Embedded in a Single Cell – Eliminates external VBA or add-ins.

Conclusion

LAMBDA can emulate Excel's Solver feature when circumstances prohibit Solver's use.

Table of each iteration's results.

Here is what is produced with each iteration of APRxNPV. It clearly shows how the routine resolves to the smallest NPV until adjustments to APR make no difference.