Why does Excel Mac instantly flag your loan amortization model as an error? Why does it refuse to calculate even when you know the logic is sound? Why does the same file work fine on Windows but throws #REF! on your MacBook?
The answer isn’t a bug—it’s Excel Mac’s default safety lock. Unlike Windows, Excel for Mac doesn’t just warn about circular references—it blocks them outright… unless you flip two hidden switches. And no, turning on 'Enable iterative calculation' alone won’t cut it. You also need to adjust the max iterations and maximum change values—and do it before entering the formula. Miss that order, and Excel ignores your settings entirely.
The Setup
You’re building a cash flow projection for a small SaaS startup. The model tracks monthly net cash flow, where ending cash = beginning cash + net income – planned capital expenditure. But here’s the twist: the capital expenditure amount depends on whether ending cash falls below $50,000 (triggering a $15,000 emergency buffer draw). That creates a natural loop: ending cash → buffer decision → capex → ending cash.
| Month | Beginning Cash | Net Income | CapEx | Ending Cash |
|---|---|---|---|---|
| Jan-24 | $124,700 | $28,300 | $0 | $153,000 |
| Feb-24 | $153,000 | $31,200 | $0 | $184,200 |
| Mar-24 | $184,200 | $26,800 | $0 | $211,000 |
| Apr-24 | $211,000 | $19,400 | $0 | $230,400 |
| May-24 | $230,400 | $22,100 | $0 | $252,500 |
| Jun-24 | $252,500 | $14,900 | $0 | $267,400 |
| Jul-24 | $267,400 | $8,200 | $0 | $275,600 |
| Aug-24 | $275,600 | $−12,500 | $0 | $263,100 |
| Sep-24 | $263,100 | $−38,700 | $0 | $224,400 |
| Oct-24 | $224,400 | $−52,900 | $0 | $171,500 |
The Challenge
You try entering this in cell D11 (CapEx for Oct-24): =IF(E11<50000,15000,0), where E11 is Ending Cash. But E11 contains =C11+D11-B11 — yes, it references D11 itself. Excel Mac immediately shows #REF! and disables the formula. No warning dialog. No option to proceed. Just silence and red text. The beauty of this approach is that it’s mathematically valid — it converges in 3–4 iterations — but Excel Mac treats it like dangerous code until you configure both the calculation engine and the formula entry sequence correctly.
Walking Through It
Step 1: Go to Excel → Preferences → Calculation. Uncheck “Limit iteration” — wait, no. That’s the trap. Don’t uncheck it. Instead, check it, then set Maximum iterations to 100 and Maximum change to 0.001. Yes — you must enable iteration before typing the circular formula. If you type the formula first, Excel locks the setting grayed out.
Step 2: Press Cmd+, to open Preferences fast — then click Calculation. Now enter your formula in D11: =IF(E11<50000,15000,0). Still red? Good. That means Excel recognizes the loop but hasn’t recalculated yet.
Step 3: Force recalculation with Cmd+=. Watch E11 update from #REF! to $171,500 — and D11 stays 0. Why? Because $171,500 ≥ $50,000. Now artificially lower E10 (Sep-24 ending cash) to $42,800. Press Cmd+= again. D11 flips to 15000, and E11 recalculates to $42,800 − $52,900 + $15,000 = $4,900. Then D11 rechecks: IF(4900<50000,15000,0) → still 15000. Stable. Converged.
| Before (no iteration enabled) | After (iteration enabled + recalc) |
|---|---|
D11: #REF!E11: #REF! | D11: 0E11: $171,500 |
D11: #REF!E11: #REF! | D11: 15000E11: $4,900 |
The Result
Here’s how the final 4 months look — now fully dynamic, with CapEx auto-triggering only when needed:
| Month | Beginning Cash | Net Income | CapEx | Ending Cash |
|---|---|---|---|---|
| Jul-24 | $267,400 | $8,200 | $0 | $275,600 |
| Aug-24 | $275,600 | $−12,500 | $0 | $263,100 |
| Sep-24 | $263,100 | $−38,700 | $0 | $224,400 |
| Oct-24 | $224,400 | $−52,900 | $0 | $171,500 |
| Nov-24 | $171,500 | $−61,200 | $0 | $110,300 |
| Dec-24 | $110,300 | $−75,400 | $15,000 | $49,900 |
| Jan-25 | $49,900 | $−8,100 | $15,000 | $56,800 |
| Feb-25 | $56,800 | $12,300 | $0 | $69,100 |
What Could Go Wrong
Mistake #1: Enabling iteration after typing the formula. Excel Mac won’t recalculate the circular cell — it stays #REF! until you delete and re-enter the formula. No error message. Just stubborn silence.
Mistake #2: Leaving Maximum Change at 0.01. With coarse tolerance, Excel may stop too early — e.g., reporting $49,999 instead of $49,999.999 — triggering CapEx one month too soon. Set it to 0.001 or lower for financial models.
Mistake #3: Forgetting Cmd+= after changing preferences. Unlike Windows, Excel Mac doesn’t auto-recalculate on preference changes. You must manually trigger it — or your numbers won’t update.
| Action | Mac Shortcut | When to Use It |
|---|---|---|
| Open Preferences | Cmd+, | Before entering any circular formula |
| Force full recalc | Cmd+= | After enabling iteration or changing inputs |
| Edit formula in cell | F2 | To verify or tweak circular logic |
| Toggle formula view | Cmd+` | Spot unintended circular dependencies |
