Microsoft Excel comes with many built-in financial functions. This page lists all of the financial formulas that are available, including those in the Analysis ToolPak. There is extensive help for each of the functions, including examples of how the formula can be used. To see a list of all the financial functions in Excel, go to Insert > Function to open the insert function wizard. Select the Financial category, choose a function, and then click on the Help button/link. Another approach is to press F1 in Excel to open the Help window, then type "financial functions" into the Answer Wizard.

See also: Financial Modeling with Excel Spreadsheets

Financial Formulas - by Category

For convenience, I've listed all of the Excel Financial functions, with a * next those that are only available after installing the Analysis ToolPak (To install, go to Tools > Add-ins > and select Analysis ToolPak).

Depreciation Formulas

  • DB - Fixed-Declining Balance
  • DDB - Double-Declining Balance
  • SLN - Straight-Line Depreciation
  • SYD - Sum-of-Years' Digits
  • VDB - Variable Declining Balance
  • * AMORLINC - (for the French accounting system) Depreciation for each accounting period
  • * AMORDEGRC - (for the French accounting system) Uses a depreciation coefficient

Formulas for Interest, Cash Flow, Investments, Annuities

  • * CUMIPMT - Cumulative Interest Payment
  • * CUMPRINC - Cumulative Principal
  • * EFFECT - Effective annual interest rate
  • FV - Future Value of an investment
  • * FVSCHEDULE - Future Value with a variable rate
  • IPMT - Interest Payment for an investment or loan
  • IRR - Internal Rate of Return
  • ISPMT - Interest Payment during a Specific period (for compatibility with Lotus)
  • MIRR - Modified Internal Rate of Return
  • NPER - Number of Periods for an investment or loan
  • NPV - Net Present Value formula
  • PMT - Periodic Payment for an annuity
  • PPMT - Payment on the Principal for an annuity or loan
  • PV - Present Value of an investment
  • RATE - Interest rate per period
  • * XIRR - Internal Rate of Return (not necessarily periodic)
  • * XNPV - Net Present Value (not necessarily periodic)

Functions for Coupons

  • * COUPDAYBS - Days from the Beginning of the Coupon period to the Settlement date
  • * COUPDAYS - Days in the coupon period that contains the Settlement date
  • * COUPDAYSNC - Days from the Settlement date to the Next Coupon date
  • * COUPNCD - Next Coupon Date after the settlement date
  • * COUPPCD - Previous Coupon Date before the settlement date
  • * COUPNUM - Number of coupons between the settlement and maturity date

Finance Formulas for Securities

  • * ACCRINT - Accrued Interest
  • * ACCRINTM - Accrued Interest at Maturity
  • * DISC - Discount rate
  • * DURATION - Annual Duration
  • * INTRATE - Interest rate for a fully invested security
  • * MDURATION - Macauley modified duration (with an assumed par value of $100)
  • * NOMINAL - Annual nominal interest rate
  • * ODDFPRICE - Price per $100 face value with an Odd First period
  • * ODDFYIELD - Yield with an Odd First period
  • * ODDLPRICE - Price per $100 face value with an Odd Last period
  • * ODDLYIELD - Yield with an Odd Last period
  • * PRICE - Price per $100 face value
  • * PRICEDISC - Price per $100 face value of a Discounted security
  • * PRICEMAT - Price per $100 face value of a security that pays interest at Maturity
  • * RECEIVED - Amount received at maturity for a fully invested security
  • * YIELD - Yield on a security that pays periodic interest
  • * YIELDDISC - Annual yield for a discounted security (Treasury bill)
  • * YIELDMAT - Annual yield of a security that pays interest at maturity

Formulas for Dollar Price Conversions

  • * DOLLARDE - Converts a dollar price from a Fraction to a Decimal number
  • * DOLLARFR - Converts a dollar price from a Decimal number to a Fraction

Treasury Bill Functions

  • * TBILLEQ - Bond-equivalent yield for a Treasury Bill
  • * TBILLPRICE - Price per $100 face value for a Treasury Bill
  • * TBILLYIELD - Yield for a Treasury Bill

Disclaimer: This article is meant for educational purposes only.

LIKE THIS PAGE?

Follow Us On ...

       
Master Excel - Spreadsheet Tips Workbook

Create Dashboard Reports in Excel!

Books on Financial Functions and Formulas


Excel Formulas and Functions (including Financial Formulas)
F1 Get the Most Out of Excel Formulas & Functions: The Ultimate Excel Formulas & Functions Help Guide
by Joseph Rubin


Financial Modeling - 2nd Edition
Financial Modeling - 2nd Edition
by Simon Benninga
(Uses Excel)


Principles of Finance with Excel
Principles of Finance with Excel
by Simon Benninga