The functions investment banking and private equity analysts actually use, each with plain-English syntax and an example from a real model.
Finds a value in one range and returns the matching value from another. Replaces VLOOKUP and HLOOKUP in modern Excel.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])=XLOOKUP("Ridgeline", Comps!A:A, Comps!F:F, "n/a")Pull a company's EV/EBITDA multiple from a comps table, showing "n/a" if it's missing.MATCH finds the position of a value; INDEX returns whatever sits at that position. Works in every Excel version and looks in any direction.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))=INDEX($D$17:$H$17, MATCH(2028, $D$5:$H$5, 0))Return FY2028 capex from a row of yearly projections.Looks down the first column of a table and returns a value from a column to the right. Still common in older models.
=VLOOKUP(lookup_value, table, col_index_num, FALSE)=VLOOKUP(B6, Debt!A:E, 3, FALSE)Look up the interest rate for a debt tranche. Always use FALSE for an exact match.The sideways version of VLOOKUP: searches across the top row and returns a value from a row below.
=HLOOKUP(lookup_value, table, row_index_num, FALSE)=HLOOKUP("FY2030E", $C$5:$H$40, 31, FALSE)Grab exit-year EBITDA from a timeline that runs across columns.Returns one of several values based on a number. The classic way to build scenario toggles.
=CHOOSE(index_num, value1, value2, ...)=CHOOSE($C$3, 0.04, 0.07, 0.10)Revenue growth switches between downside, base and upside cases when cell C3 is 1, 2 or 3.Returns a cell or range a set number of rows and columns away from a starting point. Powerful but hard to audit, so use it sparingly.
=OFFSET(reference, rows, cols, [height], [width])=SUM(OFFSET(D10, 0, 0, 1, $C$2))Sum the first N years of cash flow, where N is set in cell C2.Adds numbers or ranges. The most-used function in any model, and the shortcut Alt + = writes it for you.
=SUM(number1, [number2], ...)=SUM(D26:D29)Total operating expenses from staff, rent, marketing and overhead lines.Adds the values that meet one or more conditions. Perfect for rolling transaction-level data into a summary.
=SUMIFS(sum_range, criteria_range1, criteria1, ...)=SUMIFS(Data!E:E, Data!B:B, "Northeast", Data!C:C, 2026)Total FY2026 revenue for the Northeast region.Multiplies matching items in two or more ranges, then adds the results. Great for weighted averages.
=SUMPRODUCT(array1, array2, ...)=SUMPRODUCT(C21:C23, D21:D23)/SUM(C21:C23)Weighted average interest rate across three debt tranches.Return the smallest or largest value. In models they act as caps and floors.
=MIN(number1, ...) =MAX(number1, ...)=MIN(D52+D54, MAX(0, D49+D54))A cash sweep: repay as much debt as cash allows, but never more than the balance and never below zero.Mean and middle value. Comps tables show both, because one outlier can drag the average.
=AVERAGE(range) =MEDIAN(range)=MEDIAN(F6:F15)Median EV/EBITDA across ten comparable companies.Counts the cells that meet one or more conditions.
=COUNTIFS(criteria_range1, criteria1, ...)=COUNTIFS(Deals!D:D, ">10", Deals!E:E, "Software")How many software deals closed above 10x EBITDA.Round to a set number of digits. Useful for checks and clean outputs, but don't round inputs mid-model.
=ROUND(number, num_digits)=ROUND(D11-D18, 3)A balance check that ignores tiny floating-point differences.Returns a number without its sign.
=ABS(number)=IF(ABS(D20)>0.01, "CHECK", "OK")Flag a balance sheet that's out by more than a cent, positive or negative.Return the k-th largest or smallest value in a range.
=LARGE(array, k) =SMALL(array, k)=LARGE(F6:F15, 2)The second-highest multiple in a comps set, often used for a valuation range.Returns one value if a condition is true and another if it's false.
=IF(logical_test, value_if_true, value_if_false)=IF(D36>0, D36*$C$18, 0)Only charge tax when pre-tax income is positive.Checks several conditions in order and returns the value for the first one that's true. Cleaner than nested IFs.
=IFS(test1, value1, test2, value2, ...)=IFS(D68>5, "High", D68>3, "Moderate", TRUE, "Low")Label each year's leverage based on net debt / EBITDA.Combine conditions: AND needs all of them true, OR needs at least one.
=AND(test1, test2, ...) =OR(test1, test2, ...)=IF(AND(E$6>=$C21, E$6<$C21+$C$11), $D21/$C$11, 0)Depreciate an asset only during its useful life, the core of a depreciation waterfall.Returns a fallback value when a formula produces an error. Use it deliberately; it can hide real mistakes.
=IFERROR(value, value_if_error)=IFERROR(D21/C21-1, 0)Show 0% growth instead of #DIV/0! in a year with no prior revenue.The annual return that makes the present value of a series of cash flows equal zero. Assumes evenly spaced periods.
=IRR(values, [guess])=IRR(C81:H81)Sponsor IRR from equity invested (negative) in year 0 to exit proceeds (positive) in year 5.IRR for cash flows on specific dates. What funds use when money moves at uneven times.
=XIRR(values, dates, [guess])=XIRR(C30:J30, C29:J29)Returns on a deal with a dividend recap mid-hold and an exit on a specific closing date.Present value of a series of future cash flows. Excel treats the first value as one period away, so add any time-zero cash flow separately.
=NPV(rate, value1, [value2], ...)=NPV($C$8, D20:H20) + C20Value five years of unlevered free cash flow at the WACC, plus today's cash flow.Present value of cash flows on specific dates. More precise than NPV when periods aren't exactly one year apart.
=XNPV(rate, values, dates)=XNPV($C$8, C20:H20, C4:H4)A DCF that discounts from the actual valuation date to each fiscal year-end.The fixed payment that pays off a loan over a set number of periods. Excel returns it as a negative number.
=PMT(rate, nper, pv, [fv], [type])=-PMT(8%/12, 60, 250000)Monthly payment on a $250,000, five-year equipment loan at 8%.Present and future value of a lump sum or a stream of equal payments.
=PV(rate, nper, pmt, [fv]) =FV(rate, nper, pmt, [pv])=-PV(10%, 5, 0, 1500)Today's value of a $1,500 terminal value received in five years at a 10% discount rate.Solves for the interest rate or growth rate per period.
=RATE(nper, pmt, pv, [fv])=RATE(5, 0, -100, 250)The annual return on 2.5x over five years: about 20.1%. Same answer as =2.5^(1/5)-1.A modified IRR that lets you set the rate at which interim cash flows are reinvested, avoiding IRR's optimistic reinvestment assumption.
=MIRR(values, finance_rate, reinvest_rate)=MIRR(C81:H81, 9%, 6%)A more conservative return for a deal with large early dividends.Returns the last day of the month, a set number of months away. The backbone of monthly and quarterly timelines.
=EOMONTH(start_date, months)=EOMONTH(D4, 3)Each column's quarter-end date, three months after the previous one.Returns the same day of the month a set number of months away.
=EDATE(start_date, months)=EDATE($C$3, 60)A term loan's maturity date, five years after closing.The fraction of a year between two dates. Handy for stub periods.
=YEARFRAC(start_date, end_date, [basis])=YEARFRAC(C3, D4)The share of the first year left after a mid-year deal close, used to prorate cash flows.YEAR pulls the year from a date; DATE builds a date from a year, month and day.
=YEAR(date) =DATE(year, month, day)="FY"&YEAR(D4)&"E"Turn a fiscal year-end date into a header like FY2027E.Formats a number as text in a pattern you choose, for labels and titles.
=TEXT(value, format_text)="IRR of "&TEXT(C78, "0.0%")&" at "&TEXT(C7, "0.0x")A dynamic chart title, like "IRR of 26.1% at 7.5x".Join text together. The ampersand (&) is the quickest way.
=text1 & text2 =CONCAT(text1, text2, ...)=B21&" ("&TEXT(C21/C24, "0%")&")"Label a source of funds with its share of the total, like "Senior term loan (46%)".No functions match that search. Try “lookup”, “IRR” or “date”.
The industry convention. Anyone opening your model can see instantly what's an assumption and what's calculated.
Press F4 to cycle through $B$2, B$2 and $B2. Correct anchoring lets one formula fill across every year.
Add a balance check and an IRR cross-check. Interviewers look for them in modeling tests.