|
Excel Financial Functions
Home »
Excel-Built-In-Functions »
Excel-Financial-Functions
Search this site:
This page lists the current, built-in Excel Financial Functions. These functions have been provided
to perform many of the commonly used financial calculations, such as the calculation of yield,
interest rates, investment valuations, internal rate of return, payments and asset depreciation.
In the tables below, the financial functions have been grouped into categories, to help you to find the
function you need, to perform a specific task. Selecting a function name will take you to a full description
of the function with examples of use and details of common errors.
Excel Financial Functions List
|
Investment Value Functions
|
|
FV
|
Calculates the future value of an investment with periodic constant payments and a constant interest rate
|
|
FVSCHEDULE
|
Calculates the future value of an initial principal, after applying a series of compound interest rates
|
|
NPV
|
Calculates the net present value of an investment, based on a supplied discount rate,
and a series of future payments and income
|
|
PV
|
Calculates the present value of an investment
(ie. the total amount that a series of future payments is worth now)
|
|
RECEIVED
|
Calculates the amount received at maturity for a fully invested Security
|
|
XNPV
|
Calculates the net present value for a schedule of cash flows
|
|
Payment Functions
|
|
CUMIPMT
|
Calculates the cumulative interest paid between two specified periods
|
|
CUMPRINC
|
Calculates the cumulative principal paid on a loan, between two specified periods
|
|
IPMT
|
Calculates the interest payment for a given period of an investment, with periodic
constant payments and a constant interest rate
|
|
ISPMT
|
Returns the interest paid during a specified period of an investment
|
|
PMT
|
Calculates the payments required to reduce a loan, from a supplied present value to a specified future value
|
|
PPMT
|
Calculates the payment on the principal for a given investment, with periodic constant payments and a constant interest rate
|
|
Duration Functions
|
|
COUPDAYBS
|
Calculates the number of days from the beginning of the coupon period to the settlement date
|
|
COUPDAYS
|
Calculates the number of days in the coupon period that contains the settlement date
|
|
COUPDAYSNC
|
Calculates the number of days from the settlement date to the next coupon date
|
|
COUPNCD
|
Returns the next coupon date after the settlement date
|
|
COUPNUM
|
Returns the number of coupons payable between the settlement date and maturity date
|
|
COUPPCD
|
Returns the previous coupon date, before the settlement date
|
|
DURATION
|
Calculates the Macauley duration of a security with an assumed par value of $100
|
|
MDURATION
|
Calculates the Macauley modified duration for a security with an assumed par value of $100
|
|
NPER
|
Returns the number of periods for an investment with periodic constant payments and a constant interest rate
|
|
Yield Functions
|
|
YIELD
|
Calculates the yield of a security that pays periodic interest
|
|
YIELDDISC
|
Calculates the annual yield of a discounted security
|
|
YIELDMAT
|
Calculates the annual yield of a security that pays interest at maturity
|
|
ODDFYIELD
|
Calculates the yield of a security with an odd first period
|
|
ODDLYIELD
|
Calculates the yield of a security with an odd last period
|
|
TBILLEQ
|
Calculates the bond-equivalent yield for a treasury bill
|
|
TBILLYIELD
|
Calculates the yield for a treasury bill
|
|
|
Internal Rate of Return Functions
|
|
IRR
|
Calculates the internal rate of return for a series of cash flows
|
|
MIRR
|
Calculates the internal rate of return for a series of periodic cash flows,
considering the cost of the investment and the interest on the reinvestment of cash
|
|
XIRR
|
Calculates the internal rate of return for a schedule of cash flows
|
|
Asset Depreciation Functions
|
|
AMORDEGRC
|
Calculates the prorated linear depreciation of an asset for each accounting period
(with depreciation coefficient applied, depending on the life of the asset)
|
|
AMORLINC
|
Calculates the prorated linear depreciation of an asset for each accounting period
|
|
DB
|
Calculates the depreciation of an asset for a specified period, using the
fixed-declining balance method
|
|
DDB
|
Calculates the depreciation of an asset for a specified period, using the
double-declining balance method, or some other user-specified method
|
|
SLN
|
Returns the straight-line depreciation of an asset for one period
|
|
SYD
|
Returns the sum-of-years' digits depreciation of an asset for a specified period
|
|
VDB
|
Returns the depreciation of an asset for a specified period, (including partial periods),
using the double-declining balance method or another user-specified method
|
|
Interest Rate Functions
|
|
ACCRINT
|
Calculates the accrued interest for a security that pays periodic interest
|
|
ACCRINTM
|
Calculates the accrued interest for a security that pays interest at maturity
|
|
DISC
|
Calculates the discount rate for a security
|
|
EFFECT
|
Calculates the effective annual interest rate
|
|
INTRATE
|
Calculates the interest rate for a fully invested security
|
|
NOMINAL
|
Calculates the annual nominal interest rate
|
|
RATE
|
Calculates the interest rate required to pay off a specified amount of a loan, or
reach a target amount on an investment over a given period
|
|
Dollar Conversion
|
|
DOLLARDE
|
Converts a dollar price expressed as a fraction, into a dollar price expressed as a decimal
|
|
DOLLARFR
|
Converts a dollar price expressed as a decimal, into a dollar price expressed as a fraction
|
|
Price Functions
|
|
PRICE
|
Calculates the price per $100 face value of a security that pays periodic interest
|
|
PRICEDISC
|
Calculates the price per $100 face value of a discounted security
|
|
PRICEMAT
|
Calculates the price per $100 face value of a security that pays interest at maturity
|
|
ODDFPRICE
|
Calculates the price per $100 face value of a security with an odd first period
|
|
ODDLPRICE
|
Calculates the price per $100 face value of a security with an odd last period
|
|
TBILLPRICE
|
Calculates the price per $100 face value for a treasury bill
|
|
Further details of the Excel Financial functions are provided on the
Microsoft Office website.
Also, there is and excellent article showing practical examples of the
NPV and IRR functions.
|
