M&A and IB

Excel for finance

Build and check models like an analyst: anchored references, robust lookups, SUMIFS, NPV (net present value) and IRR (internal rate of return) without the classic off-by-one, and the modelling habits that survive a review at 2 a.m.

6 concepts7 min read25-question test, from a bank of 33Updated

Concept 1 of 61 min

References, rounding, dates and text numbers

The rule
A $ freezes the column or row that follows it when a formula is copied, and F4 cycles A1, $A$1, A$1, $A1 in Excel for Windows.
Worked example

Copying a mixed reference

Given
Formula in E5=B$2*$C4
Copied toF7
  1. Offset: 1 column right, 2 rows down
  2. B$2: column moves, B to C
  3. $C4: row moves, 4 to 6
  4. Result: =C$2*$C6

Copying moves every part not frozen by a $.

The picture
EDATE keeps the day, or the month's last day
  1. 31 January 2027Start date
  2. 28 February 2027Plus 1 month: no 31st, so the last day
  3. 31 March 2027Plus 2 months
Formula
=B$2*$C4 copied 1 column right and 2 rows down becomes =C$2*$C6
=ROUND(x, 0)
=C1-B1 in days
=EDATE(date, months)
Why it holds

ROUND sends halves away from 0. Dates are serial numbers, so subtracting them gives days, and EDATE moves a date by whole months.

Numbers stored as text are ignored by SUM, and IF tests need care at the boundary. Copying moves every unfrozen part by the same offset as the cell.

Rounding halves away from 0 differs from Python's rounding to even, so the same model can differ by a cent between tools. Subtracting 2 dates counts the days between them, not both ends.

EDATE keeps the day of the month, or takes the month's last day when it does not exist; EOMONTH returns month ends.

Imported numbers often arrive as text: they look right and are silently left out of totals until converted with VALUE() or Text to Columns.

A test like A1>0 sends 0 to the 'else' branch, Loss rather than Gain, which is where formula logic usually breaks. =ROUND(-2.5, 0) gives -3 in Excel, where Python's round(-2.5) gives -2. =EDATE of 31 January 2027 plus 1 month gives 28 February 2027, and plus 2 months 31 March 2027.

Watch out
  • Anchoring the row when the column should be fixed.
  • Counting both ends when subtracting dates.
  • Missing a number stored as text in a total.
In the interview
  1. You inherit a model and a total looks too low. Walk me through how you would find the cause.

    A complete answer covers

    • Check whether some inputs are numbers stored as text.
    • Trace precedents to see which cells the total really reads.
    • Look for hard-coded overrides inside formula rows.
    • Rebuild the total another way, for example with SUMPRODUCT, and compare.

Also in our technical interviews, on camera, with a score.

Concept 2 of 61 min

Lookups that do not break

The rule
A VLOOKUP in a model needs FALSE or 0 as its last argument, or it returns approximate matches that are wrong on unsorted data.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Concept 3 of 61 min

SUMIFS, COUNTIFS and SUMPRODUCT

The rule
SUMIFS adds a column under several conditions given as pairs of range and criterion, with the sum range first.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Concept 4 of 61 min

NPV, IRR, CAGR and loan payments

The rule
Excel's NPV() treats its first value as arriving at the end of period 1, so a cash flow today is added outside it.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Concept 5 of 61 min

Modelling habits: circularity, scenarios and checks

The rule
Interest on average debt creates a circular reference, handled with iterative calculation and a circuit breaker.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Concept 6 of 61 min

AdvancedBreaking the circular reference with algebra

The rule
When all cash after interest repays debt, interest on average debt is rate × (2 × opening debt − cash before interest) ÷ (2 − rate).

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Recap

  • $ freezes what follows it: A$1 keeps the row, $A1 the column; F4 cycles through them.
  • Exact-match lookups only: VLOOKUP needs FALSE, and INDEX-MATCH or XLOOKUP survive inserted columns.
  • SUMIFS and COUNTIFS take several conditions in range-criterion pairs; SUMPRODUCT multiplies then adds.
  • NPV() discounts its first value one period: add today's cash flow outside it; XIRR for real dates; several sign changes can give several IRRs.
  • Blue inputs, black formulas, green links; no volatile functions, no IFERROR that hides errors, no plugs.
Now test it

25 questions on Excel for finance

Timed, like the real one, drawn from a bank of 33. Every answer explained.

Test yourself →