15 Excel Formulas Every Finance Professional Needs

Finance professionals live in Excel. Whether you're building budgets, forecasting revenue, or modeling scenarios, these 15 formulas cover 90% of what you'll need. Each one includes a real-world use case and the exact syntax.

Data Aggregation

1. SUMIFS — Sum values with multiple conditions. The backbone of any financial summary.

=SUMIFS(amount_range, date_range, ">="&start_date, date_range, "<="&end_date, dept_range, "Marketing")

2. COUNTIFS — Count entries matching criteria. Track transaction counts, invoice counts, overdue items.

=COUNTIFS(status, "Overdue", amount, ">1000")

3. AVERAGEIFS — Average values with conditions. Calculate average deal size by region, average days to close.

=AVERAGEIFS(deal_size, region, "EMEA", stage, "Closed Won")

Lookup & Reference

4. XLOOKUP — Pull any data point from a reference table. Replaces VLOOKUP with fewer limitations.

=XLOOKUP(employee_id, emp_table[ID], emp_table[Salary], "Not found")

5. INDEX/MATCH — The flexible alternative for complex lookups and older Excel versions.

=INDEX(rates, MATCH(1, (currency=A2)*(date=B2), 0))

Financial Functions

6. NPV — Net Present Value. Essential for investment analysis and capital budgeting.

=NPV(discount_rate, cash_flow_range) + initial_investment

7. IRR — Internal Rate of Return. Compare investment opportunities on equal footing.

=IRR(cash_flows_including_initial)

8. PMT — Calculate loan or lease payments.

=PMT(annual_rate/12, term_months, -loan_amount)

9. XNPV / XIRR — NPV and IRR with irregular cash flow dates. More accurate for real-world timing.

=XNPV(rate, cash_flows, dates)
=XIRR(cash_flows, dates)

Date & Period Functions

10. EOMONTH — Find end of month for any offset. Critical for monthly reporting periods.

=EOMONTH(TODAY(), -1)  // End of last month
=EOMONTH(A2, 3)        // 3 months from date in A2

11. NETWORKDAYS — Business days between dates. Calculate payment terms, project timelines.

=NETWORKDAYS(start_date, end_date, holidays)

Logic & Error Handling

12. IFS — Multiple conditions without nested IFs. Cleaner than chaining IF statements.

=IFS(score>=90, "A", score>=80, "B", score>=70, "C", TRUE, "F")

13. LET — Define variables inside formulas. Reduces repetition and improves readability.

=LET(revenue, SUMIFS(amounts, type, "Revenue"),
     costs, SUMIFS(amounts, type, "Cost"),
     margin, (revenue-costs)/revenue,
     IF(margin>0.3, "Healthy", "Review"))

14. IFERROR — Handle division by zero, missing lookups, and other errors gracefully.

=IFERROR(revenue/units, 0)

Dynamic Arrays

15. FILTER — Extract matching rows dynamically. Replace complex helper columns and manual filtering.

=FILTER(transactions, (month(dates)=1)*(amounts>1000), "No results")

Dynamic arrays (FILTER, SORT, UNIQUE, SEQUENCE) require Excel 365 or 2021+. They spill results automatically — no Ctrl+Shift+Enter needed.