Excel Formulas for Financial Planning: The Complete Reference
Marcus Smolarek
Gründer von finban
Zuletzt aktualisiert
All the Excel formulas you need for your financial planning — sorted by use case, with concrete copy-paste examples. From basic formulas to advanced financial functions.
Basics
| Formula | Function | Example |
|---|---|---|
=SUM(B2:B13) | Sum of a range | Annual total of all revenue |
=IF(B14<0,"Warning!","OK") | Conditional output | Alert on negative balance |
=SUMIF(A2:A100,"Marketing",B2:B100) | Conditional sum | Total of all marketing expenses |
=SUMIFS(C2:C100,A2:A100,"Salary",B2:B100,"Jan") | Multi-criteria sum | Salaries in January |
=ROUND(B2,2) | Round to 2 decimals | Round amounts to cents |
=MAX(B2:B13) | Highest value | Best revenue month |
=MIN(B2:B13) | Lowest value | Weakest month |
Tips for Basic Formulas
- SUMIFS over SUMIF: SUMIFS allows multiple criteria and is the more modern variant
- Absolute references: Use
$signs (e.g.,$C$1) for cells that shouldn't shift when copying - Named ranges: Assign names to frequently used ranges (Formulas → Define Name) — makes formulas more readable
Cash Flow & Liquidity
| Formula | Function | Example |
|---|---|---|
=B1+B2-B3 | Closing balance | Opening balance + Revenue - Expenses |
=AVERAGE(B2:B7) | Average | Average monthly expenses (burn rate) |
=B1/AVERAGE(C2:C7) | Runway | Cash reserves / avg. monthly burn rate |
=B14*0.19 | VAT calculation | 19% on net revenue |
=B14*0.19/4 | Quarterly VAT | VAT prepayment per quarter |
=SUMIF(D2:D100,"open",E2:E100) | Open receivables | Sum of all unpaid invoices |
=DAYS(TODAY(),B2) | Payment delay | Days since due date |
Liquidity Planning: The Core Formula
The most important formula in any liquidity plan:
Closing Balance = Opening Balance + Σ Inflows − Σ Outflows
In Excel:
=B1 + SUM(B5:B15) - SUM(B18:B30)
Where:
- B1 = Opening balance (= closing balance of previous month)
- B5:B15 = all revenue categories
- B18:B30 = all expense categories
Tip: Use conditional formatting to automatically highlight closing balances < 0 in red.
Forecasting & Planning
| Formula | Function | Example |
|---|---|---|
=B2*(1+$C$1) | Apply growth | Revenue × (1 + growth rate) |
=TREND(B2:B13,A2:A13,A14:A25) | Trend forecast | Project revenue trend |
=XNPV(0.1,B2:B13,A2:A13) | Net present value | Investment valuation |
=IRR(B1:B13) | Internal rate of return | Return on investment |
=XIRR(B2:B13,A2:A13) | Modified IRR | More accurate IRR with irregular dates |
=FV(0.05/12,36,-500) | Future value | Savings plan: €500/month at 5% p.a. after 3 years |
=PV(0.08,5,,100000) | Present value | What are €100k in 5 years worth today? |
Scenario Planning with Data Tables
Excel data tables (What-If Analysis) let you systematically vary a variable:
- Define input cell: e.g., revenue growth (cell B2)
- Define result cell: e.g., closing balance December
- Create variants: Column with values (0%, 5%, 10%, 15%, 20%)
- Create data table: Data → What-If Analysis → Data Table
- Result: Automatic calculation of closing balance for each growth rate
For two variables simultaneously (e.g., growth × payment terms), use the 2D data table.
Analysis & Reporting
| Formula | Function | Example |
|---|---|---|
=VLOOKUP(A2,Categories!A:B,2,FALSE) | Category lookup | Assign transaction to a category |
=(B2-C2)/C2 | Variance in % | Actual vs. plan comparison |
=RANK(B2,B$2:B$13) | Ranking | Sort months by cash flow |
=COUNTIF(B2:B13,"<0") | Count negative months | How many months with negative cash flow? |
=XLOOKUP(A2,Accounts!A:A,Accounts!B:B,"Unknown") | Modern lookup | Look up account description (Excel 365) |
=STDEV(B2:B13) | Standard deviation | Volatility of monthly cash flows |
Pivot Tables for Financial Analysis
For deeper analysis, pivot tables are indispensable:
- Prepare data: Each transaction as a row (Date, Amount, Category, Type)
- Insert pivot table: Insert → PivotTable
- Assign fields: Rows: Category, Columns: Month, Values: Sum of Amount
- Set filters: By type (Revenue/Expense), time period, etc.
Conditional Formatting for Finance Dashboards
Make your Excel model visually informative:
| Rule | Application |
|---|---|
| Cell value < 0 → Red | Immediately spot negative balances |
| Cell value < threshold → Yellow | Warning threshold for low liquidity |
| Variance > 10% → Orange | Highlight actual vs. plan variances |
| Top 3 values → Green | Highlight best months |
| Data bars | Visual size comparisons |
| Color scale (Red–Yellow–Green) | Heatmap for cash flow over months |
Tired of formulas? Finban calculates your cash flow automatically — without a single formula. Real-time bank data, intelligent categorization, and scenarios at the click of a button. Try free now →