6.2 Core Formulas and Error Handling

What Core Formulas Means on the FMVA Excel Domain

Excel is about 10% of the FMVA final, and the case studies are Excel files. The tested skill is not memorizing every function in the ribbon. It is writing formulas that (1) copy cleanly across a forecast, (2) react to assumption switches, and (3) fail loudly when a driver is missing instead of silently showing a plausible but wrong number.

This section is the logic layer: IF, AND, OR, IFS, IFERROR, IFNA, the error codes you will actually see in a three-statement file, 1/0 boolean switches, and the difference between a circular reference and a broken formula. Lookups, SUMIFS, and NPV/IRR live in the next chapter.

IF, AND, and OR

IF returns one value when a test is TRUE and another when it is FALSE:

=IF(test, value_if_true, value_if_false)

AND is TRUE only if every argument is TRUE. OR is TRUE if any argument is TRUE. Wrap them inside IF when you need a number or a label, not just TRUE/FALSE.

Worked example: volume-and-price commission

Apex pays a 12% commission only when unit volume is at least 10,000 and price is above $50. Otherwise commission is 10%. Revenue is $600,000.

AND(12000>=10000, 55>50) is TRUE, so B5 = 0.12. Commission = $600,000 × 0.12 = $72,000.

If volume is 9,500 and price is $80, AND is FALSE (volume fails) even though price is high. Rate = 0.10. Commission = $60,000. That is the exam trap: AND is not "average the conditions." One FALSE kills the whole AND.

OR would fire the 12% rate if either volume or price clears the hurdle:

=IF(OR(B2>=10000,B3>50),0.12,0.10)

With volume 9,500 and price $80, OR is TRUE, rate 12%, commission $72,000. Do not confuse AND with OR on a case with two covenants (for example, leverage below 3.0x and interest coverage above 4.0x). A credit flag that uses OR will stay green when only one covenant holds.

Boolean tests inside AND/OR do not need IF. AND(B2>=10000,B3>50) already returns TRUE/FALSE. Nesting IF(B2>=10000,TRUE,FALSE) is noise and a place for parentheses to go wrong.

Nested IF Versus IFS

A nested IF chooses among more than two outcomes by putting another IF in the false branch.

Apex tax rate by EBT:

• EBT ≤ 0 → 0%
• EBT ≤ $50,000 → 15%
• EBT ≤ $200,000 → 25%
• otherwise → 35%

Nested:

=IF(B10<=0,0,IF(B10<=50000,0.15,IF(B10<=200000,0.25,0.35)))

If EBT is $80,000: first test FALSE, second FALSE, third TRUE → 25%. Tax = 0.25 × $80,000 = $20,000.

If EBT is $40,000, the second test is TRUE → 15%. Tax = $6,000.

IFS (Excel 2016+, which CFI requires) writes the same logic as a list of tests:

=IFS(B10<=0,0,B10<=50000,0.15,B10<=200000,0.25,TRUE,0.35)

The final TRUE,0.35 is the catch-all, the IFS equivalent of the last nested false-branch. Without that TRUE pair, an EBT of $500,000 returns #N/A because no test succeeded.

Order matters. If you test B10<=200000 before B10<=50000, an EBT of $40,000 hits 25% and never sees 15%. Always go from the first-true bucket you actually want — usually the most restrictive cutoff first, in the same order you would read a tax table.

Exam trap: IFS does not mean "evaluate all tests and add them." It returns the result for the first TRUE test only. If you need a sum of conditions (revenue where region is East and product is A), use SUMIFS (next chapter), not IFS.

A second trap is mixing text thresholds with numbers: IFS(B10<="50000",0.15,...). The text "50000" can throw #VALUE! or sort in an unexpected order. Keep cutoffs as numbers, the same way you keep 0.25 not "25%" in a tax cell.

Error Values You Must Read, Not Hide

#REF! and #NAME? are structural. Blanketing them with IFERROR turns a deleted debt schedule into a quiet zero and your WACC becomes 0%. The case then values the firm with a 0% discount rate. That is a worse error than the original #REF!.

Worked example: margin and #DIV/0!

EBIT in B20 is $150,000. Revenue in B12 is $1,000,000. EBIT margin = =B20/B12 = 15%.

If you copy the row to a blank scenario column where revenue is 0, you get #DIV/0!. A clean guard:

=IF(B12=0,0,B20/B12)

That returns 0 when there is no revenue, which is a modeling choice you can explain. It is better than IFERROR here because IFERROR would also hide a #VALUE! from a texted revenue cell. If B12 is the text n.a., =IFERROR(B20/B12,0) prints 0% margin and a reviewer thinks EBIT is zero. The IF(B12=0,...) version still shows #VALUE!, which is the correct alarm.

IFERROR Versus IFNA

IFERROR(value, value_if_error) catches any error: #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #NULL!.

IFNA(value, value_if_na) catches only #N/A. Every other error still surfaces.

Use IFNA on lookups where a missing ticker should become 0 or a label, but a #REF! should still scream. Use IFERROR only when you have already constrained the inner formula so the only plausible errors are the ones you intend to trap.

Worked example: peer multiple

=IFNA(XLOOKUP(A2,Peers[Ticker],Peers[EV_EBITDA]),"missing peer")

• Ticker not in the table → the label missing peer
• You accidentally deleted the EV/EBITDA column → #REF! still appears. Good.

=IFERROR(XLOOKUP(...),0) would turn that deleted column into 0, and the football-field chart would plot a zero multiple.

A second legitimate IFNA: a MATCH that is allowed to miss because that peer is not in this year's screen. A #NAME? from typing XLOKUP must not be caught by the same wrapper.

Rule: if you cannot name the error you are trapping, do not trap it. IF(B12=0,0,B20/B12) names #DIV/0!. IFNA names a failed lookup. IFERROR(everything,0) names nothing.

Boolean 1/0 Switches

Financial models use 1 and 0 as on/off switches because they multiply.

Let Growth in B5 be 6%. Let Case in B1 be 1 for the growth case and 0 for a no-growth case.

=C12*(1+B5*$B$1)

• Case = 1 → revenue grows 6%.
• Case = 0 → 1 + 0.06 * 0 = 1 → flat.

If opening revenue C12 is $1,000,000, Case 1 produces $1,060,000 and Case 0 produces $1,000,000. The same formula sits in every year. You do not rewrite the row when the case changes.

Excel treats TRUE as 1 and FALSE as 0 in arithmetic. Two common coercions:

• --(B1="Upside") double-unary: TRUE → 1, FALSE → 0
• (B1="Upside")*1 or N(B1="Upside")

Worked example: three-case switch

=C12*(1+INDEX($C$5:$C$7,$B$1))

If B1 = 2, INDEX returns 6%, $1,000,000 grows to $1,060,000. If someone types 4, INDEX returns #REF! — a loud failure, which is what you want. An IFERROR wrapper that returns 0% growth would hide the bad case number and present a flat forecast as if it were intended.

A CHOOSE version: =C12*(1+CHOOSE($B$1,0.02,0.06,0.10)). CHOOSE with B1 = 4 returns #VALUE!. Same principle: bad inputs should not look like base case.

Flags on a debt schedule: =IF($B$1=1,Revolver_draw,0) or =Revolver_draw*($B$1=1) once you are comfortable with TRUE/FALSE math. The 1/0 form copies well and stays numeric for data tables (Chapter 8). Text flags ("Yes"/"No") belong in the assumption block; convert them once to 1/0, then multiply.

Circularity Versus Real Errors

A circular reference means a formula depends, directly or indirectly, on its own cell. Excel's status bar shows Circular References and, with iteration off, the cell may compute as 0 or freeze.

Common intentional circularity in FMVA models: interest is a function of average debt, cash depends on interest, the revolver plugs cash, and the revolver is debt — so interest depends on the plug that depends on interest. CFI teaches that pattern in three-statement work (a later chapter covers circular references and iteration). The "error" is a design choice you turn on via File → Options → Formulas → Enable iterative calculation, with a max iteration count and a maximum change (for example 100 iterations, 0.001).

That is not the same as #REF!, #DIV/0!, or #NAME?.

Worked example: accidental versus intended

Accidental: in B12 you type =B12*(1+0.06). That is a self-reference. Excel flags a circular reference. There is no economic loop. Rewrite as =C12*(1+$B$5) or =B11*(1+$B$5).

Intended: Interest = average(opening revolver, closing revolver) × 8%. Closing revolver = the cash plug. Cash includes after-tax interest. Iteration converges: suppose the plug settles at $40,000, interest = 8% × $40,000 = $3,200 if you used closing balance only (average-balance math is similar once opening is known), and after tax the cash need is still $40,000 within $0.001.

If iteration is off, the intended revolver model may show 0 interest or a stale value and the balance sheet will not balance. That is a settings problem, not a #NAME? problem. If iteration is on and you still see #REF!, you deleted a sheet — iteration will not heal #REF!.

Exam rule of thumb: treat #DIV/0!, #VALUE!, #N/A, #NAME?, and #REF! as bugs in formulas or structure. Treat an announced circular reference as either (a) a revolver/interest loop you expected or (b) a cell that points at itself by mistake. Enabling iteration to silence a #DIV/0! is the wrong tool. Wrapping a circular plug in IFERROR(...,0) is how a $40,000 revolver becomes a quiet zero and the statements no longer articulate.