Tutorials - 2026-01-21

Excel Functions for Business & Finance

Master the financial functions in Excel. Learn NPV, IRR, PMT, and other essential functions for business analysis and financial modeling.

Excel: The Finance Professional's Best Friend

  • Excel remains the dominant tool in finance for good reason. Its built-in financial functions handle everything from simple loan calculations to complex investment analysis. Whether you're a financial analyst, accountant, or business owner, these functions are essential. For a complete overview of all Excel functions, see our Excel Functions: Complete Guide.

Loan and Payment Functions

  • PMT — Calculate Loan Payments The most-used financial function. Calculates the periodic payment for a loan. \\\ =PMT(rate, nper, pv, [fv], [type]) \\\ Arguments: rate — Interest rate per period nper — Total number of payments pv — Present value (loan amount) fv — Future value (usually 0 for loans) type — 0 = end of period, 1 = beginning Example: Monthly payment on a $300,000 mortgage at 6% for 30 years: \\\ =PMT(6%/12, 30*12, -300000) \\\ Result: $1,798.65 per month Important: Divide annual rate by 12 for monthly payments. Multiply years by 12 for total periods. IPMT — Interest Portion of Payment How much of a specific payment goes to interest? \\\ =IPMT(rate, per, nper, pv) \\\ Example: Interest portion of payment #12: \\\ =IPMT(6%/12, 12, 360, -300000) \\\ PPMT — Principal Portion of Payment How much of a specific payment goes to principal? \\\ =PPMT(rate, per, nper, pv) \\\ Example: Principal portion of payment #12: \\\ =PPMT(6%/12, 12, 360, -300000) \\\ Pro tip: IPMT + PPMT = PMT for any given period. NPER — Number of Periods How long to pay off a loan with a given payment? \\\ =NPER(rate, pmt, pv, [fv]) \\\ Example: How many months to pay off $10,000 at 5% with $200/month payments? \\\ =NPER(5%/12, -200, 10000) \\\ RATE — Calculate Interest Rate What interest rate results in a specific payment? \\\ =RATE(nper, pmt, pv, [fv]) \\\ Example: What rate gives a $500/month payment on a $25,000 loan over 5 years? \\\ =RATE(60, -500, 25000) * 12 \\\ Multiply by 12 to get annual rate.

Investment Analysis Functions

  • NPV — Net Present Value Calculates the present value of future cash flows minus the initial investment. Positive NPV = good investment. \\\ =NPV(rate, value1, [value2], ...) + initial_investment \\\ Example: Investment of $100,000 with cash flows of $30,000/year for 5 years at 10% discount rate: \\\ =-100000 + NPV(10%, 30000, 30000, 30000, 30000, 30000) \\\ Result: $13,723.60 (positive = invest!) Important: NPV assumes cash flows occur at the END of each period. Initial investment is added separately. XNPV — NPV with Specific Dates When cash flows don't occur at regular intervals, use XNPV. \\\ =XNPV(rate, values, dates) \\\ Example: Date Cash Flow 1/1/2024 -100,000 3/15/2024 25,000 7/1/2024 35,000 12/31/2024 50,000 \\\ =XNPV(10%, B2:B5, A2:A5) \\\ IRR — Internal Rate of Return The discount rate that makes NPV equal to zero. Higher IRR = better investment. \\\ =IRR(values, [guess]) \\\ Example: \\\ =IRR({-100000, 30000, 30000, 30000, 30000, 30000}) \\\ Result: 15.24% Interpretation: This investment returns 15.24% annually. XIRR — IRR with Specific Dates For irregular cash flows, use XIRR. \\\ =XIRR(values, dates, [guess]) \\\ MIRR — Modified IRR IRR assumes reinvestment at the IRR rate, which is often unrealistic. MIRR lets you specify reinvestment and finance rates. \\\ =MIRR(values, finance_rate, reinvest_rate) \\\ Example: \\\ =MIRR({-100000, 30000, 30000, 30000, 30000, 30000}, 8%, 10%) \\\

Present and Future Value

  • PV — Present Value What is a future amount worth today? \\\ =PV(rate, nper, pmt, [fv], [type]) \\\ Example: Present value of receiving $1,000/month for 10 years at 6%: \\\ =PV(6%/12, 120, -1000) \\\ FV — Future Value What will an investment be worth in the future? \\\ =FV(rate, nper, pmt, [pv], [type]) \\\ Example: Future value of investing $500/month for 20 years at 8%: \\\ =FV(8%/12, 240, -500) \\\ Result: $294,510.21

Depreciation Functions

  • SLN — Straight-Line Depreciation Equal depreciation each period. \\\ =SLN(cost, salvage, life) \\\ Example: $50,000 asset, $5,000 salvage value, 10-year life: \\\ =SLN(50000, 5000, 10) \\\ Result: $4,500/year DB — Declining Balance Accelerated depreciation—more in early years. \\\ =DB(cost, salvage, life, period, [month]) \\\ DDB — Double Declining Balance Even more accelerated depreciation. \\\ =DDB(cost, salvage, life, period, [factor]) \\\

Business Analysis Functions

  • SUMIFS — Conditional Totals Essential for financial reporting. Sum values based on multiple criteria. \\\ =SUMIFS(sum_range, criteria_range1, criteria1, ...) \\\ Example: Total revenue for Product A in Q1 2024: \\\ =SUMIFS(Revenue, Product, "A", Quarter, "Q1", Year, 2024) \\\ Learn more about SUMIFS → XLOOKUP — Data Retrieval Pull data from reference tables—prices, rates, customer info. \\\ =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) \\\ Example: Get customer credit limit: \\\ =XLOOKUP(A2, Customers!A:A, Customers!E:E, 0) \\\ Learn more about XLOOKUP → ROUND — Financial Precision Financial calculations often require specific rounding. \\\ =ROUND(number, num_digits) =ROUNDUP(number, num_digits) =ROUNDDOWN(number, num_digits) \\\ Example: Round to cents: \\\ =ROUND(A1, 2) \\\

Financial Modeling Best Practices

  • Use Named Ranges Instead of: \\\ =PMT(B1/12, B2*12, -B3) \\\ Use: \\\ =PMT(AnnualRate/12, Years*12, -LoanAmount) \\\ Separate Inputs from Calculations Create an "Assumptions" section with all inputs. Reference these cells in formulas. Sign Conventions Cash outflows (payments, investments) = negative Cash inflows (receipts, returns) = positive Document Your Formulas Add comments explaining complex calculations. Build Sensitivity Tables Use Data Tables to show how results change with different assumptions.

Common Financial Calculations

  • Loan Amortization Schedule Period Payment Interest Principal Balance 1 =PMT(...) =IPMT(...) =PPMT(...) =Previous-Principal Break-Even Analysis \\\ Break-Even Units = Fixed Costs / (Price - Variable Cost) =FixedCosts / (Price - VariableCost) \\\ Compound Annual Growth Rate (CAGR) \\\ =((EndValue/StartValue)^(1/Years))-1 \\\ Or use: \\\ =RATE(Years, 0, -StartValue, EndValue) \\\

Quick Reference: Financial Functions

  • Function Purpose PMT Loan payment IPMT Interest portion PPMT Principal portion NPER Number of periods RATE Interest rate PV Present value FV Future value NPV Net present value XNPV NPV with dates IRR Internal rate of return XIRR IRR with dates MIRR Modified IRR SLN Straight-line depreciation DB Declining balance DDB Double declining balance

Next Steps

  • Expand your financial Excel skills: Excel Functions: Complete Guide — Complete function reference Excel Functions for Data Analysis — Analytical functions Financial Functions — Full financial function library Math & Trig Functions — Calculation functions Browse All Functions — Complete function library

Apply this tutorial in your workbook

  • Copy one example from this article into a blank sheet. Change the sample ranges to match your column letters. Press F2 and Enter after edits so Excel recalculates. Compare your result to the expected output in the article. Related hubs: Excel functions directory · Formula guides · Function comparisons

Frequently asked questions

  • What should I practice after reading this tutorial? Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.
  • Which Excel version do I need for the formulas in this article? Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.