A reference of the 30 Excel functions that come up most often in undergraduate finance — time value of money, investment analysis, lookups, statistics, and dates. Each entry shows the syntax, a one-line description, and a working example with a concrete finance result. Filter by typing, or jump to a category below.
Periodic payment for a fixed-rate loan or annuity. Sign convention: a positive PV (money borrowed) returns a negative PMT (money you pay out), and vice versa.
=PMT(0.07/12, 360, -300000)
→ $1,995.91 monthly mortgage on a $300k loan at 7% over 30 years
Future value of an investment given regular contributions and a constant rate.
=FV(0.06/12, 240, -500, 0)
→ $231,020 — saving $500/month for 20 years at 6%
Present value of a stream of future cash flows. The discounting workhorse.
=PV(0.05, 30, 0, -100000)
→ $23,138 — what $100k thirty years from now is worth today at 5%
Periodic interest rate implied by the other variables. Multiply by the number of compounding periods to annualize.
=RATE(360, -1995.91, 300000)*12
→ 7.0% annual — implied rate from a $1,995.91 monthly payment on $300k over 30 years
Number of periods to hit a target balance given a constant payment and rate.
=NPER(0.08/12, -200, 0, 50000)/12
→ 12.3 years — to reach $50k saving $200/month at 8%
Net present value of cash flows that occur at the end of equal-length periods. Important quirk: NPV discounts the first value by one period, so add the initial t=0 outlay separately.
=NPV(0.10, 25000, 30000, 35000, 40000, 45000) - 100000
→ $29,078 — five-year project, $100k initial outlay, discounted at 10%
Internal rate of return for cash flows at equal intervals. First value should be the negative initial outlay.
=IRR({-100000, 25000, 30000, 35000, 40000, 45000})
→ 19.8% — IRR of the same project as the NPV example
NPV using actual dates rather than equal periods — the realistic case for real-world projects with irregular cash flows.
=XNPV(0.08, A2:A10, B2:B10)
→ NPV at 8% using cash flows in column A and the actual dates in column B
IRR using actual dates. In practice, prefer this to plain IRR — real cash flows are rarely on perfect calendar periods.
=XIRR(A2:A10, B2:B10)
→ annualized return given irregular cash flow dates
Modified IRR. Addresses IRR's questionable assumption that interim cash flows are reinvested at the IRR itself.
=MIRR(A2:A10, 0.05, 0.08)
→ MIRR assuming financing at 5% and reinvestment at 8%
The modern, default-exact-match replacement for VLOOKUP, HLOOKUP, and most INDEX/MATCH uses. Use this in new work.
=XLOOKUP("AAPL", A2:A100, F2:F100, "n/a")
→ returns the column F value where column A equals "AAPL", or "n/a" if not found
The pre-XLOOKUP standard. Still common in older workbooks you'll inherit — worth recognizing.
=INDEX(F2:F100, MATCH("AAPL", A2:A100, 0))
→ same result as the XLOOKUP example above
Returns a range offset from a starting cell. Useful for dynamic ranges and rolling windows. Volatile — recalculates on every change.
=AVERAGE(OFFSET(B100, -11, 0, 12, 1))
→ trailing 12-month average ending at B100
Converts a text string into a cell reference. Powerful for dynamic sheet/range references — but volatile and slows large workbooks.
=INDIRECT("'" & A1 & "'!B5")
→ pulls cell B5 from a sheet whose name is the text in A1
The classic conditional.
=IF(B2>10, "above target", "below")
→ returns one of two strings depending on B2
Multiple conditions without nested IFs. Much cleaner than IF(IF(IF(...))) chains.
=IFS(B2<0, "loss", B2<10, "low", B2<25, "mid", TRUE, "high")
→ buckets B2 into one of four categories; final TRUE acts as the fallback
Wraps a formula that might return #DIV/0!, #N/A, or similar, and substitutes a fallback. Essential for clean models.
=IFERROR(A2/B2, 0)
→ returns 0 rather than #DIV/0! if B2 is zero or empty
Sum with one or more conditions. Note the argument order: sum range first, then criteria pairs.
=SUMIFS(D:D, A:A, "Tech", B:B, ">=2024")
→ sum of column D where sector is "Tech" and year is 2024 or later
Count rows that satisfy multiple conditions.
=COUNTIFS(C:C, ">0", D:D, "<0")
→ count of rows where C is positive and D is negative
Average values with one or more conditions.
=AVERAGEIFS(R:R, S:S, "Large Cap", T:T, ">=2020")
→ average return for large-cap stocks from 2020 onward
Sample standard deviation. Use STDEV.P only when your data is the entire population, not a sample.
=STDEV.S(R2:R61)
→ sample standard deviation of 60 monthly returns (a useful risk measure)
Pearson correlation coefficient between two series. Returns a value in [−1, 1].
=CORREL(B2:B61, C2:C61)
→ correlation between two stocks' monthly returns
Sample covariance. The foundation of portfolio variance and the numerator of beta.
=COVARIANCE.S(R2:R61, M2:M61)
→ covariance between stock returns (column R) and market returns (column M)
Slope of the OLS regression line. In finance: this is beta when y = stock excess returns and x = market excess returns.
=SLOPE(B2:B61, C2:C61)
→ beta of the stock in column B against the market in column C
Y-intercept of the OLS regression line. In a CAPM regression, this is Jensen's alpha.
=INTERCEPT(B2:B61, C2:C61)
→ alpha of the stock vs the market (positive = outperformance after adjusting for beta risk)
Full OLS regression results as an array. With stats=TRUE you get standard errors, R², F-statistic, and degrees of freedom. Enter as an array formula or wrap in INDEX to pull individual values.
=LINEST(B2:B61, C2:C61, TRUE, TRUE)
→ array containing beta, alpha, standard errors, R², F-stat, residual SS
Date that is a given number of months after (or before, if negative) the start date.
=EDATE(TODAY(), 12)
→ today's date plus 12 months
Last day of the month that is N months from the start date. Useful for month-end pricing and reporting dates.
=EOMONTH(TODAY(), 0)
→ last day of the current month
Fractional years between two dates. The basis controls day count: 0 = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360. Matters for bonds and accrued interest.
=YEARFRAC("2024-01-15", TODAY(), 1)
→ years held using actual day count
Adds business days to a date (skips weekends; optionally skips a holiday list). Used for settlement dates.
=WORKDAY(TODAY(), 5, holidays)
→ settlement date five business days from today, accounting for the named holiday range