Behind every loan decision, savings plan, and investment pitch is a spreadsheet running the same dozen functions. Excel’s financial functions answer the money questions — what’s this worth today, what will it grow to, what’s the monthly payment, is the return good enough — and once you know the handful that matter, you can model most of personal and small-business finance in a few cells. The functions are the easy part; the trap that breaks everyone’s first formula is the sign convention, which gets its own section below. Start with what each one answers.
PV and FV: money across time
The foundation of finance is that a dollar today isn’t a dollar next year. PV (present value) and FV (future value) translate between the two at a given interest rate.
=FV(rate, nper, pmt, pv) 'what a savings plan grows to
=PV(rate, nper, pmt, fv) 'what a future sum is worth todayFV answers “if I save $200 a month at 5% for ten years, what will I have?” PV runs it backward: “what’s a $50,000 payout in five years worth to me now?” Both take a rate per period, a number of periods, and a payment — the building blocks every other function here shares. Microsoft’s PV function reference documents each argument, and once PV and FV click, the rest of the family is variations on the same inputs.
PMT: the payment on a loan or plan
PMT is the most-used financial function, because everyone has a loan. It returns the fixed periodic payment given the rate, the number of periods, and the amount borrowed.
=PMT(rate/12, years*12, loan_amount)For a $20,000 car loan at 6% annual over 5 years, that’s =PMT(0.06/12, 5*12, 20000) — and the result is a monthly payment. Notice the /12 and *12: this is the single most common mistake in financial formulas. The rate and the period count must match the payment frequency, so for monthly payments you divide the annual rate by 12 and multiply the years by 12. Use the annual rate with monthly periods and the answer is wildly wrong, in a way that looks plausible enough to miss.
NPV and IRR: judging an investment
When money comes in and goes out over several years, NPV and IRR tell you whether the whole thing is worth doing. They work on a series of cash flows rather than a single payment.
=NPV(rate, year1:year5) + initial_investment
=IRR(cashflow_range)NPV gives the net value in today’s dollars — positive means the investment creates value at your chosen discount rate. IRR reports the implied annual return as a percentage, which makes it easy to compare projects. One catch with NPV: it assumes the first cash flow is one period in the future, so the initial outlay at time zero is added outside the function (and entered as a negative). Miss that and your NPV is off by one period’s discounting. Read the two together — NPV for dollars of value, IRR for the rate — rather than relying on either alone.
RATE and NPER: solve for the unknown
The same relationship can be rearranged to find whichever piece you’re missing. RATE solves for the interest rate; NPER solves for how many periods it takes.
=RATE(nper, pmt, pv) 'what interest rate does this deal imply?
=NPER(rate, pmt, pv) 'how many payments until it's paid off?RATE is how you reverse-engineer the true cost of a “no-interest” financing offer, and NPER answers “if I pay $300 a month, how long until this is gone?” Together with PV, FV, and PMT, these five functions describe the same equation from every angle — give Excel any four of the variables and it solves for the fifth. That’s the whole mental model: one relationship, solved for whatever you don’t know.
IPMT and PPMT: split a payment
A loan payment is part interest, part principal, and the mix shifts every month. IPMT and PPMT break a single payment into those two pieces for any period you choose.
=IPMT(rate/12, period, nper, pv) 'interest portion of one payment
=PPMT(rate/12, period, nper, pv) 'principal portion of the same paymentThe period argument is which payment you’re asking about — payment 1, payment 12, payment 60. Early in a loan, IPMT is large and PPMT is small (you’re mostly paying interest); late in the loan it flips. Listed down a column for every period, these two build a full amortization schedule that shows exactly how each payment chips away at the balance — the same breakdown a lender’s statement gives you, computed yourself. It’s the natural next step once PMT gives you the total payment.
The sign convention that breaks the first formula
Get the convention right and the functions behave; get it wrong and you’ll see negative payments where you expected positive, or an IRR that makes no sense. The rule of thumb: from your own perspective, outflows are negative and inflows are positive. A savings deposit is negative (money leaving you now) and the future balance is positive (money coming back). When NPV or IRR looks incorrect, timing and signs are the first two things to verify — they account for nearly every “the formula is broken” report.
From functions to a working model
Before wiring them together, here’s the whole toolkit at a glance — five answers to five money questions:
| Function | Answers |
|---|---|
| PV / FV | What’s money worth now versus later? |
| PMT | What’s the fixed payment on a loan or plan? |
| NPV / IRR | Does this investment create value, and at what return? |
| RATE / NPER | What’s the missing interest rate or number of periods? |
The functions become an analysis when you wire them to real numbers. Put your assumptions — rate, term, amount — in labeled input cells and reference those cells in the formulas, so one change updates the whole model. A simple loan model is three input cells (amount, annual rate, years) and one =PMT(rate/12, years*12, -amount) that reads them; drop in a different rate and the payment updates instantly. That separation of inputs from formulas is what turns a one-off calculation into a tool you can reuse for the next loan without rebuilding anything.
Keep inputs separate from formulas, and a loan or investment model becomes something you can flex: change the rate in one cell and every payment, total, and NPV recalculates. Pair these functions with conditional totals to compare modeled figures against actuals, and surface the results on a live dashboard for anything you track over time. They sit naturally alongside the other essential Excel formulas every analyst leans on. Learn the five core functions, respect the sign convention, keep your rate and periods consistent, and Excel handles the time-value math that underpins almost every financial decision you’ll model — from a car loan to a five-year investment case.