Master the 9 Basic Excel Financial Functions and Formulas

Excel financial functions let you calculate loan payments, investment returns, savings growth, depreciation, and bond prices in a single formula. Tasks that once needed a financial calculator or an accountant's worksheet take seconds once you know which function to use.

This guide covers the nine basic financial functions every business owner, manager, and analyst should know: PMT, NPV, IRR, SYD, FV, PV, DDB, PRICE, and SLN. For each one, you will find what it does, its syntax in plain language, a worked example with the exact result, and when to use it. A short section at the end introduces a few related functions, such as CUMIPMT, RATE, and XIRR, that build on the same ideas.

It also includes a free calculator that runs all of these functions on your own numbers and shows the Excel formula beside every answer.

The 9 Excel Financial Functions at a Glance

Every example in this table is explained in detail below, and every result was checked against the same math Excel uses.

One Rule Before You Start: Cash Flow Signs

Excel's financial functions treat money from your point of view. Money you receive is positive, and money you pay out is negative. That is why a loan amount is often entered as a negative number in PMT, or why PV returns a negative result for payments you would receive.

If a result has the "wrong" sign, the math is not wrong. Flip the sign of the input, or put a minus sign in front of the function, and the answer will read the way you expect. Keep this rule in mind and most confusion with financial functions disappears.

The second rule is to match your periods. If payments are monthly, divide the annual rate by 12 and multiply the years by 12. Mixing an annual rate with monthly periods is the most common mistake in these formulas.

1. PMT: Calculating Loan Payments

The PMT function calculates the regular payment on a loan with a constant interest rate and constant payments. It is the function behind every mortgage, car loan, and equipment financing quote.

The syntax is =PMT(rate, nper, pv, [fv], [type]). The rate is the interest rate per period, nper is the total number of payments, and pv is the loan amount. The optional fv is the balance you want left after the last payment, which defaults to zero, and type tells Excel whether payments happen at the end of each period (0, the default) or the beginning (1).

Example: for a $10,000 loan at 5% annual interest over five years, =PMT(5%/12,60,-10000) returns $188.71, the monthly payment. The loan is entered as a negative number so the payment comes back positive. Enter it as 10000 and the result is -188.71, which means the same thing.

Use PMT for loan repayment planning, comparing financing offers, and checking whether a purchase fits your monthly cash flow. Microsoft documents every argument on its PMT function page.

2. NPV: Calculating Net Present Value

The NPV function calculates what a series of future cash flows is worth today, using a discount rate that reflects your cost of money or required return. A dollar received in three years is worth less than a dollar today, and NPV puts all the future dollars on the same footing.

The syntax is =NPV(rate, value1, [value2], ...), where rate is the discount rate per period and the values are cash flows at regular intervals.

Example: with a 10% discount rate and cash flows of $1,000, $1,500, and $3,000 over three years, =NPV(10%,1000,1500,3000) returns $4,402.70.

There is one important detail. Excel's NPV assumes the first cash flow happens at the end of the first period, so the upfront investment should not go inside the function. Subtract it separately. If this project costs $5,000 today, =NPV(10%,1000,1500,3000)-5000 returns -$597.30. A negative NPV means the project earns less than your 10% target, so on these numbers it is not worth doing.

Use NPV to compare investments, equipment purchases, and projects on a like-for-like basis. See Microsoft's NPV function page for more detail.

3. IRR: Calculating Internal Rate of Return

The IRR function calculates the internal rate of return, the annual return an investment earns based on its cash flows. Put another way, it is the discount rate at which NPV equals exactly zero.

The syntax is =IRR(values, [guess]). The values are a range of cash flows that must include at least one negative number, usually the initial investment, and one positive number. The optional guess is a starting point for Excel's calculation and defaults to 10%.

Example: an investment of $5,000 that returns $1,500, $2,000, $2,500, and $3,000 over the next four years. With -5000 in B2 and the four inflows in B3 through B6, =IRR(B2:B6) returns 24.89%.

If your required return is lower than the IRR, the investment clears the bar. IRR is ideal for ranking projects, but it assumes evenly spaced cash flows. When payments fall on irregular dates, use XIRR instead, covered later in this guide. Microsoft's IRR function page explains the calculation.

4. SYD: Sum-of-Years' Digits Depreciation

The SYD function calculates depreciation using the sum-of-years' digits method, which front-loads depreciation into the early years of an asset's life. It suits assets that lose value fastest when they are new, such as vehicles and technology.

The syntax is =SYD(cost, salvage, life, per): the asset's cost, its salvage value at the end of its life, the number of periods in its life, and the period you want to calculate.

Example: for a $10,000 asset with a $1,000 salvage value and a five-year life, =SYD(10000,1000,5,1) returns $3,000 for the first year. The method adds the years' digits (1 + 2 + 3 + 4 + 5 = 15) and depreciates 5/15 of the $9,000 depreciable amount in year one, 4/15 in year two, and so on down to 1/15 in year five.

5. FV: Calculating Future Value

The FV function calculates what an investment or savings plan will be worth in the future, based on regular payments and a constant rate of return.

The syntax is =FV(rate, nper, pmt, [pv], [type]). The rate is per period, nper is the number of periods, and pmt is the amount added each period, entered as a negative number because it is money you pay in. The optional pv is any starting balance, and type sets end or beginning of period.

Example: saving $200 a month for 10 years at a 6% annual return, =FV(6%/12,10*12,-200) returns $32,775.87. You deposit $24,000 in total, and the remaining $8,775.87 is growth.

Use FV for savings goals, retirement planning, and building a cash reserve for the business. Changing type to 1, for deposits at the start of each month, adds a little more growth because every deposit earns interest for one extra month.

6. PV: Calculating Present Value

The PV function works in the opposite direction from FV. It calculates what a series of future payments is worth today, at a given rate. It answers questions like "How much is a stream of $500 monthly payments really worth?"

The syntax is =PV(rate, nper, pmt, [fv], [type]), with the same arguments as FV.

Example: receiving $500 a month for five years at a 5% annual rate, =PV(5%/12,5*12,500) returns -$26,495.35. The negative sign follows the cash flow rule. To receive those payments, you would need to invest $26,495.35 today, so the value of the payment stream is $26,495.35.

PV is also how you work out how much you can borrow. If you can afford $400 a month for five years at 7%, =PV(7%/12,60,-400) tells you the loan amount that payment supports.

7. DDB: Double-Declining Balance Depreciation

The DDB function calculates depreciation using the double-declining balance method, another accelerated approach. It applies twice the straight-line rate to the asset's remaining book value each year.

The syntax is =DDB(cost, salvage, life, period, [factor]). The optional factor sets the rate at which the balance declines and defaults to 2, which is what "double" refers to.

Example: for the same $10,000 asset with a $1,000 salvage value and a five-year life, =DDB(10000,1000,5,1) returns $4,000 for the first year. The straight-line rate is 20% a year, so DDB uses 40% of the $10,000 book value. In year two, it takes 40% of the remaining $6,000, which is $2,400. Excel stops depreciating once the book value reaches the salvage value, which is why the final year is smaller.

8. PRICE: Calculating the Price of a Bond

The PRICE function calculates the price of a bond per $100 of face value, based on the yield an investor expects. When the market yield is higher than the bond's coupon rate, the bond sells below face value. When it is lower, the bond sells above.

The syntax is =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis]). Settlement is the date the buyer takes ownership, maturity is the date the bond expires, rate is the annual coupon rate, yld is the annual yield, redemption is the value at maturity per $100 of face value, frequency is the number of coupon payments per year (1, 2, or 4), and basis is an optional day-count convention.

Example: a bond with a 5% coupon, paid twice a year, and exactly 10 years left to maturity, priced to yield 6%. With the settlement date in A2, =PRICE(A2,EDATE(A2,120),5%,6%,100,2) returns 92.56, so the bond costs $92.56 for every $100 of face value. The EDATE function sets maturity 120 months after settlement. Always enter dates as real Excel dates or cell references, not as text.

9. SLN: Straight-Line Depreciation

The SLN function calculates straight-line depreciation, the simplest and most common method, which spreads the cost evenly across the asset's life.

The syntax is =SLN(cost, salvage, life). Example: for the $10,000 asset with a $1,000 salvage value and five-year life, =SLN(10000,1000,5) returns $1,800 per year.

Comparing the Three Depreciation Methods

The table shows how SLN, SYD, and DDB spread the same $9,000 of depreciation over five years. All three reach the same total, but the accelerated methods recognize more of it early. Which method a business uses for its books or taxes depends on accounting rules and professional advice, and this table only shows how the Excel functions differ.

Free Excel Financial Functions Calculator

Try these functions with your own numbers. The calculator below is loaded with the examples from this guide. Change the loan, savings plan, investment cash flows, or asset details, and every answer updates along with its Excel formula.

Click Copy to Excel, then paste into cell A1 of a blank sheet. Your inputs land in column B and every result is pasted as a live formula, so you get a working financial model you can keep adjusting.

More Excel Financial Functions Worth Knowing

Once the nine basics feel comfortable, a handful of related functions cover most of the remaining everyday needs. Microsoft's financial functions reference lists the complete set.

CUMIPMT and CUMPRINC total the interest or principal paid between two payment periods. For the $10,000 loan above, =CUMIPMT(5%/12,60,10000,1,12,0) returns -$459.00, the interest paid in the first year.

IPMT and PPMT split a single payment into its interest and principal parts, which is the basis of any amortization schedule.

RATE finds the interest rate when you know the payment, and NPER finds how many payments it will take. For example, =NPER(5%/12,-188.71,10000) confirms the loan above takes about 60 payments.

XNPV and XIRR work like NPV and IRR but accept a date for each cash flow, which makes them the right choice when payments are irregular.

EFFECT and NOMINAL convert between a quoted annual rate and the effective rate after compounding, useful for comparing loan and savings offers fairly.

Common Mistakes With Excel Financial Functions

Mismatched periods. Using a 6% annual rate with 120 monthly periods overstates growth dramatically. Divide annual rates by 12 for monthly calculations.

Confusing signs. A negative result usually means money going out. Check which way the cash flows before assuming there is an error.

Putting the initial investment inside NPV. NPV discounts every value in the list, including the first one. Subtract the upfront cost outside the function.

Typing dates as text. Functions like PRICE and XIRR need real dates. Use cell references or the DATE and EDATE functions.

Hardcoding numbers. Put rates, amounts, and terms in their own cells and reference them. It makes models easy to update and easy to check.

Putting Financial Functions to Work in Your Business

These functions become most valuable when they are part of a regular planning habit, such as checking financing options before a purchase, testing whether a new hire or piece of equipment pays for itself, or projecting cash reserves. Our guide to building an Excel cash flow planner shows how to put them into a working model, and the top Excel functions for a CFO goes further.

When the numbers you need come from your team's time, projects, and invoices, Updoot is business management software that keeps that data in one place, so your spreadsheet models start from accurate figures. It costs $5 per user per month, and you can start a free trial anytime.

Final Thoughts

PMT, NPV, IRR, SYD, FV, PV, DDB, PRICE, and SLN cover the core questions of business finance: what something costs over time, what it will be worth, what it returns, and how its value declines. Learn the cash flow sign rule, match your periods, and keep inputs in their own cells, and these nine functions will handle most of the financial math you face. Use the calculator above to practice, then copy the formulas straight into your own workbook.

Sources

Function syntax and behavior follow Microsoft's documentation for PMT, NPV, and IRR, and the complete financial functions reference. All example results were calculated independently to verify them.