The financial functions are the foundation of every valuation, loan, and savings model. The ten functions below cover 90% of what the senior uses in any finance engagement. The five mistakes are the ones that quietly break a model - catch them on the mid-build review and the model survives the audit.
The ten functions
NPV(rate, value1, value2, ...)
Net present value of a series of cash flows at a constant discount rate. Assumes equal spacing between cash flows.
=NPV(0.10, 100, 200, 300, 400) returns 778.12 (assuming period 1 to period 4).
XNPV(rate, values, dates)
Net present value with actual dates. Use this for any cash flow series that is not evenly spaced.
=XNPV(0.10, {100, 200, 300, 400}, {DATE(2026,1,1), DATE(2026,6,1), DATE(2027,1,1), DATE(2028,1,1)}) returns the NPV with actual day-count.
IRR(values, [guess])
Internal rate of return. The discount rate at which NPV equals zero.
=IRR({-1000, 300, 400, 500, 600}) returns the periodic rate (about 27% per period).
XIRR(values, dates, [guess])
Internal rate of return with actual dates.
=XIRR({-1000, 300, 400, 500, 600}, {DATE(2026,1,1), DATE(2026,12,31), DATE(2027,12,31), DATE(2028,12,31), DATE(2029,12,31)}) returns the annual IRR.
PMT(rate, nper, pv, [fv], [type])
Periodic payment of a loan or annuity. Negative if you are paying; positive if you are receiving.
=PMT(0.05/12, 360, 500000) returns the monthly payment on a 30-year, 5%, ₹5L mortgage.
IPMT(rate, per, nper, pv)
Interest portion of a payment in a specific period.
=IPMT(0.05/12, 1, 360, 500000) returns the interest in the first month.
PPMT(rate, per, nper, pv)
Principal portion of a payment in a specific period.
=PPMT(0.05/12, 1, 360, 500000) returns the principal in the first month.
FV(rate, nper, pmt, [pv], [type])
Future value of a series of payments.
=FV(0.07/12, 12*30, -1000) returns the value of ₹1,000/month for 30 years at 7% annual return.
PV(rate, nper, pmt, [fv], [type])
Present value of a series of payments.
=PV(0.05, 10, -1000) returns the present value of ₹1,000/year for 10 years at 5% discount rate.
RATE(nper, pmt, pv, [fv], [type], [guess])
Periodic interest rate of an annuity.
=RATE(360, -1500, 500000) returns the monthly rate on a 30-year, ₹1,500/month, ₹5L loan.
The five mistakes to avoid
NPV with the initial investment in the wrong period
NPV discounts the first cash flow by one period. If the initial investment is at time 0, it must be added separately, not included in the NPV range. The fix: =initial_investment + NPV(rate, cash_flows_from_period_1). The senior puts the initial investment on the Assumptions tab and references it in the formula.
Using NPV with irregular dates
NPV assumes equal spacing. For irregular dates (capital calls, milestone payments), XNPV is the correct function. The fix: pass the dates as a range to XNPV, and ensure the dates are real Excel dates (not text).
IRR with no sign change in the cash flows
IRR requires at least one positive and one negative value in the series. If all cash flows are positive, there is no rate at which NPV equals zero, and IRR returns #NUM!. The fix: check the cash flow series; if all positive, the model is missing the initial investment.
PMT with no rate argument
PMT requires the periodic rate. If the rate is annual, divide by the number of periods per year. The fix: PMT(annual_rate/12, nper*12, pv) for monthly payments on an annual rate. The senior wraps the rate calculation in a named cell to make the formula auditable.
Mixing up PV and FV in PMT
PV is positive if you are receiving (borrowing), negative if you are paying (lending). FV is the future value, usually 0 for a fully-amortised loan. The fix: be explicit about the sign convention. The senior uses comments in the formula bar to document the sign.
How the senior wires them together
A senior-built valuation model uses IRR, XIRR, NPV, XNPV, and PMT in different places. The pattern: XIRR for the deal IRR (because deal cash flows are irregular); XNPV for the deal NPV at the target IRR; PMT for any debt service schedule; FV for any earnout calculation. The five functions are on the Assumptions tab, named, and referenced from the calculation tabs. The senior documents each one in the comments field on the named range so the next reader understands the sign convention.
