Excel Formulas For Financial Analysts

This deck is built around the Excel formulas that financial analysts reach for most often, from valuation work like NPV and IRR to lookup patterns such as INDEX/MATCH and XLOOKUP. You'll also find practical questions on date handling, filtering tricks like SUBTOTAL and AGGREGATE, and everyday formatting hacks such as displaying values in thousands without altering the underlying numbers. Whether you're modeling cash flows, pricing a bond, calculating a loan payment, or computing CAGR, the cards cover the formulas and functions that show up in real analytical workflows.

It's well suited to anyone working in finance, accounting, or business analysis who wants to sharpen their spreadsheet skills, as well as students preparing for finance or consulting interviews where Excel proficiency is often tested. If you're newer to financial modeling, the deck offers a focused tour of high-impact functions; if you're more experienced, it's a useful way to fill gaps on the less common but valuable formulas like XNPV, XIRR, and YEARFRAC.

To get the most out of the cards, try to recall the formula syntax and purpose before flipping to the answer, and then practice each one in an actual workbook right after reviewing it. Spacing your study sessions over several days rather than cramming will help these formulas move into long-term memory, and pairing a card with a small task, like building a quick NPV or CAGR example, makes the learning stick far better than passive review alone.

Excel's time-value-of-money toolkit lets an analyst move between lump sums, streams, and rates without leaving the spreadsheet. The workhorse is `=NPV(rate, value1, value2, ...)`, which discounts its first argument by one period, its second by two, and so on. A critical gotcha is that the period-zero outflow is not part of that discount ladder, so an analyst must list it separately — typically as `+ initial_investment` — for the formula to produce a true net present value. By contrast, `=PV(rate, nper, pmt, [fv], [type])` discounts a level annuity or single future sum, taking the annuity payment and number of periods rather than a vector of cash flows. Companion functions `=IRR(cashflows)` and `=XIRR(values, dates)` return the discount rate that drives net present value to zero, with XIRR accepting actual transaction dates rather than assuming equal periods; `=XNPV(rate, values, dates)` provides the same flexibility on the present-value side when cash flows arrive on irregular dates.

Several functions exist for solving a rate or a number of periods when the cash flow pattern is known. `=RATE(nper, pmt, pv, [fv], [type], [guess])` uses Excel's goal-seeking to back out the periodic interest rate of an annuity, while `=NPER(rate, pmt, pv, [fv], [type])` answers the inverse question — how many periods it takes to amortize a loan or reach a savings goal. `=EFFECT(nominal_rate, periods_per_year)` converts a nominal rate compounded n times per year into the effective annual rate that should be used for cross-product comparison. `=MIRR(cashflows, finance_rate, reinvest_rate)` refines IRR by replacing the unrealistic assumption that interim cash flows are reinvested at the IRR itself; instead, the modeler specifies separate borrowing and reinvestment rates that reflect how the firm actually deploys capital. The closed-form annuity present-value factor \(\frac{1 - (1+r)^{-n}}{r}\) is exactly what `=PV(rate, n, -1)` computes in one cell.

For loan and growth modeling, `=PMT(rate/12, n_months, -principal)` returns the periodic payment, where the sign of the principal is flipped so the result is a positive outflow. To break a single payment into its components, `=IPMT` and `=PPMT` produce the interest and principal share respectively for any period, and `=CUMIPMT` and `=CUMPRINC` aggregate those amounts across any window of periods — making it straightforward to populate an interest schedule or compute a cumulative tax shield. Compound growth uses the closed form \(\text{Principal} \cdot (1 + r/n)^{n t}\), but the equivalent `=FV(rate, nper, pmt, pv, type)` is usually faster and easier to audit. CAGR itself reduces to the simple identity \((E/S)^{1/n} - 1\), while an annuity-due pattern (payments at the start of each period) is achieved by passing 1 as the final argument of FV or PMT.

Bond pricing and risk metrics live in a tight cluster of fixed-income functions. `=PRICE(settlement, maturity, coupon, yld, redemption, frequency, basis)` returns the clean price given the current market yield, while `=YIELD(settlement, maturity, rate, pr, redemption, frequency, basis)` inverts that calculation to retrieve yield-to-maturity from a quoted clean price. Accrued interest between coupon dates is computed with `=ACCRINT(issue, first_interest, settlement, rate, par, frequency, basis)` — necessary for settling an ex-coupon trade or stitching coupon dates into a swap schedule. For short-dated US Treasury bills, which are quoted on a discount basis rather than a yield basis, `=TBILLPRICE(settlement, maturity, discount)` and `=TBILLYIELD(settlement, maturity, pr)` provide the matching pair. When only cash flows and price are known and the bond looks annuity-like, `=RATE(N, Coupon, -Price)` recovers an approximate yield-to-maturity by treating the coupons as level annuity payments.

Interest-rate sensitivity has three standard building blocks. `=DURATION(settlement, maturity, coupon, yld, frequency, basis)` returns the Macaulay duration in years — the weighted average time at which the bond's cash flows are received. `=MDURATION(...)` divides Macaulay duration by \((1 + y/f)\), converting it into the modified duration that actually drives price change for a small parallel shift. `=CONVEXITY(settlement, maturity, coupon, yld, frequency, basis)` provides the second-order correction that improves the duration approximation for larger moves. A practical sensitivity measure for traders is DV01 (or PV01), which can be approximated in Excel as the price change from a one-basis-point bump in yield: re-price the bond with `yld + 0.0001`, take the difference, and divide by 10,000 to express it as a dollar change per \$1\) of par.

Equity cost of capital derives from a few canonical formulas. CAPM expresses the required return as \(R_e = R_f + \beta (R_m - R_f)\), where the beta itself can be estimated from a returns series as `=SLOPE(stock_returns, market_returns)`. The Fama–French three-factor extension adds a size premium \(s \cdot SMB\) and a value premium \(v \cdot HML\), making the model \(R_e = R_f + \beta \cdot Mkt + s \cdot SMB + v \cdot HML\). For dividend-paying equities, the Gordon growth model collapses the discount to \(R_e = D_1/P_0 + g\). The firm-wide WACC is the weighted average \(WACC = E/V \cdot R_e + D/V \cdot R_d \cdot (1 - t)\), where \(t\) is the marginal tax rate that shields interest expense.

A discounted cash flow model is a five-step assembly: forecast unlevered cash flows, choose a discount rate, compute the terminal value, discount the entire stream back to today, and bridge from enterprise value to per-share equity. Free cash flow to the firm is built as \(FCFF = EBIT \cdot (1 - t) + D\&A - \Delta WC - CapEx\); the equity version, FCFE, swaps net income for \(EBIT \cdot (1-t)\) and adds net borrowing. A weighted-average basis (for portfolio returns, blended COGS, or index membership weights) collapses to `=SUMPRODUCT(values, weights)/SUM(weights)`, and the same SUMPRODUCT pattern serves as a portable pre-SUMIFS conditional sum: `=SUMPRODUCT((region="EMEA")*(value))`.

To find an implied terminal value, anchor on \(TV = FCF_{n+1}/(WACC - g)\) from the Gordon growth model or \(TV = EBITDA_n \cdot \text{ExitMultiple}\) from a market-multiple approach, then discount the running stream and the terminal value together. Two refinements tend to matter in practice. First, a mid-year convention treats each period's cash flow as arriving at the midpoint rather than the end, dividing each discount exponent by an extra half-year; an XNPV using June 30 dates captures the same effect without rewriting the NPV formula. Second, the bridge from enterprise value to equity subtracts net debt and preferred stock before dividing by diluted shares: \(\text{Price} = (EV - ND - Pref)/\text{Shares}\). Net debt is computed as total interest-bearing debt minus cash and equivalents, deliberately removing operating cash to avoid inflating the equity claim. The sanity-check multiple for a comparable company is simply \(EV / LTM\_{\text{EBITDA}}\), which should be broadly consistent with the multiple used in the exit-multiple terminal value.

Because most DCF outputs depend nonlinearly on WACC, a sensitivity chart is often more useful than a single point estimate. Building one is mechanical: lay out WACC values from 7% to 12% in column A, place `=NPV(A2, FCFs) + InitialOutflow` in column B, and insert a line chart of B against A. The chart's slope reveals how brittle the value is to the cost-of-capital assumption. Solver can then be asked to find the WACC at which NPV equals the deal premium, or to back out the EBITDA growth path that justifies a given entry multiple. The implicit shape of the curve — steep at first, then flattening as compounding dominates — is itself a useful sanity check against a comparable transaction multiple or a deal sponsor's underwriting threshold.

A disciplined financial model expresses the income statement, balance sheet, and cash flow statement as a single waterfall of accounts. Reading top-down, the canonical line items are Revenue, then COGS for Gross Profit, then operating expenses for EBITDA, plus depreciation and amortization for EBIT, less interest, less taxes, for Net Income. From there, free cash flow adds back D&A and subtracts changes in working capital and capital expenditure. Each period's debt schedule follows a fixed rhythm: beginning balance → interest accrual (rate × average balance) → mandatory and optional repayments → ending balance. Ratios derived from this skeleton fall into three families: liquidity (`=CurrentAssets / CurrentLiabilities` for the current ratio, `=(CurrentAssets − Inventory) / CurrentLiabilities` for the quick ratio), leverage (`=TotalDebt / TotalEquity` and `=EBIT / InterestExpense` for coverage), and operating efficiency (`=AccountsReceivable / (Revenue / 365)` for DSO, `=Inventory / (COGS / 365)` for DIO, and the cash conversion cycle `=DSO + DIO − DPO`).

The DuPont identity decomposes return on equity into three multiplicative drivers — net margin, asset turnover, and financial leverage: \(ROE = (NI/Sales) \cdot (Sales/Assets) \cdot (Assets/Equity)\). It is one of the cleanest tools for diagnosing whether a deterioration in ROE is coming from pricing power, asset productivity, or capital structure. Operating leverage likewise expresses how sensitive profit is to sales — `=ContributionMargin / OperatingIncome` yields the percent change in EBIT for a one-percent change in sales. Break-even volume reduces to `=FixedCosts / (Price − VariableCost)`, useful for capacity or product-pricing decisions. When stepping from EBIT to EBITDA, the bridge is simply \(EBITDA = EBIT + D\&A\), with awareness that non-cash impairments are sometimes lumped into D&A and should be normalized out. For asset-side schedules the depreciation family includes `=SLN` (straight-line), `=DB` (declining balance), `=DDB` (double-declining), `=SYD` (sum-of-years'-digits), and `=VDB(cost, salvage, life, start, end, factor, no_switch)` for partial-period depreciation with automatic switchover to straight-line.

Best-practice layout and tooling separate the model from its inputs. A common pattern is a three-section sheet structure: yellow-filled inputs at the top, black-font formulas in the model, and blue-font cross-sheet references in the output summary. Structured references such as `Table1[Column]` auto-adjust when rows are inserted, sparing the analyst from rewriting ranges every refresh. `=FORMULATEXT(A1)` documents any cell by returning its formula as a string, while `=ISFORMULA(A1)` flags whether a cell is calculated versus hardcoded; conditional formatting keyed off ISFORMULA gives an at-a-glance audit. Trace Precedents and Trace Dependents (Formulas → arrows) draw relationship lines that show what feeds into a formula and what depends on it; the Evaluate Formula tool steps through nested calculations one operator at a time and is indispensable for debugging NPV/IRR chains. Circular references — most often from interest expense referencing average debt that depends on cash flow that depends on interest — are resolved by enabling iterative calculation under File → Options → Formulas, with explicit seed values to anchor convergence. For very large workbooks, switching to manual calculation mode (Formulas → Calculation Options → Manual) keeps recalc time manageable; pressing F9 just before producing a deliverable refreshes the final values. Conditional formatting on formulas (e.g. `=\(B2="Critical"` applied to `\)A2:$Z2` for row highlighting or `=B2/A2-1>0.1` for growth cells) and sheet protection with input cells unlocked complete the hygiene stack.

The lookup family has been redesigned around XLOOKUP, but understanding the legacy functions remains important. `=VLOOKUP(value, table, col, FALSE)` looks up by the leftmost column of a range and returns a column to its right — it cannot look left. `=XLOOKUP(lookup, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])` removes those restrictions: it can look in any direction, defaults to exact match, and accepts a built-in "not found" fallback; passing `match_mode = 2` enables wildcard matching (`*`, `?`). For complex data with row and column orientation, the `=INDEX(returnRange, MATCH(lookupValue, lookupRange, 0))` pattern is more robust than VLOOKUP, and INDEX/MATCH scales better than OFFSET — which is volatile and forces recalculation on every change. Multi-criteria lookups in dynamic-array Excel collapse to `=XLOOKUP(1, (A:A=x)*(B:B=y), C:C)`; the boolean product acts as an AND across the criteria arrays. To fetch only the last non-blank value in a column, the classic trick `=LOOKUP(2, 1/(A:A<>""), A:A)` works without array-entering.

Modern Excel replaces Ctrl-Shift-Enter array formulas with dynamic spilling. `=SORT(UNIQUE(A1:A100))` produces a clean sorted list in one motion, and `=FILTER(array, include, [if_empty])`, `=SORT(array, [sort_index], [order])`, and `=SORTBY(array, by_array, [order])` round out the toolkit. `=VSTACK(range1, range2)` and `=HSTACK(range1, range2)` stack ranges vertically or horizontally — useful for stitching monthly extracts into one master table. `=LET(x, A1*2, y, x+5, y*3)` names intermediate results so the same sub-expression isn't recomputed; `=LAMBDA(x, x*2)(5)` defines a reusable function inline, and named LAMBDAs in Name Manager replace the old VBA custom-function workflow. When a spill is blocked, `#SPILL!` appears; clearing the blocking cells or wrapping with `@` for implicit intersection resolves the conflict. To reference the entire spill range of another cell, use the spill range operator `=A2#`, which is cleaner and far cheaper than OFFSET.

Several smaller utilities round out the lookup toolkit. `=IFS(cond1, val1, cond2, val2, TRUE, "default")` reads better than deeply nested IFs, and `=SWITCH(value, 1, "Low", 2, "Med", 3, "High", "Other")` is the right tool when one expression is compared to a list of candidates. References are anchored with `$A\(1` (or selectively with `\)A1` or `A$1`), cycled by pressing F4 while the cursor is in the reference. For ad-hoc multi-criteria lookups without SUMIFS, the database functions `=DGET(database, field, criteria)` and `=DSUM(database, field, criteria_block)` work with a header-row criteria range. `=IFNA(...)` traps only `#N/A` so genuine bugs surface, whereas `=IFERROR(...)` catches everything and can mask real mistakes — pick the narrower trap when possible. To leap to a specific cell from any sheet, `=HYPERLINK("#'P&L'!A1", "Open P&L")` keeps a navigation menu at the top of the model. Volatile functions (`OFFSET`, `INDIRECT`, `NOW`, `TODAY`, `RAND`, `RANDBETWEEN`) recalc on every change and slow large workbooks; prefer `INDEX`-based dynamic ranges wherever feasible.

Excel's What-If tools translate an analyst's question into the structure the engine can solve. Goal Seek sets a single input cell so that a single output cell hits a target value — for example, finding the operating margin that makes NPV equal to zero. Data Tables generalize this to one or two varying inputs. A one-variable Data Table lays the inputs down a column and asks Excel to recompute the output cell for each; a two-variable Data Table varies one input along the top row and another down the left column, producing a full grid of results for visualization. Scenario Manager is similar but higher-level: it saves named sets of input values across multiple cells and lets the analyst switch between cases to compare outcomes — useful for "Base," "Upside," and "Downside" views of the same model. `=CHOOSE(scenario_index, case1, case2, case3)` performs the in-formula equivalent, picking the nth argument when an integer flag changes.

Solver extends the same idea to constrained optimization. Loaded as an add-in under File → Options → Add-ins, Solver adjusts changing cells to maximize, minimize, or set a target on an objective cell, subject to inequality, equality, and integer constraints. For smooth nonlinear models (calibrating a yield curve, fitting Black–Scholes implied vol to a price), GRG Nonlinear is the right engine; for resource-allocation problems with linear constraints (portfolio rebalancing, mix-shift optimization, blend planning), LP Simplex offers exact solutions and a tableau for diagnostics.

When Goal Seek and Solver both fall short, Monte Carlo simulation samples from input distributions and re-runs the model many times to build an empirical distribution of outcomes. A column of `=NORM.INV(RAND(), mean, stdev)` next to one output cell, iterated across N rows via a Data Table, is the lightest-weight version that doesn't require VBA — Excel repopulates `RAND()` per iteration. For valuation work, the most common sensitivity chart pairs discount rate with NPV: a column of WACC values from 7% to 12% alongside `=NPV(A2, FCFs) + InitialOutflow`, plotted as a line. The chart's slope reveals how brittle the value is to the cost-of-capital assumption. Across all of these tools, manual calculation mode (toggled under Formulas → Calculation Options → Manual) keeps large sensitivity grids from triggering thousands of unwanted recalculations; pressing F9 just before producing a deliverable refreshes the final values.

When raw data is messy or comes from multiple systems, Power Query is the right first stop. Power Query connects to a CSV, SQL database, or API, applies a sequence of transform steps (column renames, type changes, merges, unpivots), and refreshes the result on demand — replacing manual copy-paste cleanup. Power Pivot complements it on the modeling side: Power Pivot builds a relational star schema and lets the analyst write DAX measures against it. The role split is "Power Query shapes and loads; Power Pivot models and measures." Built-in text companions are also part of the shaping toolkit: `=CONCAT(range)` joins cells with no separator, while `=TEXTJOIN(",", TRUE, range)` lets the analyst pick the separator and skip empties; a conditional variant spills only matching values: `=TEXTJOIN(", ", TRUE, IF(A2:A100="Active", B2:B100, ""))`. Modern Excel adds `=TEXTSPLIT(text, delim)` for the inverse operation, and `=TEXT(A1, "yyyy-mm-dd")` for embedding formatted dates inside a TEXTJOIN or filename.

Text cleanup leans on a small set of dedicated functions: `=TRIM` removes extra spaces, `=CLEAN` strips non-printable characters, `=SUBSTITUTE(A1, "$", "")` replaces specific characters, and `=NUMBERVALUE("$1,234.56")` parses locale-formatted currency strings to numbers. Substring extraction uses `=LEFT`, `=MID`, and `=RIGHT`, often combined with `=FIND` to locate a delimiter and `=LEN` for total length. Text dates become real dates via `=DATEVALUE("2025-12-31")` or, for fixed-width strings, the manual assembly `=DATE(RIGHT(A1,4), MID(A1,6,2), LEFT(A1,2))`; once arithmetically manipulated, a serial number can be returned to a display string by wrapping it in `=TEXT(value, "yyyy-mm-dd")` or by applying a date number format to the cell. For an analyst's calendar, `=EOMONTH(start_date, n)` returns month-end, `=NETWORKDAYS(start, end, [holidays])` counts business days, `=WORKDAY(start, n, [holidays])` returns a due date skipping weekends, `=YEARFRAC(start, end, basis)` produces the fractional-year day-count familiar from swaps and bonds, and `=DATEDIF(DOB, TODAY(), "Y")` quietly computes age in years (or "YM"/"MD" for finer units). A fiscal-quarter conversion rolls month values through `(MONTH(date) - FiscalStart + 12) MOD 12 / 3, 0`. `=SUBTOTAL(9, range)` and the more flexible `=AGGREGATE(9, 5, range)` sum only visible cells after filtering, and custom number formats like `#,##0.0,,"M"` re-display a value in millions without altering the underlying number.

Pivot Tables summarize data without formulas but expose many of the analyst's most-needed operations in clicks. Right-clicking a date field and choosing Group lets the analyst collapse dates into months, quarters, or years without a date table; Value Filters → Top 10 returns the largest contributors; Value Field Settings exposes Distinct Count for unique-customer counts (`DISTINCTCOUNT(Orders[CustomerId])` in DAX), Running Total In for cumulative revenue, and Show Values As → % Difference From for prior-period comparisons. Slicers (Insert → Slicer) bind to a field and filter every connected pivot and chart on a dashboard. For pulling a single value into a downstream formula, `=GETPIVOTDATA(...)` references a pivot cell by dimension rather than by cell address, so the formula survives layout changes at the cost of verbosity.

DAX extends Excel into a relational engine. Filter context — set by slicers, page filters, and `CALCULATE` filters — determines which rows a measure aggregates; row context — created by calculated columns and iterators like `SUMX` and `AVERAGEX` — runs an expression per row. `CALCULATE([Sales], 'Date'[Year] = 2025)` is the foundation of nearly every advanced measure; chained with `ALL('Date')` it returns a "% of grand total," and the sameperiod-last-year comparison expands into a YoY% measure like `YoY% := DIVIDE([Sales], CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))) − 1`. Time intelligence ships as a family: `TOTALYTD`, `TOTALQTD`, `TOTALMTD` for running totals; `DATEADD('Date'[Date], -1, MONTH)` for the same period one month earlier; `PARALLELPERIOD(...,-1,YEAR)` for the full prior year; and a manual `CALCULATE([NetFlow], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date])))` for a cumulative balance. To activate a non-default relationship between fact and dimension (a common need for "order date" versus "ship date"), `CALCULATE` paired with `USERELATIONSHIP(...)` chooses which side of the relationship to honor; `DIVIDE(num, den, alt)` is preferred over `/` to suppress divide-by-zero artifacts. Standard risk measures crystallize from the same primitives — Sharpe as `(AVERAGE − Rf) / STDEV.S · √periods` (252 for daily, 12 for monthly), Sortino the same but with `STDEV.S(IF(returns