Practice free

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

All financial functions

FunctionWhat it doesLevelSinceSpills?
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