Mastering Financial Functions in Google Sheets: NPV, IRR, Loans, and Bonds
July 24th, 2026
Google Sheets ships a full corporate-finance toolkit behind ordinary-looking formulas. NPV and IRR evaluate whether a project is worth funding, PMT and PV turn loan and annuity math into a single cell, and a smaller family of bond and depreciation functions handles the rest. This walkthrough covers all of them with concrete numbers you can drop straight into a sheet.
NPV: is the investment worth it?
Net present value discounts a series of future cash flows back to today's dollars at a given rate, then sums them. If the result is positive, the investment clears your required return.
=NPV(0.08, -10000, 3000, 4200, 4500, 3800)
Here -10000 is the upfront outlay, followed by four years of projected returns, discounted at 8%. That returns roughly 2,171 — a positive NPV, meaning the project beats an 8% hurdle rate even after accounting for the time value of money. Drop the rate to 4% and the same cash flows are worth more today; push it to 15% and NPV can go negative even though the raw cash flows never changed sign.
One gotcha: NPV assumes the first cash flow happens one period from now, not at time zero. If your initial outlay happens today, keep it outside the NPV range and add it separately:
=-10000 + NPV(0.08, 3000, 4200, 4500, 3800)
IRR: the rate that makes NPV zero
Where NPV asks "what's this worth at rate X," IRR asks the reverse question: "what rate makes NPV exactly zero?" That rate is the investment's break-even return, and it's the number most people mean when they say "return on investment."
=IRR(A1:A5)
If A1:A5 holds -10000, 3000, 4200, 4500, 3800 — the same cash flows as above — IRR returns about 15.9%. Compare that against your cost of capital: if you can borrow at 8% and the project returns 15.9%, it clears the bar. IRR occasionally fails to converge on unusual cash-flow patterns (multiple sign changes), in which case a second argument nudges the initial guess:
=IRR(A1:A5, 0.1)
XIRR: when cash flows land on real dates
IRR assumes evenly spaced periods. Real investments rarely cooperate — a capital call in March, a distribution in September, another in the following June. XIRR takes actual dates instead of assuming a fixed interval:
=XIRR(B1:B4, C1:C4)
With amounts -50000, 12000, 18000, 35000 in B1:B4 and dates 2024-01-15, 2024-07-01, 2025-02-10, 2025-11-30 in C1:C4, XIRR annualizes the return correctly despite the uneven gaps — something a plain IRR on the same numbers would get wrong by treating each entry as exactly one period apart.
PMT and RATE: pricing a loan
PMT computes the fixed periodic payment on a loan or annuity given a rate, term, and principal:
=PMT(0.06/12, 360, 300000)
A $300,000 mortgage at 6% annual interest (divided by 12 for a monthly rate) over 360 monthly payments returns about -1,799 — the sign is negative because it's a payment leaving your account. To find the payment on the principal alone, then compare against a competing rate, just swap the rate argument.
Run the same problem backwards with RATE when you know the payment but not the interest rate — useful for reverse-engineering the APR buried in a dealer financing offer:
=RATE(60, -450, 22000) * 12
A $22,000 loan repaid at $450/month for 60 months implies a monthly rate that, annualized, comes out to roughly 7.4%.
PV and FV: present and future value
PV tells you what a stream of future payments is worth today; FV runs the same annuity math forward.
=PV(0.05/12, 240, -1200)
Saving nothing extra but planning to withdraw $1,200/month for 20 years (240 months) at a 5% annual return requires a present lump sum of about $183,000 today.
=FV(0.07/12, 360, -500)
Contributing $500/month for 30 years at a 7% annual return grows to roughly $610,000 — the compounding engine behind most retirement-planning spreadsheets.
Bond valuation: YIELD and PRICE
Bonds pay a fixed coupon but trade at prices that move with market rates, so their real return isn't the coupon rate. YIELD backs out the actual annualized return from a bond's market price:
=YIELD(DATE(2024,1,1), DATE(2034,1,1), 0.04, 92, 100, 2, 0)
A 10-year bond with a 4% coupon, semi-annual payments, and a redemption value of 100, currently trading at 92 (a discount), yields about 4.85% — higher than the coupon because you're buying it below par.
PRICE runs the same relationship the other direction — given a target yield, what should you pay?
=PRICE(DATE(2024,1,1), DATE(2034,1,1), 0.04, 0.05, 100, 2, 0)
If the market demands a 5% yield on that same 4% bond, fair price comes out to about 92.3 — confirming the numbers above are internally consistent.
ACCRINT: interest that's built up but not yet paid
Bonds trade between coupon dates, and the buyer owes the seller the interest that accrued since the last payment. ACCRINT calculates that accrued amount:
=ACCRINT(DATE(2024,1,1), DATE(2024,7,1), DATE(2024,4,15), 0.05, 1000, 2, 0)
For a bond issued January 1 with semi-annual coupons and a face value of 1,000, settling on April 15 means roughly three and a half months of interest has accrued since issue — around $18.75 — which gets added to the quoted price to determine what the buyer actually pays.
Depreciation: SLN vs. DB and DDB
SLN spreads an asset's cost evenly across its useful life — the simplest depreciation method:
=SLN(25000, 3000, 8)
A $25,000 asset with a $3,000 salvage value depreciated over 8 years loses a flat $2,750 per year, every year.
Declining-balance methods front-load the expense instead, matching how many assets (vehicles, equipment) actually lose value fastest when new. DB uses a fixed-rate declining balance:
=DB(25000, 3000, 8, 1)
That first-year depreciation comes out closer to $5,938 — more than double the straight-line figure. DDB (double-declining-balance) is more aggressive still and lets you set the acceleration factor explicitly:
=DDB(25000, 3000, 8, 1, 2)
With a factor of 2 (true double-declining), year-one depreciation is about $6,250. Both DB and DDB taper off in later years as the remaining book value shrinks, eventually approaching (but respecting) the salvage floor.
EFFECT and NOMINAL: comparing rates quoted differently
A "6% annual rate, compounded monthly" isn't the same as a flat 6% — compounding frequency changes the real return. EFFECT converts a nominal rate into the true annual rate you actually earn:
=EFFECT(0.06, 12)
That returns about 6.17% — the real annual return once monthly compounding is accounted for. NOMINAL runs the conversion in reverse, useful when a bank quotes an effective rate and you need the nominal figure to compare against a loan quoted the conventional way:
=NOMINAL(0.0617, 12)
That recovers the original 6% nominal rate. Whenever you're comparing two loans or investment products that compound at different frequencies, converting both to effective rates with EFFECT first is the only fair comparison.