Excel for Financial Analysts: Core Skills and Formulas
Excel skills financial analyst roles test: modelling structure, absolute references, SUMIFS, lookups, growth and CAGR, NPV and IRR, scenarios and error checks.
By Ayoub Farid · · 2 min read
Financial analysts live in Excel. Tests for these roles often include a modelling task under time pressure, so speed and clean structure matter as much as knowing functions.
Structure first
- Inputs, calculations, outputs on separate sheets or clearly marked areas.
- Assumptions in cells, never typed into formulas: growth rate, tax rate, discount rate.
- Consistent formulas across each row so one formula can be filled right.
- Colour code: inputs in blue font is a common convention.
Formulas you'll use daily
| Task | Formula |
|---|---|
| Growth | =B2*(1+$C$1) |
| Year-over-year | =(C2-B2)/B2. See percent change |
| CAGR | =(end/start)^(1/years)-1 |
| Summaries | SUMIFS |
| Lookups | XLOOKUP, INDEX MATCH |
| Margins | =(B2-C2)/B2. See profit margin |
| Present value | =NPV(rate,C2:G2)+B2 |
| Return | =IRR(B2:G2) |
| Loan payment | =PMT(rate/12,years*12,-amount) |
Note: NPV assumes the first cash flow is one period away, so the initial investment (time 0) is added separately.
Mixed references matter most
Projections are filled right (years) and down (line items), so references need the right mix of $ locks. A growth rate in column C used across all years: $C2. A year header used down all rows: D$1. See mixed references.
Scenarios
- A scenario switch cell with CHOOSE or XLOOKUP pulling base/bull/bear assumptions.
- Data tables (What-If Analysis) for sensitivity.
Checks
Balance sheet balances (should equal zero), cash ties out, no hard-coded numbers in formula rows.
Frequently asked questions
What Excel skills do financial analysts need?
Model structure, absolute/mixed references, SUMIFS, lookups, growth and margin formulas, NPV/IRR, scenarios and checks.
What Excel functions are used in finance?
NPV, XNPV, IRR, XIRR, PMT, SUMIFS, XLOOKUP, INDEX/MATCH, EOMONTH and ROUND are common.
How do I prepare for a financial modelling test?
Practise building a simple three-year projection quickly, with inputs separated and formulas consistent.
Why is NPV in Excel different from my calculator?
Excel's NPV discounts the first value by one period. Add time-zero cash flows outside the function.