Financial Functions and Formulas in Excel

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