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.