Financial functions
Financial functions handle loans, investments, depreciation and bond math. They follow standard finance formulas, so sign conventions matter.
56 functions in this category.
Start here
- PMT
Calculates the level payment needed to pay off a loan at a fixed rate. The classic tool for mortgage, car and personal loan quotes.
- NPV
Discounts a series of future cash flows back to today. Used to judge whether an investment is worth its cost.
- PV
Finds today's worth of a series of future payments, for instance how large a loan a given monthly payment can support.
- FV
Projects what regular deposits plus any starting balance will grow to at a fixed interest rate. A basic tool for savings and retirement projections.
- RATE
Solves for the interest rate hidden in a loan or annuity. Multiply by 12 for a nominal annual rate when payments are monthly.
- IRR
Finds the discount rate that makes a series of evenly spaced cash flows break even in present-value terms. Analysts use it to compare projects and investments.
All financial functions
| Function | What it does | Level | Since | Spills? |
|---|---|---|---|---|
ACCRINT | Works out how much interest has built up on a bond between its issue date and the settlement date. Useful when pricing or booking a bond bought between coupon payments. | Expert | 2007 or earlier | No |
ACCRINTM | Calculates the interest earned from issue to maturity on a bond or note that pays all its interest in one lump at the end. Handy for discount notes and short-term paper. | Expert | 2007 or earlier | No |
AMORDEGRC | Gives a period's depreciation using an accounting method common in France, where the rate is boosted by a coefficient tied to asset life. Rarely needed unless you report under French rules. | Expert | 2007 or earlier | No |
AMORLINC | Returns a period's straight-line depreciation with the first, partial period prorated by days. It follows French accounting conventions and mirrors SLN for full periods. | Expert | 2007 or earlier | No |
COUPDAYBS | Counts the days between the start of the current coupon period and the settlement date. Used to work out accrued interest on a bond bought mid-period. | Expert | 2007 or earlier | No |
COUPDAYS | Tells you how many days are in the coupon period that contains the settlement date, under the chosen day-count basis. | Expert | 2007 or earlier | No |
COUPDAYSNC | Counts days from settlement to the next coupon payment. Handy for discounting the next cash flow of a bond. | Expert | 2007 or earlier | No |
COUPNCD | Finds the next coupon payment date after you buy a bond. The result is a date serial number, so format the cell as a date. | Expert | 2007 or earlier | No |
COUPNUM | Returns how many coupons remain between settlement and maturity, rounded up to a whole number. Useful for building a bond cash-flow schedule. | Expert | 2007 or earlier | No |
COUPPCD | Finds the coupon date that came before the settlement date. The result is a date serial, so apply a date format to read it. | Expert | 2007 or earlier | No |
CUMIPMT | Adds up the interest portion of a loan's payments over a range of periods, for example the interest paid in year two of a mortgage. | Intermediate | 2007 or earlier | No |
CUMPRINC | Adds up the principal portion of a loan's payments over a range of periods, showing how much debt you cut down in that stretch. | Intermediate | 2007 or earlier | No |
DB | Applies a constant rate to the asset's remaining book value each period, so deductions are larger early on. A standard choice for accelerated depreciation. | Intermediate | 2007 or earlier | No |
DDB | Depreciates an asset at a multiple of the straight-line rate, so early periods get the biggest write-offs. It never takes the book value below salvage. | Intermediate | 2007 or earlier | No |
DISC | Converts a bond's price and redemption value into a discount rate. Mostly used with treasury bills and other discount instruments. | Expert | 2007 or earlier | No |
DOLLARDE | Converts a quote written in fractional notation, such as 1.02 meaning one and two sixteenths, into a decimal. Used with older bond price quotes. | Intermediate | 2007 or earlier | No |
DOLLARFR | Turns a decimal price into fractional notation, so 1.125 with a denominator of 16 becomes 1.02. This is the reverse of DOLLARDE. | Intermediate | 2007 or earlier | No |
DURATION | Measures the weighted average time until a bond's cash flows arrive, in years. Bond analysts use it to gauge sensitivity to interest-rate moves. | Expert | 2007 or earlier | No |
EFFECT | Converts a stated annual rate into the true yearly rate once compounding is included. Useful for comparing loans or savings that compound at different frequencies. | Intermediate | 2007 or earlier | No |
FV | Projects what regular deposits plus any starting balance will grow to at a fixed interest rate. A basic tool for savings and retirement projections. | Beginner | 2007 or earlier | No |
FVSCHEDULE | Compounds a starting amount through a list of different rates, one per period. Good for variable-rate investments where each year's rate is different. | Intermediate | 2007 or earlier | No |
INTRATE | Finds the simple annual rate earned when you pay a set amount now and receive a larger amount at maturity, as with a zero-coupon note. | Expert | 2007 or earlier | No |
IPMT | Splits a single payment into its interest part. Pair it with PPMT to build a loan amortisation table. | Intermediate | 2007 or earlier | No |
IRR | Finds the discount rate that makes a series of evenly spaced cash flows break even in present-value terms. Analysts use it to compare projects and investments. | Beginner | 2007 or earlier | No |
ISPMT | Calculates interest on a loan where the principal is repaid in equal chunks, so interest shrinks in a straight line. It differs from IPMT, which assumes level payments. | Intermediate | 2007 or earlier | No |
MDURATION | Gives Macaulay duration divided by one plus the periodic yield. It estimates the percentage price change for a one-point change in yield. | Expert | 2007 or earlier | No |
MIRR | A more realistic cousin of IRR that lets you set separate borrowing and reinvestment rates. It always gives a single answer. | Intermediate | 2007 or earlier | No |
NOMINAL | Converts an effective yearly rate back to the quoted nominal rate for a given compounding frequency. The reverse of EFFECT. | Intermediate | 2007 or earlier | No |
NPER | Tells you how many payments it takes to pay off a loan or reach a savings goal. Often used to see the effect of paying more each month. | Beginner | 2007 or earlier | No |
NPV | Discounts a series of future cash flows back to today. Used to judge whether an investment is worth its cost. | Beginner | 2007 or earlier | No |
ODDFPRICE | Prices a bond whose first coupon period is shorter or longer than normal. Bond desks use it for new issues that start mid-cycle. | Expert | 2007 or earlier | No |
ODDFYIELD | Works backwards from a bond's price to its yield when the first coupon period is irregular. It is the inverse of ODDFPRICE. | Expert | 2007 or earlier | No |
ODDLPRICE | Prices a bond whose final coupon period is shorter or longer than the regular one. Seen with bonds that mature off-cycle. | Expert | 2007 or earlier | No |
ODDLYIELD | Turns a price into a yield for a bond whose final coupon period is irregular. It is the inverse of ODDLPRICE. | Expert | 2007 or earlier | No |
PDURATION | Answers how long it takes money to grow from one amount to another at a fixed rate, with no regular payments. Simpler than NPER for lump sums. | Intermediate | 2013 | No |
PMT | Calculates the level payment needed to pay off a loan at a fixed rate. The classic tool for mortgage, car and personal loan quotes. | Beginner | 2007 or earlier | No |
PPMT | Shows how much of a given payment reduces the loan balance. Combine with IPMT for an amortisation schedule. | Intermediate | 2007 or earlier | No |
PRICE | Values a regular coupon bond for a given yield. It is the standard way to find a bond's clean price in Excel. | Expert | 2007 or earlier | No |
PRICEDISC | Prices a zero-coupon instrument such as a treasury bill from its discount rate. It is the inverse of DISC. | Expert | 2007 or earlier | No |
PRICEMAT | Prices a bond or note that pays all its interest on the maturity date. Used for money-market style instruments. | Expert | 2007 or earlier | No |
PV | Finds today's worth of a series of future payments, for instance how large a loan a given monthly payment can support. | Beginner | 2007 or earlier | No |
RATE | Solves for the interest rate hidden in a loan or annuity. Multiply by 12 for a nominal annual rate when payments are monthly. | Beginner | 2007 or earlier | No |
RECEIVED | Finds how much you collect at maturity on a short-term security bought at a given discount rate. | Expert | 2007 or earlier | No |
RRI | Returns the steady growth rate that takes a value from start to end over a number of periods, like CAGR for revenue or investments. | Intermediate | 2013 | No |
SLN | Spreads an asset's cost minus salvage evenly over its useful life. The simplest and most common depreciation method. | Intermediate | 2007 or earlier | No |
STOCKHISTORY | Pulls daily, weekly or monthly price history for a stock or fund straight into the sheet. Handy for quick charts and returns analysis without a data export. | Advanced | 365 only | Yes |
SYD | Gives accelerated depreciation that falls steadily each year, based on the sum of the years in the asset's life. | Intermediate | 2007 or earlier | No |
TBILLEQ | Converts a treasury bill's discount rate into a yield comparable with bonds, on an annual basis. | Expert | 2007 or earlier | No |
TBILLPRICE | Prices a treasury bill from its discount rate, using an actual-days over 360 convention. | Expert | 2007 or earlier | No |
TBILLYIELD | Derives a treasury bill's yield from its price, on an actual-days over 360 basis. | Expert | 2007 or earlier | No |
VDB | Variable declining balance depreciation that can cover partial periods and switch to straight-line when that gives a bigger deduction. | Intermediate | 2007 or earlier | No |
XIRR | Like IRR but uses the actual date of each cash flow, so it suits irregular investments such as deposits and withdrawals on arbitrary days. | Intermediate | 2007 or earlier | No |
XNPV | Discounts irregularly timed cash flows using actual dates. Better than NPV when flows are not evenly spaced. | Intermediate | 2007 or earlier | No |
YIELD | Works out the yield to maturity of a coupon bond from its price. The reverse of PRICE. | Expert | 2007 or earlier | No |
YIELDDISC | Calculates the yield on a zero-coupon security from its price and redemption value. | Expert | 2007 or earlier | No |
YIELDMAT | Gives the yield of a bond or note that pays all its interest at maturity, based on its price. The inverse of PRICEMAT. | Expert | 2007 or earlier | No |
Other categories
- Lookup and reference functions
43 functions
- Math and trig functions
80 functions
- Logical functions
19 functions
- Text functions
48 functions
- Date and time functions
25 functions
- Statistical functions
111 functions
- Information functions
21 functions
- Database functions
12 functions
- Engineering functions
54 functions
- Web functions
3 functions
- Compatibility functions
41 functions
- Cube functions
7 functions
- Add-in and automation functions
5 functions