9 financial Excel functions for analysts in 2026
Every financial analyst needs to compute the same handful of things: present value, internal rate of return, loan payments, net present value of cash flow streams. Excel has dedicated functions for all of them — but the real-world versions (XIRR, XNPV) matter far more than the textbook versions (IRR, NPV), and knowing when to use which separates working analysts from students. This is the practical guide.
💰 What "financial functions" actually means
Excel's financial functions fall into three practical categories: investment analysis (XIRR, XNPV, NPV, IRR) for valuing cash flow streams, loan modeling (PMT, CUMIPMT, RATE) for mortgages and debt schedules, and time value of money (PV, FV) for savings and retirement calculations. This list covers all three with the specific functions professionals actually use — not the outdated textbook versions.
Quick navigation — jump to any function
The sign convention you must understand first
Before diving into functions, one universal rule that trips up every new user: Excel financial functions use signed cash flows. Money paid out is negative. Money received is positive. This holds across every function on this list.
When you borrow $200,000, that's a positive $200,000 you received. Your monthly payment is a negative amount you're paying out. PMT returns a negative number by design — wrap in ABS() if you want the display to show positive.
When you invest $10,000 today for future returns, the -$10,000 is negative (money leaving your pocket) and the future returns are positive. XIRR expects you to enter it this way — if you put both as positive, it returns an error or nonsense value.
Internal rate of return with actual cash flow dates
XIRR is the most important financial function you can learn. It calculates the annualized rate of return for a series of cash flows that happen at specific dates. Unlike IRR (which assumes regular annual intervals), XIRR accepts actual dates — making it accurate for real-world investment analysis where cash flows never follow neat schedules.
Dates = {2020-01-01, 2022-03-15, 2025-09-30}
=XIRR(CashFlows, Dates)
Net present value with actual cash flow dates
XNPV is to NPV what XIRR is to IRR — the real-world version that accepts actual dates instead of assuming equal periods. Discounts each cash flow back to today's value using its specific date, giving you the true present value of an investment.
values = {-500000, 150000, 150000, ..., 150000}
dates = {2026-01-01, 2026-06-30, 2026-12-31, ...}
=XNPV(0.10, values, dates)
NPV > 0 means the project creates value at your required rate. NPV < 0 means it destroys value. NPV = 0 means it exactly meets your hurdle rate (breakeven). This is the single most important decision rule in corporate finance.
Net present value assuming equal periods
NPV is the simpler, older cousin of XNPV. It assumes cash flows happen at regular intervals (typically once per year) and are all evenly spaced. Simpler input but less accurate for real-world scenarios where cash flows have irregular timing.
Excel's NPV function assumes the first cash flow happens at period 1, not period 0. If your investment is at year 0 (today) and returns start at year 1, you must ADD the initial investment separately outside the function. This is why professional models use XNPV or manually handle year-0 separately — the traditional NPV syntax is genuinely confusing.
Internal rate of return with equal periods
IRR calculates the discount rate at which NPV equals zero — effectively the annualized return of an investment assuming equal-period cash flows. Like NPV, it's the textbook version that XIRR replaces for real-world use.
=IRR(values)
When cash flows change sign multiple times (positive then negative then positive), IRR may have multiple mathematical solutions or fail to converge at all. Provide a guess parameter close to expected value to help convergence: =IRR(values, 0.15). For unconventional cash flows, use MIRR (modified IRR) instead which avoids this problem.
Calculate the periodic payment on a loan
PMT returns the fixed periodic payment required to pay off a loan given interest rate, term, and loan amount. The workhorse function for every mortgage calculator, auto loan calculator, and debt schedule ever built.
Periods: 30 × 12 = 360 payments
Amount: $200,000
=PMT(0.06/12, 360, 200000)
Total interest paid between any two periods
CUMIPMT calculates the total interest paid on a loan between two specific periods without building a full amortization schedule. Perfect for questions like "How much interest will I pay in the first 5 years?" or "What's the interest in year 10 only?" Its sibling CUMPRINC does the same for principal.
What future money is worth today
PV answers "How much is a stream of future payments worth today at a given discount rate?" Foundational for bond pricing, annuity valuation, and any scenario where you compare cash flows across time periods.
What today's money grows into
FV answers "How much will my savings be worth in the future given regular contributions and an assumed interest rate?" The workhorse of retirement calculators, savings goal planners, and long-term investment projections.
Periods: 30 × 12 = 360 months
Contribution: $500/month
=FV(0.07/12, 360, -500)
Solve for the interest rate given other loan parameters
RATE is the inverse function to PMT, PV, and FV — given known payment, term, and amounts, RATE returns the implied interest rate. Useful for reverse-engineering effective rates on unusual financial products.
The XIRR vs XNPV vs IRR vs NPV decision
The 4 investment-analysis functions are easy to confuse. Here's the practical decision framework:
| Question you're answering | Cash flow timing | Use this function |
|---|---|---|
| What's the annualized return? | Irregular dates | XIRR |
| What's the annualized return? | Equal annual periods | IRR |
| What's it worth today at rate X? | Irregular dates | XNPV |
| What's it worth today at rate X? | Equal annual periods | NPV |
The rule of thumb: use XIRR and XNPV by default. They handle every case that IRR and NPV handle, plus real-world irregular cash flows. The only reason to use plain IRR or NPV is if you're doing a textbook problem or if you want the simpler input syntax for cash flows you know are perfectly regular.
In private equity, venture capital, real estate, and any investment vehicle where cash flows happen at unpredictable dates (capital calls, distributions, exits), IRR gives the wrong answer because it assumes annual regularity. XIRR is the industry standard for LP reporting. If you're analyzing any real investment, use XIRR unless you have a specific reason not to.
Building a full loan amortization schedule
PMT gives you the total payment. To split each payment into interest vs principal, you need three additional functions:
| Function | What it returns | Typical use |
|---|---|---|
IPMT | Interest portion of a specific payment | Row-by-row interest column |
PPMT | Principal portion of a specific payment | Row-by-row principal column |
CUMIPMT | Cumulative interest between two periods | Summary stats — first 5 years interest, etc. |
CUMPRINC | Cumulative principal between two periods | Summary stats — total principal paid to date |
A complete amortization row typically has: Payment #, Beginning Balance, Payment (PMT), Interest (IPMT), Principal (PPMT), Ending Balance. Copy the formulas down for every payment period and you have a complete schedule that updates when loan terms change.
The 3 financial modeling patterns you'll use forever
Beyond individual functions, three patterns recur across most financial models:
The DCF pattern. Project future cash flows, discount them back with XNPV using an appropriate discount rate (usually WACC or hurdle rate), sum for enterprise value. Add/subtract cash and debt for equity value. Divide by shares outstanding for per-share value. Every equity valuation model follows this pattern.
The LBO returns pattern. Compute purchase price, model 5 years of cash flows, project exit value, use XIRR on the resulting cash flow series to get sponsor returns. Every private equity model computes returns this way.
The debt sizing pattern. Start with target debt service coverage ratio (DSCR), work backward using PMT to determine maximum affordable loan size. Common in real estate acquisition analysis and any leveraged buyout.
Related resources
Master financial modeling
Financial function pages with worked examples, plus pillar guides for the essential Excel skills every analyst needs.
Financial Functions Category → Finance Templates → VLOOKUP Guide →Frequently asked questions
What is the difference between NPV and XNPV in Excel?
NPV assumes cash flows occur at regular intervals (typically once per year) starting at period 1. XNPV allows you to specify the actual date of each cash flow, which is more accurate for real-world scenarios where cash flows happen at irregular times. Professional financial analysts use XNPV for actual investment analysis and reserve NPV for textbook problems or highly simplified models.
Which is better for investment analysis — IRR or XIRR?
XIRR is significantly better for real-world investment analysis. IRR assumes all cash flows happen at regular annual intervals, which is rarely true. XIRR accepts actual dates for each cash flow, producing accurate rates of return even when investments have irregular timing. Private equity, venture capital, and real estate analysts almost exclusively use XIRR because their cash flows never follow neat annual schedules.
How do I calculate a loan payment in Excel?
Use the PMT function: =PMT(rate, nper, pv). Rate is the periodic interest rate (annual rate divided by payments per year), nper is the total number of payments, and pv is the loan amount as a positive number. For a $200,000 mortgage at 6% annual over 30 years with monthly payments, use =PMT(0.06/12, 30*12, 200000) which returns approximately -$1,199 per month.
Why does PMT return a negative number?
Excel financial functions use a cash flow sign convention where money paid out is negative and money received is positive. When you borrow money (positive PV), your payments are cash outflows (negative). To display a positive payment amount, wrap PMT in the ABS function: =ABS(PMT(rate, nper, pv)). This is a common source of confusion for new users but becomes second nature once you understand the sign convention.
What is the discount rate in NPV calculation?
The discount rate represents your required rate of return or cost of capital — the rate at which future cash flows are worth less than present cash flows. Higher discount rates make future cash flows worth less, reducing NPV. Common rates: 8-10% for corporate hurdle rates, 12-15% for growth-stage venture capital, 20%+ for early-stage investments. The choice of discount rate significantly impacts NPV, so document your assumption.
Can Excel calculate mortgage amortization schedules?
Yes, using PMT combined with IPMT (interest portion) and PPMT (principal portion) for each period. Alternatively, CUMIPMT and CUMPRINC calculate cumulative interest or principal over a range of periods. For a full amortization schedule, create a table with one row per payment showing beginning balance, interest paid (IPMT), principal paid (PPMT), and ending balance. Many free amortization templates use this exact pattern.
Do Excel financial functions work in Google Sheets?
Yes, all standard financial functions — XIRR, XNPV, NPV, IRR, PMT, PV, FV, RATE, NPER, IPMT, PPMT, CUMIPMT, CUMPRINC — work identically in Google Sheets. This is one of the areas where Excel and Sheets have completely converged. Complex depreciation functions and bond-pricing functions may have minor implementation differences but core financial functions behave the same on both platforms.
What is the difference between IRR and MIRR?
IRR calculates the internal rate of return assuming intermediate cash flows are reinvested at the IRR itself — an assumption that can produce misleadingly high returns for high-IRR projects. MIRR (Modified IRR) lets you specify separate reinvestment and financing rates, producing more realistic returns. Use MIRR when you have unconventional cash flow patterns (multiple sign changes) or when the IRR reinvestment assumption is clearly unrealistic.
How do I project future cash flows in Excel?
Build a schedule with periods (years/quarters/months) as columns or rows and cash flow line items (revenue, costs, capex, working capital changes) as the opposite dimension. Use growth-rate assumptions and reference cells for key drivers. Sum to net cash flow per period. Then use XNPV to discount to present value or XIRR to compute return. Every DCF model follows this basic pattern.
Bottom line: Use XIRR and XNPV for real-world investment analysis. Use PMT, CUMIPMT for loans. Use PV and FV for time-value calculations. Master these 9 and you cover 95% of financial modeling work.