PMT
=PMT(rate, nper, pv)
Calculate the periodic payment for a loan. Give it the interest rate, number of periods, and loan amount — get the payment. The mortgage/car loan/personal loan workhorse.
PMT Complete Guide →Financial Functions
Everything a finance modeler needs — loan payments, investment returns, present value, bond calculations, and depreciation. The category that made Excel the standard tool of finance.
Excel became the world's financial modeling standard because of these 55 functions. They handle every core finance calculation: what's this loan's monthly payment? What's the return on this investment? What's the NPV of these cash flows? What's a bond worth today? How much depreciation do I take this year? Master this category and you can build any finance model that a professional analyst would.
💡 Time Value of Money — the foundation
Most financial functions revolve around one idea: a dollar today is worth more than a dollar tomorrow. Excel uses five variables to describe any finance scenario — PV (present value), FV (future value), PMT (periodic payment), NPER (number of periods), and RATE (interest rate per period). If you know four, Excel can calculate the fifth.
Understand this once and every financial function makes sense.
The essential four
NPV / XNPV
=NPV(rate, cashflows) + initial
Net Present Value — the value of future cash flows in today's dollars. NPV assumes equal periods; XNPV works with actual dates. Use XNPV for real investment analysis.
NPV / XNPV Guide →IRR / XIRR
=XIRR(cashflows, dates)
Internal Rate of Return — the discount rate that makes NPV zero. The single most important return metric in finance. XIRR handles irregular dates, IRR assumes equal periods.
IRR / XIRR Guide →RATE
=RATE(nper, pmt, pv)
Solve for the interest rate given known payments. Reverse-engineer the rate on a loan you're offered, or find the yield on a bond you're evaluating.
RATE Complete Guide →Browse by sub-group
Time Value of Money
6 functions
The five core variables and how they interconnect. Master these, master finance.
Investment Analysis
8 functions
NPV, IRR, and their date-aware siblings. Plus MIRR for realistic reinvestment assumptions.
Loans & Mortgages
10 functions
Break down payments into principal vs interest for any period. Build full amortization tables.
Depreciation
6 functions
Straight-line, declining balance, sum-of-years, and variable methods for accounting depreciation.
Bonds — Pricing
10 functions
Calculate bond prices, yields, durations. Everything needed for fixed-income analysis.
Bonds — Coupon Dates
7 functions
Coupon period calculations — days until next coupon, days in current period, coupon count.
Treasury & Discount Bills
6 functions
T-bill discount calculations, discount rates, bond-equivalent yields.
Common finance modeling patterns
- Mortgage payment:
=PMT(rate/12, years*12, -loan_amount)— annual rate divided by 12, years times 12 payments, negative because loan is money you owe - Retirement savings goal:
=NPER(return_rate, -monthly_contrib, 0, target_amount)— how many months until you hit your target - Investment NPV:
=XNPV(discount_rate, cashflows, dates)where cashflows column includes the initial negative outflow - Full amortization row:
Interest = IPMT(rate, period_num, nper, -loan)andPrincipal = PPMT(rate, period_num, nper, -loan) - Yield to maturity:
=YIELD(settlement, maturity, rate, price, redemption, frequency)— the bond analyst's daily bread
Frequently asked questions
Why does PMT return a negative number?
Because Excel treats cash flows by direction. Money paid out is negative, money received is positive. To display a positive payment, wrap PMT in a negative: =-PMT(rate, nper, pv).
NPV or XNPV — which should I use?
Use XNPV for real investment analysis. XNPV takes actual dates so it handles irregular timing correctly. NPV assumes cash flows come at the end of equal periods, which almost never matches real-world investments.
Why does my NPV formula give a weird number?
Three common issues: (1) Excel's NPV expects cash flows starting at period 1, not 0 — so add the initial investment outside NPV. (2) The rate is per period, not annual — divide by 12 for monthly. (3) Sign conventions — inflows positive, outflows negative.
IRR returns #NUM! — why?
IRR needs at least one positive and one negative cash flow. If all values have the same sign, no interest rate makes NPV zero, so Excel can't solve it. Also try providing a guess value: =IRR(range, 0.1).
Are Excel's financial functions US-only?
No — but many bond functions have a "basis" argument that specifies the day-count convention. US 30/360 is basis 0, actual/actual is 1, European 30/360 is 4. Match your market's convention.
Financial models, generated in seconds.
Ask the Add-in to build an amortization schedule, a DCF model, or a mortgage comparison — get the formulas and structure instantly.
Get the Add-in →