Excel Finance Functions Guide With PMT, NPV, IRR and Loan Models

Excel runs more of the finance world than most spreadsheets ever should. Treasury teams price bonds in it. Founders model their seed round in it. Your bank probably built your loan amortization schedule inside a workbook that looks suspiciously like one of yours. The reason is simple — Excel ships a deep library of finance functions, and once you learn the core dozen, you can build almost anything.

This guide walks through the functions that pay rent inside real finance workbooks. We'll cover PMT, PV, FV, RATE, NPER, NPV, IRR, MIRR, XNPV, XIRR, CUMIPMT, CUMPRINC, IPMT, PPMT — plus DDB, SLN and SYD for depreciation.

Then we'll build a real loan amortization table, price a bond, run an NPV decision, and stress-test it with Goal Seek, Data Tables and Scenario Manager. You'll also pick up the small habits — sign conventions, rate conversions, period alignment — that separate working models from the ones that quietly mislead the board.

One thing up front. Excel returns finance numbers exactly. The trick is making sure your inputs mean what you think they mean. A monthly rate written as an annual rate will overstate a loan payment by a factor of twelve. A positive cash outflow will swap the sign of an IRR. The functions don't lie. The inputs sometimes do.

Start with the time value of money. Everything in finance leans on this — a dollar today is worth more than a dollar in twelve months. Today's dollar can earn interest, dodge inflation, or fund something else. Excel ships five core functions that share the same arguments and the same mental model. Learn one, learn them all.

The five are PMT, PV, FV, RATE and NPER. Each solves for a different variable in the same equation. PMT solves for payment. PV gives present value. FV returns future value. RATE backs out the interest rate. NPER tells you how many periods you need. Describe a situation in plain English and Excel can solve for whatever you're missing.

All five share three required inputs: rate, nper and one of pmt/pv/fv. The rate must match the period. A 30-year mortgage paid monthly uses 360 periods and a rate of annual/12. A 5-year auto loan paid monthly uses 60 periods at the same monthly rate. Skip the conversion and the answer comes back wildly wrong.

Here's a tiny example to make it stick. "I want to borrow $250,000 at 6.5% over 30 years." Excel solves that with one PMT call: =PMT(0.065/12, 360, 250000) returns about -$1,580 per month. Want to know what loan size that payment supports if rates jump to 7.5%? Swap the function — use PV instead. Same five arguments, different unknown. That's the whole game.

Move past the core five and you hit the cash-flow family. NPV — net present value — discounts an irregular stream of cash flows back to today at a fixed rate. The classic use case is a capital decision: we spend $500k today, we get $120k a year for six years, is this worth doing at a 9% cost of capital? NPV gives you the answer in one line.

Net present value above zero means the project beats the hurdle rate. Below zero, it doesn't. Right at zero, you break even — which is also the definition of the project's IRR. The math is older than most computers; Excel just made it usable for anyone without a financial calculator.

Watch the quirk that trips up nearly everyone. Excel's NPV starts discounting at period 1, not period 0. If your initial investment happens today, add it outside the NPV call — usually =NPV(rate, B2:B7) + B1, where B1 is the negative day-zero outlay. Forget that and you'll discount your initial spend by one extra year. Many real models in real companies have shipped with this mistake baked in.

IRR is NPV's twin. Instead of fixing a rate and solving for value, IRR fixes value at zero and solves for the rate. It returns the discount rate that makes NPV equal zero — the project's break-even return. MIRR adjusts for the fact that real-world reinvestment doesn't actually happen at IRR. You feed it a finance rate and a reinvest rate, and it returns a more honest number for long projects.

XNPV and XIRR are the same idea as NPV and IRR, with one upgrade — they take a column of dates alongside the cash flows. Regular NPV and IRR assume every period is evenly spaced. Reality rarely cooperates. Use XNPV and XIRR whenever your cash flow dates aren't perfectly periodic. Almost always.

You get an investor distribution on January 14. The next one on April 3. A top-up on June 22. Regular NPV can't handle that. XNPV can. Build it into your default toolkit and you'll never run into the "my IRR is wrong" panic again.

The amortization family is where loans live. CUMIPMT returns total interest between two periods. CUMPRINC returns total principal in that window. IPMT and PPMT do the same thing for a single period — interest only or principal only on one payment. Useful for "how much interest did we pay in year 3?" questions during a tax season scramble.

Last group — depreciation. SLN is straight-line, the simplest method: cost minus salvage divided by life. DDB is double-declining-balance, an accelerated method that front-loads expense in the early years. SYD is sum-of-years' digits, another accelerated method with a smoother taper. Pick the method your accountant approves and Excel calculates it per period without complaint.

Time for something concrete. A $300,000 mortgage at 6.75% over 30 years, paid monthly. The PMT formula is =PMT(0.0675/12, 30*12, 300000) and it returns roughly -$1,945.79. The minus sign is Excel's sign convention — positive numbers are money you receive, negative numbers are money you pay. PMT returns negative because you're paying the bank.

If that bothers you, wrap the whole thing in a negative: =-PMT(0.0675/12, 30*12, 300000). Same answer, cleaner display. Same trick works for IPMT, PPMT, CUMIPMT and CUMPRINC. They all follow the same sign convention, so flipping signs at the formula edge keeps your output column readable for stakeholders.

To build the full schedule, lay out 360 rows. Each row needs four numbers: opening balance, interest portion, principal portion, closing balance. Interest equals =IPMT(rate, period, nper, pv). Principal equals =PPMT(rate, period, nper, pv). By month 360 the balance should hit zero, give or take a rounding penny. That's your full loan amortization table.

Bonds next. Excel's PRICE function returns the clean price per $100 of face value, given settlement date, maturity date, coupon rate, yield, redemption value and coupon frequency. For a 10-year corporate paying 5% semi-annually when yields are at 6%, PRICE returns a clean price below par — higher yields push bond prices down. The YIELD function does the reverse. Feed it a price, get back the yield to maturity.

Investment decisions usually live in NPV territory. Evaluate a software platform costing $2M to build, saving $480k a year, lasting five years, cost of capital 11%. Cash flow column reads -2,000,000 at year 0 and 480,000 at years 1 through 5. NPV in Excel is =NPV(0.11, C2:C6) + C1. Positive answer clears the hurdle.

If you also want the project's IRR, drop =IRR(C1:C6) right next to it. Two numbers, one decision. Add a sensitivity layer underneath — what's the NPV if cost of capital climbs to 13%? — and you have a defensible business case in less than ten minutes of work.

Retirement planning leans on FV. Someone saving $800 a month into a portfolio earning 7% annual for 30 years ends up with — let's let Excel say it: =FV(0.07/12, 30*12, -800, 0) returns roughly $976,000. Change the monthly contribution to $1,000 and you cross the million-dollar line. Drop the rate to 5% and the same $800 a month gets you about $666,000. Small inputs, big swings, very fast feedback.

Cash flow analysis lives in XIRR land. A private equity fund draws $10M from you on March 1, 2020, returns $2M on July 15, 2022, another $4M on November 30, 2023, and a final $9M on April 10, 2026. Dates aren't periodic. Use =XIRR(cashflows, dates) to get the annualized IRR of the actual schedule, properly time-weighted.

That's the number to quote to a limited partner. Regular IRR can't even get close on dates this messy — it treats each cash flow as if it landed on a perfectly spaced anniversary, which would inflate or deflate the result depending on how the actual dates fell. Always pair XIRR with the actual deal dates from your statements.

Templates and modeling tips. The single most common mistake in junior analyst models is rate mismatch. Annual rates need annual periods. Monthly rates need monthly periods. The conversion is usually dividing by 12 — but it's not always linear. Effective annual rates differ from nominal rates depending on compounding frequency.

Excel ships NOMINAL and EFFECT for exactly this. =EFFECT(0.06, 12) converts a 6% nominal annual rate compounded monthly into the effective rate of 6.17%. =NOMINAL(0.0617, 12) reverses it. Use them whenever you're comparing loan offers with different compounding schedules — the headline rate isn't always the honest one.

Goal Seek is the secret weapon for inverse questions. "What monthly contribution gets me to a million dollars in 25 years at 6%?" Build the FV formula, then Data > What-If Analysis > Goal Seek. Set the FV cell to 1,000,000 by changing the contribution cell. Excel iterates and lands on the answer in milliseconds. Same trick for break-even units, target IRR, required loan size — anything where you have a formula and want to back-solve for one input.

Data Tables are sensitivity analysis at scale. Your NPV model depends on a discount rate and a growth rate. You want to see NPV across a grid — eight discount rates down the side, six growth rates across the top, forty-eight NPVs in the body. Set up the grid, link the corner cell to your NPV formula, then Data > What-If Analysis > Data Table. Instant heatmap.

Tell Excel which input is the row variable and which is the column variable. The grid fills in instantly. You now have a visual heatmap of NPV under every reasonable assumption combination — and a way to spot the regions where the project breaks even, looks great, or quietly goes negative.

Scenario Manager handles the bigger move — swapping multiple inputs at once. Base case, downside, upside, each with different inputs. Data > What-If Analysis > Scenario Manager. Add each scenario with its input changes. Switching scenarios reloads the model in one click. It's how serious decks get built — and how analysts avoid the soul-crushing process of manually retyping inputs three times.

Power Query pulls live data. Historical stock prices, FX rates, treasury yields, any web table. Combined with Excel 365's STOCKHISTORY function — which returns historical OHLC data between two dates — you can build live equity dashboards inside Excel with no external tools. The Stocks linked data type pulls current price, market cap and P/E on demand, refreshing whenever you ask.

Charts matter when the model has to land with someone who doesn't open spreadsheets for fun. Waterfall charts are the right pick for bridging numbers — opening balance, plus or minus each component, closing balance. Excel ships a native Waterfall type since 2016. Combo charts mix bar and line for revenue alongside margin.

Power BI integrates straight from Excel for dashboards. Get Data > From Workbook inside Power BI Desktop, and your model becomes a refreshable data source for stakeholder dashboards. The same underlying numbers, two views — analyst-grade detail in Excel, board-grade clarity in Power BI.

One last skill — the one that saves the most pain. Audit your formulas. Press F2 inside any cell to enter edit mode and see the colored ranges. Use Formula Auditing on the Formulas ribbon to trace precedents and dependents. Evaluate Formula steps through complex expressions. Show Formulas flips the whole sheet into formula view, perfect for spotting hard-coded numbers buried inside calculations.

The classic finance errors are predictable. PMT returning negative when you wanted positive — flip the sign. Annual rate used with monthly period — divide by 12. NPV missing the initial outlay — add C1 outside the NPV call. IRR returning the wrong root — supply a guess. Mixing nominal and real rates. Each one has shipped to production in real companies. Each one has cost real money.

That's the finance toolkit. The functions are the easy part. The hard part is what you do with them — and that, happily, is a skill that compounds. Build a few real models. Break them on purpose. Trace the errors. Rebuild cleaner.

Inside a year you'll have the kind of fluency where you read someone else's workbook and spot the three audit risks immediately. Keep PMT, NPV, IRR and FV inside muscle-memory reach — they're the four functions you'll reach for nine times out of ten. The rest you can look up. The big four you should know cold.

And one last reminder. The functions are tools, not answers. A perfectly correct NPV calculation on flawed cash flow assumptions still gives you a flawed decision. Spend at least as much time pressure-testing the inputs as you do typing the formula. Where did the revenue forecast come from? Is the discount rate defensible? Does the terminal value assume growth your business model can actually support? Those questions are where the real finance work happens — and Excel just gives you the math to evaluate the answers.

The good news is that this entire toolkit is portable. Once you can model a mortgage in Excel, you can model a corporate bond in Excel. Once you can build a sensitivity grid for one project, you can build one for any project. The mental moves transfer. The function names transfer. Even the bad habits transfer — so build the good ones first, and let your future self thank you for the discipline.