Study guide

6.2 Core Formulas and Error Handling

Financial Modeling & Valuation Analyst FMVA

Track your progress as you read

Finished sections get a check mark.

1 Introduction

Current Exam Facts Program Path and How to Use This Guide

2 Chapter 1: Accounting Foundations

1.1 Accounting Principles 1.2 Reading Financial Statements 1.3 Accounting Decisions in Models

3 Chapter 2: Time Value and Capital Budgeting

2.1 Time Value of Money 2.2 NPV, IRR, and Decision Rules 2.3 Capital Budgeting Cash Flows

4 Chapter 3: Cost of Capital

3.1 Capital Structure 3.2 CAPM, Beta, and Cost of Equity 3.3 WACC

5 Chapter 4: Profitability and Returns

4.1 Profitability and Margins 4.2 Returns and DuPont Analysis 4.3 Per-Share Metrics and Growth

6 Chapter 5: Liquidity, Leverage, and Cash Flow

5.1 Liquidity and Working Capital Metrics 5.2 Leverage, Coverage, and Credit 5.3 Cash Flow Statement Analysis

7 Chapter 6: Excel Fundamentals

6.1 Shortcuts, Setup, and Navigation 6.2 Core Formulas and Error Handling 6.3 Named Ranges and Workbook Hygiene

8 Chapter 7: Excel Lookups and Financial Functions

7.1 Lookup Functions 7.2 Conditional Aggregations 7.3 Financial Functions

9 Chapter 8: Excel What-If Analysis and Auditing

8.1 Goal Seek and Data Tables 8.2 Formula Auditing and Protection

10 Chapter 9: Financial Modeling Guidelines

9.1 Model Structure and Layout 9.2 Color Conventions and Formatting 9.3 Integrity Checks and Documentation

11 Chapter 10: Three-Statement Modeling

10.1 Articulating the Three Statements 10.2 Building the Income Statement 10.3 Building the Balance Sheet and Cash Flow Statement

12 Chapter 11: Circularity and Model Audit

11.1 Circular References and Iteration 11.2 Auditing and Balancing a 3-Statement Model

13 Chapter 12: Operational Modeling

12.1 Revenue and Operating Drivers 12.2 Working Capital Modeling 12.3 Capex and Depreciation

14 Chapter 13: Debt Schedules, Scenarios, and Sensitivity

13.1 Debt and Interest Schedules 13.2 Scenario Analysis 13.3 Sensitivity Analysis

15 Chapter 14: Valuation Foundations

14.1 Valuation Methods and Enterprise Value 14.2 Unlevered Free Cash Flow

16 Chapter 15: DCF Valuation

15.1 DCF Structure, WACC, and Mid-Year Convention 15.2 Terminal Value and the Equity Bridge

17 Chapter 16: Comparable Valuation

16.1 Trading Comps and Multiples 16.2 Precedent Transactions and the Football Field

18 Chapter 17: Budgeting and Forecasting

17.1 Budget Types 17.2 Forecasting Techniques 17.3 Rolling Forecasts and Variance Analysis

19 Chapter 18: Presentation and Visuals

18.1 Chart Types and Selection 18.2 Dashboards and Data Visualization 18.3 PowerPoint and Pitchbooks 18.4 Presenting Model Outputs

20 Chapter 19: Professional Ethics and Qualitative Analysis

19.1 Professional Ethics 19.2 Qualitative Business Analysis

6.2 Core Formulas and Error Handling

Key Takeaways Explain This Topic AI

IF(AND(volume>=10000,price>50),0.12,0.10) returns 12% only when both tests are TRUE; volume 9,500 and price $80 still pays 10% because AND is not an average of the two hurdles.

IFS (Excel 2016+) returns the result of the first TRUE test in order; a nested IF is the same logic with parentheses, and a misplaced first test (EBT<=200000 before EBT<=50000) mis-buckets $40,000 of EBT at 25% instead of 15%.

IFERROR traps every error type; IFNA traps only #N/A — wrap a peer lookup in IFNA so a missing ticker can be labeled while a deleted column still shows #REF!.

#DIV/0!, #REF!, #VALUE!, #N/A, and #NAME? are formula or structure bugs; a revolver-interest circular reference is a loop that iteration may be designed to solve, not a reason to IFERROR the model to zero.

Boolean 1/0 switches multiply cleanly: =C12*(1+Growth*Case) is unchanged when Case is 0 and grows when Case is 1, which is the pattern data tables need later.

Last updated: August 2026

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.

  • Driver Cell Value
  • Volume B2 12,000
  • Price B3 $55
  • Revenue B4 $600,000
  • Rate B5 =IF(AND(B2>=10000,B3>50),0.12,0.10)
  • Commission $ B6 =B4*B5

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.

  • Approach When to use on FMVA Failure mode
  • Single IF Two-way flag (revolver on/off, mid-year vs year-end) Over-nested when a third branch appears

Nested IF Works in every Excel 2016+ file; no extra function to remember Hard to read; easy to mismatch parentheses

IFS Excel 2016+; three or more mutually exclusive buckets No catch-all unless you add TRUE; tests must be in order

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

Error Typical cause in a model First fix

#DIV/0! Division by zero or a blank denominator: =B12/C12 when C12 is 0 Guard with IF(C12=0,0,B12/C12) or fix the driver

#REF! Formula points at a deleted cell, sheet, or a cut-paste that broke the link Undo; restore the sheet; do not IFERROR it away

#VALUE! Wrong type: "12%" * 1000000 if 12% is text; or a space in a number Coerce with VALUE, or clean the input

#N/A Lookup found no match (VLOOKUP/XLOOKUP/MATCH) Check the key; use IFNA if a miss is expected

#NAME? Misspelled function or named range: =IFERRORR(...) or =WACC when WACC was never defined Fix the spelling; do not wrap in another IFERROR first

  • #NULL! Space where a comma or colon belongs in a range Rare; almost always a syntax typo
  • #NUM! Invalid numeric (IRR with no sign change, too-large number) Check cash-flow signs

#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
  • Case B1 value Growth used
  • Downside 1 2% in C5
  • Base 2 6% in C6
  • Upside 3 10% in C7
  • =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?.

Symptom Meaning Response

Status bar: Circular References; model of revolver + interest Intentional loop Enable iteration; check the plug still ties

Status bar: Circular References; you did not mean to loop Accidental (cell points at itself) Find the cell; break the link

  • #DIV/0!, #VALUE!, #N/A, #NAME?, #REF! Real formula error Fix the cause; do not enable iteration
  • Values oscillate each recalc Unstable loop or too-loose iteration Tighten maximum change; inspect the plug
  • 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.

Loading diagram... Trap the error you can name; do not IFERROR a broken model to zero