1. SUMIFS — Conditional Aggregation
The most-used formula in financial reporting. Sum any column by any combination of conditions:
Sum revenue for a specific region and quarter
=SUMIFS(Revenue, Region, "EMEA", Quarter, "Q3")
2. INDEX MATCH — Flexible Lookups
Preferred over VLOOKUP in financial models because it does not break when columns are inserted or reordered — critical in models that evolve over time.
Pull a rate from a pricing table
=INDEX(RateTable, MATCH(A2, ProductList, 0), MATCH(B2, TierList, 0))
3. IFERROR — Clean Outputs
Financial dashboards should never show #N/A or #DIV/0! to stakeholders. Wrap any formula with IFERROR:
Handle division by zero in margin calculation
=IFERROR(Profit/Revenue, "N/A") ← shows N/A when Revenue=0
=IFERROR(Profit/Revenue, 0) ← shows 0 (use with care — hides real issues)
4. NPV — Net Present Value
NPV formula
=NPV(discount_rate, cash_flow_year1:cash_flow_yearN) + initial_investment
=NPV(10%, B3:B8) + B2 ← B2=initial investment (negative number), B3:B8=yearly cash flows
5. XNPV — NPV for Irregular Cash Flows
Use XNPV when cash flows do not occur at regular annual intervals:
XNPV with actual dates
=XNPV(discount_rate, cash_flows, dates)
=XNPV(10%, B2:B10, A2:A10) ← A2:A10=actual dates, B2:B10=cash flows for those dates
6. IRR and XIRR — Internal Rate of Return
IRR and XIRR
=IRR(B2:B8) ← regular annual cash flows
=XIRR(B2:B10, A2:A10) ← irregular cash flows with actual dates (more accurate)
7. DATEDIF — Period Calculations
Months between dates (loan tenure, contract length)
=DATEDIF(start_date, end_date, "M") ← complete months
=DATEDIF(start_date, end_date, "Y") ← complete years
=NETWORKDAYS(start_date, end_date) ← business days (excludes weekends)
8. OFFSET — Dynamic Ranges
Builds ranges dynamically — used in rolling financial models where the data range expands over time:
Rolling 12-month average
=AVERAGE(OFFSET(B2, COUNT(B:B)-12, 0, 12, 1))
9. TEXT — Formatted Display
Format numbers for reports
=TEXT(A2, "$#,##0.00") ← formats as $1,234.56
=TEXT(A2, "0.0%") ← formats as 42.3%
="Revenue: "&TEXT(SUM(B:B),"$#,##0") ← combine text and formatted number
10. CHOOSE — Scenario Switching
Switch between Base / Bull / Bear scenarios in a financial model with a single input cell:
Three-scenario model (1=Base, 2=Bull, 3=Bear)
=CHOOSE($B$1, BaseRevenue, BullRevenue, BearRevenue)
FAQ
Frequently Asked Questions
What is the most important Excel formula for financial modelling?▾
SUMIFS is used in nearly every financial model for conditional aggregation. INDEX MATCH is critical for flexible lookups that don't break when models evolve. IFERROR is essential for clean professional outputs. Together these three cover most financial reporting needs.
How do I calculate NPV in Excel correctly?▾
Excel's NPV function starts from period 1, not period 0. Add the initial investment separately: =NPV(discount_rate, B3:B8)+B2 where B2 is the initial investment (as a negative number) and B3:B8 are future cash flows. For irregular intervals, use XNPV instead.
What is the difference between IRR and XIRR in Excel?▾
IRR assumes cash flows occur at regular annual intervals. XIRR accepts actual dates for each cash flow, making it more accurate for real-world investments where cash flows do not arrive exactly one year apart. Always use XIRR when you have specific transaction dates.
How do I generate financial modelling formulas automatically?▾
Use ExcelForm at excelform.xyz — describe the calculation in plain English, for example 'calculate rolling 12-month average of column B that updates automatically as new data is added'. ExcelForm generates the correct formula including OFFSET or dynamic array approaches.