This reference lists 55 Excel financial functions, ordered by frequency of use, with high-frequency functions marked ⭐. Each function includes its syntax and an example.
| Function | Description | Example |
|---|---|---|
FV ⭐ |
Returns the future value of an investment | =FV(rate,nper,pmt,pv,type) — Returns the future value of a periodic investment after nper periods |
PMT ⭐ |
Returns the periodic payment for an annuity | =PMT(rate,nper,pv,fv,type) — Returns the periodic payment of a loan |
ACCRINT |
Returns the accrued interest for a security that pays periodic interest | =ACCRINT(issue,first_interest,settlement,rate,par, frequency) — Returns the accrued interest from the last coupon date to settlement |
ACCRINTM |
Returns the accrued interest for a security that pays interest at maturity | =ACCRINTM(issue,settlement,rate,par) — Returns the accrued interest from issue to settlement for a security at maturity |
AMORDEGRC |
Returns the depreciation for each accounting period by using a depreciation coefficient | =AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate,basis) — Returns depreciation using the French accounting system |
AMORLINC |
Returns the depreciation for each accounting period | =AMORLINC(cost,date_purchased,first_period,salvage,period,rate,basis) — Returns the depreciation of each period using the linear method |
COUPDAYBS |
Returns the number of days from the beginning of the coupon period to the settlement date | =COUPDAYBS(settlement,maturity,frequency,basis) — Returns the days from the start of the coupon period to settlement |
COUPDAYS |
Returns the number of days in the coupon period that contains the settlement date | =COUPDAYS(settlement,maturity,frequency,basis) — Returns the number of days in the coupon period |
COUPDAYSNC |
Returns the number of days from the settlement date to the next coupon date | =COUPDAYSNC(settlement,maturity,frequency,basis) — Returns the days from settlement to the next coupon date |
COUPNCD |
Returns the next coupon date after the settlement date | =COUPNCD(settlement,maturity,frequency,basis) — Returns the next coupon date after settlement |
COUPNUM |
Returns the number of coupons payable between the settlement date and maturity date | =COUPNUM(settlement,maturity,frequency,basis) — Returns the number of coupons between settlement and maturity |
COUPPCD |
Returns the previous coupon date before the settlement date | =COUPPCD(settlement,maturity,frequency,basis) — Returns the previous coupon date before settlement |
CUMIPMT |
Returns the cumulative interest paid between two periods | =CUMIPMT(rate,nper,pv,start_period,end_period,type) — Returns the cumulative interest paid on a loan from start to end period |
CUMPRINC |
Returns the cumulative principal paid on a loan between two periods | =CUMPRINC(rate,nper,pv,start_period,end_period,type) — Returns the cumulative principal paid on a loan from start to end period |
DB |
Returns the depreciation of an asset for a specified period by using the fixed-declining balance method | =DB(cost,salvage,life,period,month) — Returns the depreciation of a period using the fixed declining balance method |
DDB |
Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify | =DDB(cost,salvage,life,period,factor) — Returns depreciation using the double declining balance method |
DISC |
Returns the discount rate for a security | =DISC(settlement,maturity,price,redemption,basis) — Returns the discount rate of a security |
DOLLARDE |
Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number | =DOLLARDE(1.05,8) — Converts 1 5/8 dollars to the decimal 1.0625 |
DOLLARFR |
Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction | =DOLLARFR(1.625,8) — Converts 1.625 dollars to the fraction 1 5/8 |
DURATION |
Returns the annual duration of a security with periodic interest payments | =DURATION(settlement,maturity,coupon,yield,frequency,basis) — Returns the Macauley duration of a bond with periodic coupons |
EFFECT |
Returns the effective annual interest rate | =EFFECT(nominal_rate,npery) — Converts a nominal annual rate to the effective annual rate |
FVSCHEDULE |
Returns the future value of an initial principal after applying a series of compound interest rates | =FVSCHEDULE(principal,schedule) — Returns the future value of principal compounded at a series of rates |
INTRATE |
Returns the interest rate for a fully invested security | =INTRATE(settlement,maturity,investment,redemption,basis) — Returns the interest rate of a fully invested security |
IPMT |
Returns the interest payment for an investment for a given period | =IPMT(rate,per,nper,pv,fv,type) — Returns the interest portion of the loan payment in period per |
IRR |
Returns the internal rate of return for a series of cash flows | =IRR(values,guess) — Returns the internal rate of return of a series of cash flows |
ISPMT |
Calculates the interest paid during a specific period of an investment | =ISPMT(rate,per,nper,pv) — Returns the interest paid in a given period of an investment |
MDURATION |
Returns the Macauley modified duration for a security with an assumed par value of $100 | =MDURATION(settlement,maturity,coupon,yield,frequency,basis) — Returns the modified Macauley duration of a bond |
MIRR |
Returns the internal rate of return where positive and negative cash flows are financed at different rates | =MIRR(values,finance_rate,reinvest_rate) — Returns the modified internal rate of return, factoring in the reinvestment rate |
NOMINAL |
Returns the annual nominal interest rate | =NOMINAL(effect_rate,npery) — Converts the effective annual rate to the nominal annual rate |
NPER |
Returns the number of periods for an investment | =NPER(rate,pmt,pv,fv,type) — Returns the number of periods needed to reach a target value |
NPV |
Returns the net present value of an investment based on a series of periodic cash flows and a discount rate | =NPV(rate,value1,value2,...) — Returns the net present value of cash flows at the given discount rate |
ODDFPRICE |
Returns the price per $100 face value of a security with an odd first period | =ODDFPRICE(settlement,maturity,issue,first_coupon,rate,yield,redemption,frequency,basis) — Returns the price of a security with an odd (short or long) first period |
ODDFYIELD |
Returns the yield of a security with an odd first period | =ODDFYIELD(settlement,maturity,issue,first_coupon,rate,price,redemption,frequency,basis) — Returns the yield of a security with an odd first period |
ODDLPRICE |
Returns the price per $100 face value of a security with an odd last period | =ODDLPRICE(settlement,maturity,last_interest,rate,yield,redemption,frequency,basis) — Returns the price of a security with an odd last period |
ODDLYIELD |
Returns the yield of a security with an odd last period | =ODDLYIELD(settlement,maturity,last_interest,rate,price,redemption,frequency,basis) — Returns the yield of a security with an odd last period |
PDURATION |
Returns the number of periods required by an investment to reach a specified value | =PDURATION(rate,pv,fv) — Returns the periods needed for an investment to grow from present to target value |
PPMT |
Returns the payment on the principal for an investment for a given period | =PPMT(rate,per,nper,pv,fv,type) — Returns the principal portion of the loan payment in period per |
PRICE |
Returns the price per $100 face value of a security that pays periodic interest | =PRICE(settlement,maturity,rate,yield,redemption,frequency,basis) — Returns the price of a bond that pays periodic interest |
PRICEDISC |
Returns the price per $100 face value of a discounted security | =PRICEDISC(settlement,maturity,discount,redemption,basis) — Returns the price of a discounted security |
PRICEMAT |
Returns the price per $100 face value of a security that pays interest at maturity | =PRICEMAT(settlement,maturity,issue,rate,yield,basis) — Returns the price of a security that pays interest at maturity |
PV |
Returns the present value of an investment | =PV(rate,nper,pmt,fv,type) — Returns the present value of future cash flows |
RATE |
Returns the interest rate per period of an annuity | =RATE(nper,pmt,pv,fv,type,guess) — Returns the periodic interest rate of an annuity |
RECEIVED |
Returns the amount received at maturity for a fully invested security | =RECEIVED(settlement,maturity,investment,discount,basis) — Returns the amount received at maturity for a fully invested security |
RRI |
Returns an equivalent interest rate for the growth of an investment | =RRI(nper,pv,fv) — Returns the equivalent interest rate for an investment to reach a target value |
SLN |
Returns the straight-line depreciation of an asset for one period | =SLN(cost,salvage,life) — Returns the yearly straight-line depreciation |
SYD |
Returns the sum-of-years' digits depreciation of an asset for a specified period | =SYD(cost,salvage,life,period) — Returns the depreciation of a period using the sum-of-years method |
TBILLEQ |
Returns the bond-equivalent yield for a Treasury bill | =TBILLEQ(settlement,maturity,discount) — Returns the bond-equivalent annual yield of a Treasury bill |
TBILLPRICE |
Returns the price per $100 face value for a Treasury bill | =TBILLPRICE(settlement,maturity,discount) — Returns the price of a Treasury bill |
TBILLYIELD |
Returns the yield for a Treasury bill | =TBILLYIELD(settlement,maturity,price) — Returns the yield of a Treasury bill |
VDB |
Returns the depreciation of an asset for a specified or partial period by using a declining balance method | =VDB(cost,salvage,life,start_period,end_period,factor,no_switch) — Returns the depreciation of any given period |
XIRR |
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic | =XIRR(values,dates,guess) — Returns the internal rate of return of irregular cash flows |
XNPV |
Returns the net present value for a schedule of cash flows that is not necessarily periodic | =XNPV(rate,values,dates) — Returns the net present value of irregular cash flows |
YIELD |
Returns the yield on a security that pays periodic interest | =YIELD(settlement,maturity,rate,price,redemption,frequency,basis) — Returns the yield of a bond that pays periodic interest |
YIELDDISC |
Returns the annual yield for a discounted security; for example, a Treasury bill | =YIELDDISC(settlement,maturity,price,redemption,basis) — Returns the annual yield of a discounted security |
YIELDMAT |
Returns the annual yield of a security that pays interest at maturity | =YIELDMAT(settlement,maturity,issue,rate,price,basis) — Returns the annual yield of a security that pays interest at maturity |
From Online Toolbox (www.vba.net)