Finance and investment formulas

Excel Financial Functions Guide

Use Excel financial functions for loans, investments, net present value, and internal rate of return with practical formula examples.

Core financial formulas

  • Use PMT for loan payments, FV for future value, and PV for present value.
  • Use NPV and IRR to compare investment scenarios and project returns.
  • Keep rate and period units consistent — monthly rate with monthly periods, annual rate with annual periods.

Avoid finance formula errors

  • Confirm cash outflows and inflows use consistent signs across the model.
  • Use DATE or EDATE when payment schedules depend on month-based timelines.
  • Document assumptions so NPV and IRR outputs can be audited later.

Financial modeling workflow

  • Document rate period (annual vs monthly) on a Settings tab visible to every reviewer.
  • Separate inputs, calculations, and outputs — color-code editable cells.
  • Reconcile PMT schedules to bank statements on three sample months before trusting the model.
  • Use XNPV and XIRR when cash flows are not evenly spaced in time.

Recommended next steps

  • PMT, FV, and PV for loans and savings; NPV and IRR for project cash-flow decisions.
  • XNPV and XIRR when payment dates are irregular instead of evenly spaced periods.
  • Always document whether rates are annual vs monthly — mixing periods is the top cause of wrong loan math.
  • Download the budget template to practice variance formulas alongside financial functions.

Financial function patterns

  • Loan payment: =PMT(Rate/12,TermMonths,-Principal) with rate converted to monthly when needed.
  • Future value of savings: =FV(Rate/12,Months,-Payment) for regular contributions.
  • Project NPV: =NPV(DiscountRate,CashFlows)+InitialInvestment arranged with consistent period spacing.
  • Bond-style XIRR: =XIRR(Amounts,Dates) when payment dates are irregular.

People also ask

  • NPV vs XNPV? NPV assumes equal periods; XNPV accepts actual payment dates.
  • Why is PMT negative? Sign convention — Excel returns outflows as negative; align signs across the model.
  • IRR not converging? Try different guess or XIRR with explicit dates when cash flows are unusual.

Deep-dive function map for Excel Financial Functions Guide

  • PMT (/functions/pmt/): Calculates the payment for a loan based on constant payments and a constant interest rate. Example: =PMT(5%/12, 360, -200000).
  • FV (/functions/fv/): Returns the future value of an investment based on periodic, constant payments and a constant interest rate. Example: =FV(5%/12, 120, -500).
  • PV (/functions/pv/): Returns the present value of an investment - the total amount that future payments are worth now. Example: =PV(5%/12, 60, -500).
  • NPV (/functions/npv/): Calculates the net present value of an investment based on a discount rate and future cash flows. Example: =NPV(10%, 30000, 35000, 40000, 45000).
  • IRR (/functions/irr/): Returns the internal rate of return for a series of cash flows. Example: =IRR({-100000, 30000, 35000, 40000, 45000}).
  • XIRR (/functions/xirr/): Returns the internal rate of return for a schedule of cash flows with specific dates. Example: =XIRR(A1:A5, B1:B5).
  • RATE (/functions/rate/): Returns the interest rate per period of an annuity. Example: =RATE(60, -500, 25000)*12.
  • NPER (/functions/nper/): Returns the number of periods for an investment based on periodic payments and a constant interest rate. Example: =NPER(5%/12, -500, 25000).

Two-week study plan

  • Week 1 — Read each related function page and copy one example per function into a practice Table.
  • Week 2 — Apply this guide to a real export from your job; document fixes in a changelog tab.
  • Explore categories: Financial.
  • Run the fix-excel-formula-errors checklist when any formula shows #N/A, #VALUE!, #REF!, or #SPILL!.
  • Compare similar functions (VLOOKUP vs XLOOKUP, SUMIF vs SUMIFS) before standardizing team templates.
  • Export a PDF snapshot of your completed practice workbook for future reference and onboarding.

Error and troubleshooting cross-links

  • Fix #NUM! (/errors/num/) — step-by-step causes and solutions in the error directory.
  • Fix #VALUE! (/errors/value-error/) — step-by-step causes and solutions in the error directory.