Excel Finance Formulas Cheat Sheet
Excel is the backbone of financial analysis. Whether you’re building a DCF model, analyzing financial statements, or pricing a bond, these formulas are the ones you’ll use daily. This cheat sheet covers the most essential functions with exact syntax and practical use cases.
Time Value of Money Functions
| Function | Syntax | Use Case |
|---|---|---|
| NPV | =NPV(rate, value1, value2, …) | Net present value of future cash flows |
| XNPV | =XNPV(rate, values, dates) | NPV with specific dates (irregular cash flows) |
| IRR | =IRR(values, [guess]) | Internal rate of return for periodic cash flows |
| XIRR | =XIRR(values, dates, [guess]) | IRR with specific dates — use this for real deals |
| PV | =PV(rate, nper, pmt, [fv], [type]) | Present value of an annuity or lump sum |
| FV | =FV(rate, nper, pmt, [pv], [type]) | Future value with compound interest |
| PMT | =PMT(rate, nper, pv, [fv], [type]) | Loan payment calculation |
| RATE | =RATE(nper, pmt, pv, [fv], [type]) | Solve for interest rate given other inputs |
| NPER | =NPER(rate, pmt, pv, [fv], [type]) | Number of periods to reach a target |
Valuation & Modeling Functions
| Function | Syntax | Use Case |
|---|---|---|
| WACC (manual) | =E/(E+D)*Ke + D/(E+D)*Kd*(1-T) | Weighted average cost of capital |
| Terminal Value (Gordon Growth) | =FCF*(1+g)/(WACC-g) | Terminal value in a DCF |
| EV/EBITDA (manual) | =Enterprise_Value/EBITDA | EV/EBITDA multiple |
| Unlevered Beta | =Levered_Beta/(1+(1-Tax)*D/E) | Remove capital structure effect from beta |
| Levered Beta | =Unlevered_Beta*(1+(1-Tax)*D/E) | Re-lever beta to target capital structure |
Lookup & Reference Functions
| Function | Syntax | Use Case |
|---|---|---|
| VLOOKUP | =VLOOKUP(value, table, col, FALSE) | Look up data in a table (legacy, still common) |
| XLOOKUP | =XLOOKUP(value, lookup_array, return_array) | Modern replacement — works left/right |
| INDEX/MATCH | =INDEX(array, MATCH(value, range, 0)) | Most flexible lookup, preferred by analysts |
| OFFSET | =OFFSET(ref, rows, cols, [height], [width]) | Dynamic range references for scenarios |
| INDIRECT | =INDIRECT(ref_text) | Convert text string to cell reference |
| CHOOSE | =CHOOSE(index, val1, val2, …) | Scenario toggle switches in models |
Statistical & Data Analysis
| Function | Syntax | Use Case |
|---|---|---|
| AVERAGE | =AVERAGE(range) | Mean value |
| MEDIAN | =MEDIAN(range) | Middle value — better for skewed data |
| STDEV.S | =STDEV.S(range) | Standard deviation (sample) — volatility |
| CORREL | =CORREL(array1, array2) | Correlation between two return series |
| PERCENTILE.INC | =PERCENTILE.INC(array, k) | Percentile ranking |
| CAGR (manual) | =(End/Start)^(1/Years)-1 | Compound annual growth rate |
| SLOPE | =SLOPE(y_range, x_range) | Beta regression (stock vs. market) |
Conditional & Logic Functions
| Function | Syntax | Use Case |
|---|---|---|
| IF | =IF(condition, true_val, false_val) | Conditional logic in models |
| IFERROR | =IFERROR(formula, fallback) | Catch #DIV/0! and other errors |
| AND / OR | =AND(cond1, cond2) / =OR(cond1, cond2) | Multiple conditions |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, …) | Conditional summing across data sets |
| COUNTIFS | =COUNTIFS(range1, criteria1, …) | Count entries matching multiple criteria |
| MIN / MAX | =MIN(range) / =MAX(range) | Floor/ceiling constraints in models |
Date & Formatting Functions
| Function | Syntax | Use Case |
|---|---|---|
| EOMONTH | =EOMONTH(start_date, months) | End of month — key for model periodicity |
| YEARFRAC | =YEARFRAC(start, end, [basis]) | Fraction of year between dates |
| DAYS360 | =DAYS360(start, end) | Day count for bond pricing |
| TEXT | =TEXT(value, format_code) | Format numbers as text strings |
Analyst Tip
Always use XNPV and XIRR instead of NPV and IRR in real models. The non-X versions assume evenly spaced periods and often produce incorrect results. Also, NPV in Excel assumes cash flows start at period 1, not period 0 — you need to add the initial investment separately.Key Takeaways
- XNPV and XIRR are the correct functions for real-world valuation — use them over NPV and IRR.
- INDEX/MATCH is more flexible than VLOOKUP and is the standard in professional financial modeling.
- CHOOSE + scenario toggles let you switch between bull/base/bear cases instantly.
- IFERROR prevents ugly #DIV/0! errors — wrap it around any formula that might divide by zero.
- Learn keyboard shortcuts alongside formulas — speed matters in banking. See the financial modeling shortcuts cheat sheet.
FAQ
What is the difference between NPV and XNPV in Excel?
NPV assumes equal period spacing and starts discounting from period 1 (not period 0). XNPV takes specific dates as inputs and correctly handles irregular cash flow timing. Always use XNPV for real analysis.
How do I calculate WACC in Excel?
There’s no built-in WACC function. Build it manually: =E/(E+D)*Cost_of_Equity + D/(E+D)*Cost_of_Debt*(1-Tax_Rate). Reference your WACC inputs from a dedicated assumptions section.
Should I use VLOOKUP or INDEX/MATCH?
Use INDEX/MATCH. VLOOKUP can only look right, breaks when columns are inserted, and is slower on large datasets. XLOOKUP is the modern alternative if your Excel version supports it.
How do I calculate CAGR in Excel?
Use the formula =(Ending_Value/Beginning_Value)^(1/Number_of_Years)-1. There’s no built-in CAGR function. For example, =(150/100)^(1/5)-1 gives the 5-year compound annual growth rate.
What Excel functions do investment bankers use most?
The core toolkit includes XNPV, XIRR, INDEX/MATCH, SUMIFS, IF/IFERROR, CHOOSE (for scenarios), EOMONTH (for dates), and OFFSET (for dynamic ranges). Master these and you’ll handle 90% of modeling tasks.