Financial Functions and Formulas in Excel

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 (see Depreciation Calculator)
• 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 (see Compound Interest Formula)
• * 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 (see NPV Calculator)
• 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) (see the Investment Tracker for an example use)
• * XNPV - Net Present Value (not necessarily periodic)

Download the Simple Loan Calculator to see how to use the PMT, NPER, PV, and RATE functions.

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