9 Financial Excel Functions for Analysts in 2026 — Investment, Loan & DCF Formulas
Home› 9 Financial Excel Functions for Analysts
💰 Top List · Financial Functions

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.

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.


1
Essential Investment Intermediate
XIRR The Real-World IRR

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.

Arguments
valuesRange of cash flows (first one usually negative — the investment).
datesRange of dates matching each cash flow.
[guess]Optional starting estimate for iteration (default 0.1 = 10%).
=XIRR(CashFlows, Dates)
Worked Example — Venture Investment
Invest $100,000 on Jan 1, 2020. Receive $30,000 dividend on Mar 15, 2022. Sell for $180,000 on Sep 30, 2025.
CashFlows = {-100000, 30000, 180000}
Dates = {2020-01-01, 2022-03-15, 2025-09-30}
=XIRR(CashFlows, Dates)
Annualized return: ≈ 18.4%
Real use case: Private equity fund reporting, venture capital track records, real estate deal analysis, personal investment portfolio returns. Anywhere cash flows happen on irregular dates. XIRR is the industry-standard function for LP performance reporting.
2
Essential Investment Intermediate
XNPV The Real-World NPV

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.

Arguments
rateDiscount rate (annual) — your required rate of return.
valuesRange of cash flows.
datesRange of dates matching each cash flow.
=XNPV(0.10, CashFlows, Dates)
Worked Example — Project Evaluation
Project requires $500K on Jan 1, returns $150K each June and Dec for 4 years. Use 10% discount rate.
rate = 10%
values = {-500000, 150000, 150000, ..., 150000}
dates = {2026-01-01, 2026-06-30, 2026-12-31, ...}
=XNPV(0.10, values, dates)
NPV: ≈ $377,000 (positive = project creates value)
Decision rule

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.

Real use case: Capital budgeting decisions, M&A deal valuation, real estate acquisition analysis, discounted cash flow (DCF) models. XNPV is the professional standard because real cash flows never happen on textbook-perfect annual schedules.
3
Investment Beginner
NPV The Textbook NPV

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.

Arguments
rateDiscount rate per period (annual if annual cash flows).
value1, value2, ...Cash flows starting at period 1 (not period 0!).
=NPV(0.10, CashFlows) + InitialInvestment
The famous NPV gotcha

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.

Worked Example — Annual Project
Invest $100K today. Receive $30K per year for 5 years. Discount rate: 8%.
=NPV(0.08, {30000, 30000, 30000, 30000, 30000}) - 100000
NPV: ≈ $19,782
Real use case: Textbook problems, simplified models where annual cash flows are a reasonable approximation, quick back-of-envelope calculations. For any serious model, use XNPV instead.
4
Investment Intermediate
IRR The Textbook IRR

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.

Arguments
valuesRange of cash flows starting with initial investment (negative).
[guess]Optional starting estimate for the iteration (default 0.1).
=IRR(CashFlowRange)
Worked Example — 5-Year Project
Invest $100K, receive $28K per year for 5 years.
values = {-100000, 28000, 28000, 28000, 28000, 28000}
=IRR(values)
IRR: ≈ 12.4%
IRR can return multiple values or fail

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.

Real use case: Simple project ranking where all cash flows fall at year-end, comparing hurdle rate to project return, education/certification exams. For actual investment analysis, use XIRR.
5
Essential Loans Beginner
PMT The Loan Payment Calculator

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.

Arguments
ratePeriodic interest rate (annual / payments per year).
nperTotal number of payments (years × payments per year).
pvPresent value — the loan amount.
[fv]Optional future value (default 0 — loan fully paid off).
[type]Optional: 0 = payment at end of period (default), 1 = beginning.
=PMT(0.06/12, 30*12, 200000)
Worked Example — 30-Year Mortgage
$200,000 loan at 6% annual interest, 30-year term, monthly payments.
Rate: 0.06/12 = 0.5% per month
Periods: 30 × 12 = 360 payments
Amount: $200,000
=PMT(0.06/12, 360, 200000)
Monthly payment: ≈ -$1,199 (negative = money out)
Real use case: Every mortgage calculator, auto loan calculator, student loan estimator, business loan model. Pair with IPMT and PPMT to build a complete amortization schedule showing interest vs principal for each payment.
6
Loans Advanced
CUMIPMT Cumulative Interest Paid

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.

Arguments
ratePeriodic interest rate.
nperTotal number of payments.
pvLoan amount.
start_periodFirst period to include.
end_periodLast period to include.
type0 = end of period (default), 1 = beginning.
=CUMIPMT(0.06/12, 30*12, 200000, 1, 60, 0)
Worked Example — First 5 Years of Mortgage Interest
Same $200K mortgage. How much interest in the first 5 years (payments 1-60)?
=CUMIPMT(0.06/12, 360, 200000, 1, 60, 0)
Interest in first 5 years: ≈ -$57,600
Real use case: Tax deduction estimates (mortgage interest is deductible in many jurisdictions), refinance breakeven analysis, understanding how much of early payments go to interest vs principal. Critical for anyone deciding whether to prepay a mortgage.
7
Time Value of Money Beginner
PV Present Value

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.

Arguments
rateDiscount rate per period.
nperNumber of periods.
pmtPayment per period (0 if no regular payments).
[fv]Optional lump-sum future value.
[type]0 = payments at end (default), 1 = beginning.
=PV(0.05, 20, 10000)
Worked Example — Annuity Valuation
You'll receive $10,000/year for 20 years. At a 5% discount rate, what's it worth today?
=PV(0.05, 20, 10000)
Present value: ≈ -$124,622
Real use case: Valuing pension/annuity streams, structured settlement analysis, "lump sum vs annuity" lottery decisions, retirement planning "how much do I need today for X per year". Also used to compute bond prices as the PV of future coupon payments plus face value.
8
Time Value of Money Beginner
FV Future Value

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.

Arguments
rateInterest rate per period.
nperNumber of periods.
pmtPayment per period (negative if you're contributing).
[pv]Optional starting balance (negative if already contributed).
[type]0 = payments at end (default), 1 = beginning.
=FV(0.07/12, 30*12, -500)
Worked Example — Retirement Savings
Save $500/month for 30 years earning 7% annual return.
Rate: 0.07/12 = ~0.583% monthly
Periods: 30 × 12 = 360 months
Contribution: $500/month
=FV(0.07/12, 360, -500)
Future value: ≈ $611,000
Real use case: Retirement planning ("if I save $X/month for Y years..."), education savings goals, "how long until I hit my target" calculations, understanding the power of compound interest. Every financial planning conversation involves FV somewhere.
9
Loans Intermediate
RATE The Implied Interest Rate

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.

Arguments
nperNumber of payments.
pmtPayment per period (negative for outflow).
pvPresent value.
[fv]Optional future value (default 0).
[type]0 or 1.
[guess]Optional starting estimate for iteration.
=RATE(60, -450, 25000) * 12
Worked Example — Effective Auto Loan Rate
$25,000 car loan, 60 monthly payments of $450 each. What's the effective interest rate?
=RATE(60, -450, 25000) * 12
Annual interest rate: ≈ 3.36%
Real use case: "Rent-to-own" calculations to reveal the implied borrowing rate, refinance shopping (compute effective APR from advertised payment terms), evaluating "0% financing" that actually has hidden fees, comparing loan offers with different structures.

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 answeringCash flow timingUse this function
What's the annualized return?Irregular datesXIRR
What's the annualized return?Equal annual periodsIRR
What's it worth today at rate X?Irregular datesXNPV
What's it worth today at rate X?Equal annual periodsNPV

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.

Why professionals prefer XIRR

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:

FunctionWhat it returnsTypical use
IPMTInterest portion of a specific paymentRow-by-row interest column
PPMTPrincipal portion of a specific paymentRow-by-row principal column
CUMIPMTCumulative interest between two periodsSummary stats — first 5 years interest, etc.
CUMPRINCCumulative principal between two periodsSummary 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.

=IPMT(0.06/12, PaymentNumber, 30*12, 200000)

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

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.