Excel for Finance: Functions Every Financial Analyst Uses

Spreadsheets remain the main tool of financial analysis. A modest set of functions and features covers most of what analysts do: time value of money calculations, investment appraisal, lookups across data, conditional totals and "what if" analysis. This guide explains the essentials using Microsoft Excel’s function names; most other spreadsheet programs use the same or very similar functions.

What you will learn

Use the time value of money functions: PMT, FV, PV, RATE and NPER
Calculate NPV and IRR correctly, including with irregular dates
Look up and summarise data with INDEX/MATCH, XLOOKUP and SUMIFS
Run sensitivity analysis with data tables and Goal Seek

Time value of money functions

Function	Returns	Syntax (main arguments)
PMT	Periodic payment of a loan or annuity	`PMT(rate, nper, pv, [fv], [type])`
FV	Future value	`FV(rate, nper, pmt, [pv], [type])`
PV	Present value	`PV(rate, nper, pmt, [fv], [type])`
RATE	Interest rate per period	`RATE(nper, pmt, pv, [fv], [type])`
NPER	Number of periods	`NPER(rate, pmt, pv, [fv], [type])`

Sign convention: money received is positive, money paid is negative. A loan received (pv positive) produces a negative payment. type = 0 for payments at the end of each period (default), 1 for the beginning.

Worked example: an EMI with PMT

A loan of 1,000,000 at 9% a year, repaid monthly over 20 years (illustrative terms):

=PMT(9%/12, 20*12, 1000000) returns −8,997.26.

The rate is divided by 12 and the term multiplied by 12 because payments are monthly. The result matches the formula in how EMI is calculated and the ARBIMETRA loan payment calculator.

NPV and IRR — and their traps

Excel’s NPV function discounts every value it is given, starting one period from now. It therefore assumes the first cash flow occurs at the end of period 1. The initial investment made today (period 0) must be added separately.

Worked example: the NPV function used correctly

A project costs 100,000 today and returns 40,000, 45,000 and 50,000 at the end of years 1–3; the discount rate is 10% (cells B2:B5 hold −100,000, 40,000, 45,000, 50,000).

Correct: =NPV(10%, B3:B5) + B2 returns 11,119.46.
Wrong: =NPV(10%, B2:B5) discounts the initial investment by one year as well and returns 10,108.60 — an error of about 9%.

=IRR(B2:B5) returns about 15.98%. These match the results in NPV and IRR explained and the NPV and IRR calculators.

For cash flows on irregular dates, use XNPV(rate, values, dates) and XIRR(values, dates). They discount by the actual number of days between dates (on a 365-day basis), which is essential for real investment and loan cash flows that do not arrive at neat annual intervals. Note that XNPV discounts to the date of the first cash flow, so the first value is not discounted.

Lookups and conditional summaries

INDEX/MATCH: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) finds a value in any column and is robust when columns are inserted. The 0 requires an exact match.
XLOOKUP (available in recent versions): =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) — simpler and safer than VLOOKUP.
VLOOKUP with FALSE for exact matches still appears widely, but it breaks when columns are inserted and only searches the first column.
SUMIFS / COUNTIFS / AVERAGEIFS: totals with one or more conditions, for example sales by region and month: =SUMIFS(Sales, Region, "North", Month, D1).

These functions turn transaction-level data into the summaries a model needs, such as revenue by product line.

Useful supporting functions

Function	Use in models
`IF`, `AND`, `OR`	Logic and flags (e.g. whether a loan is still outstanding)
`MIN`, `MAX`	Caps and floors (e.g. tax cannot be negative: `=MAX(0, Profit*Rate)`)
`EOMONTH`, `EDATE`, `YEARFRAC`	Building timelines and day-count fractions
`ROUND`	Presenting results; avoid rounding inside calculations
`IFERROR`	Handling expected errors — use sparingly, as it can hide real mistakes
`SUMPRODUCT`	Weighted averages, such as a WACC from weights and costs

What-if analysis

Data tables (What-If Analysis → Data Table) recalculate an output for a range of one or two inputs — for example NPV for several discount rates and growth rates. This is the standard way to build the sensitivity grids described in scenario and sensitivity analysis.
Goal Seek finds the input value that makes an output equal a target — for example, the selling price at which NPV is zero.
Scenario Manager stores sets of inputs as named scenarios, although many modellers prefer a scenario selector built with lookups, which is more transparent.

Worked example: break-even price with Goal Seek

A project needs 300,000 now and sells 10,000 units a year for 5 years with a variable cost of 30 per unit and fixed costs of 80,000 a year; the discount rate is 10% (illustrative figures).

Setting NPV to 0 by changing the price gives a break-even price of about 45.91. Check: the annual cash flow needed is 300,000 ÷ 3.7908 (the 5-year annuity factor at 10%) ≈ 79,139, and (45.91 − 30) × 10,000 − 80,000 ≈ 79,100.

Good habits

Use named ranges or clearly labelled input cells for key assumptions.
Keep absolute references ($A$1) for inputs referred to from many cells.
Prefer simple, consistent formulas that can be copied across rows — see best practices.
Use conditional formatting to highlight check failures.
For large data sets, repeatable processes or simulations, consider complementing spreadsheets with tools such as Python, which offers libraries for data analysis and numerical finance.

Common mistakes

Including the period-0 investment inside Excel’s NPV function.
Using an annual rate with monthly periods in PMT.
Using approximate-match lookups by accident.
Wrapping everything in IFERROR, hiding genuine problems.
Typing results of a Goal Seek over formulas, breaking the model.

Key terms

PMT — spreadsheet function returning the periodic payment for a loan or annuity.
XNPV / XIRR — NPV and IRR for cash flows on specific dates.
INDEX/MATCH — a flexible lookup combination.
Data table — a what-if tool that recalculates outputs for ranges of inputs.
Goal Seek — a tool that finds the input needed to reach a target output.

Frequently asked questions

Why does Excel’s PMT give a negative number?

Because of the cash flow sign convention: if the loan amount is entered as money received (positive), payments are money paid out (negative).

When should I use XIRR instead of IRR?

Whenever cash flows do not occur at equal intervals, such as investments made and returned on specific dates.

Is VLOOKUP still acceptable?

It works for simple exact-match lookups, but INDEX/MATCH or XLOOKUP are more robust in models that change.

Key takeaways

PMT, FV, PV, RATE and NPER handle most time value of money problems; watch the sign convention and period units.
Excel’s NPV assumes the first value is one period away; add today’s investment separately.
Use XNPV and XIRR for irregular dates.
Lookups, SUMIFS, data tables and Goal Seek power most analysis tasks.