Excel Formulas For Financial Analysts

=NPV(rate, value1, value2, ...) + initial_investment — NPV discounts from period 1; include the initial outflow (often negative) separately.

=IRR(cashflows) — first cashflow usually negative (investment), then inflows.

NPV: assumes equal-period intervals.
XNPV(rate, values, dates): handles irregular dates.

IRR: equal periods.
XIRR(values, dates): irregular dates — used when actual cash flow timing varies.

=INDEX(returnRange, MATCH(lookupValue, lookupRange, 0)) — more flexible than VLOOKUP.

VLOOKUP: looks up by leftmost column, returns a column to the right; can't go left.
XLOOKUP: any direction, exact match default, IFNULL built in.

=(EndValue/StartValue)^(1/Years) - 1

=PMT(rate/12, n_months, -principal)

=PRICE(settlement, maturity, coupon_rate, yield, redemption, frequency, [basis])

=ROUNDUP((MONTH(A1) - FiscalStart + 12) MOD 12 / 3, 0) or use INT((MONTH+offset)/3).

=YEARFRAC(start, end, basis) — fractional years between two dates with day-count conventions (30/360, ACT/360, etc.).

=SUBTOTAL(9, range) or =AGGREGATE(9, 5, range)