Financial
★ 45 · ◉ 30 · All versions
=XIRR()
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function.
=XIRR(values, dates, [guess])
Financial Calculations
Investment Appraisal
Financial
★ 50 · ◉ 25 · All versions
=RATE()
Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.
=RATE(nper, pmt, pv, [fv], [type], [guess])
Financial Calculations
Loans & Installments
Financial
★ 5 · ◉ 23 · All versions
=ACCRINTM()
Returns the accrued interest for a security that pays interest at maturity.
=ACCRINTM(issue, settlement, rate, par, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 30 · ◉ 22 · All versions
=CUMIPMT()
Returns the cumulative interest paid on a loan between start_period and end_period.
=CUMIPMT(rate, nper, pv, start_period, end_period, type)
Financial Calculations
Loans & Installments
Financial
★ 25 · ◉ 22 · All versions
=SYD()
Returns the sum-of-years' digits depreciation of an asset for a specified period.
=SYD(cost, salvage, life, per)
Financial Calculations
Depreciation
Date & Time
★ 35 · ◉ 22 · All versions
=YEARFRAC()
YEARFRAC calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). For instance, you can use YEARFRAC to identify the proportion of a whole year's benefits, or obligations to assign to a specific term.
=YEARFRAC(start_date, end_date, [basis])
Date & Time Calculations
Workdays & Due Dates
Date & Time
★ 15 · ◉ 21 · All versions
=DAYS360()
The DAYS360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.
=DAYS360(start_date,end_date,[method])
Date & Time Calculations
Workdays & Due Dates
Financial
★ 60 · ◉ 20 · All versions
=PMT()
How to use the PMT function in Excel to calculate monthly loan payments based on constant payments and a constant interest rate.
=PMT(rate, nper, pv, [fv], [type])
Financial Calculations
Loans & Installments
Financial
★ 4 · ◉ 20 · All versions
=TBILLYIELD()
Returns the yield for a Treasury bill.
=TBILLYIELD(settlement, maturity, pr)
Financial Calculations
Securities & Bonds
Financial
★ 3 · ◉ 19 · All versions
=ODDLPRICE()
Returns the price per $100 face value of a security having an odd (short or long) last coupon period.
=ODDLPRICE(settlement, maturity, last_interest, rate, yld, redemption, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 30 · ◉ 18 · All versions
=NOMINAL()
Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year.
=NOMINAL(effect_rate, npery)
Financial Calculations
Loans & Installments
Financial
★ 4 · ◉ 17 · All versions
=DOLLARDE()
Converts a dollar price expressed as an integer part and a fraction part, such as 1.02, into a dollar price expressed as a decimal number. Fractional dollar numbers are sometimes used for security prices. The fraction part of the value is divided by an integer that you specify. For example, if you want your price to be expressed to a precision of 1/16 of a dollar, you divide the fraction part by 16. In this case, 1.02 represents $1.125 ($1 + 2/16 = $1.125).
=DOLLARDE(fractional_dollar, fraction)
Financial Calculations
Securities & Bonds
Financial
★ 30 · ◉ 17 · All versions
=MIRR()
Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of cash.
=MIRR(values, finance_rate, reinvest_rate)
Financial Calculations
Investment Appraisal
Financial
★ 45 · ◉ 17 · All versions
=PV()
PV, one of the financial functions, calculates the present value of a loan or an investment, based on a constant interest rate. You can use PV with either periodic, constant payments (such as a mortgage or other loan), or a future value that's your investment goal.
=PV(rate, nper, pmt, [fv], [type])
Financial Calculations
Loans & Installments
Financial
★ 40 · ◉ 17 · All versions
=XNPV()
Returns the net present value for a schedule of cash flows that is not necessarily periodic. To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
=XNPV(rate, values, dates)
Financial Calculations
Investment Appraisal
Financial
★ 8 · ◉ 16 · All versions
=DISC()
Returns the discount rate for a security.
=DISC(settlement, maturity, pr, redemption, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 35 · ◉ 16 · All versions
=EFFECT()
Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.
=EFFECT(nominal_rate, npery)
Financial Calculations
Loans & Installments
Financial
★ 3 · ◉ 16 · All versions
=TBILLEQ()
Returns the bond-equivalent yield for a Treasury bill.
=TBILLEQ(settlement, maturity, discount)
Financial Calculations
Securities & Bonds
Financial
★ 30 · ◉ 15 · All versions
=DB()
Returns the depreciation of an asset for a specified period using the fixed-declining balance method.
=DB(cost, salvage, life, period, [month])
Financial Calculations
Depreciation
Financial
★ 10 · ◉ 15 · All versions
=ISPMT()
Calculates the interest paid (or received) for the specified period of a loan (or investment) with even principal payments.
=ISPMT(rate, per, nper, pv)
Financial Calculations
Loans & Installments
Financial
★ 45 · ◉ 15 · All versions
=NPER()
Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
=NPER(rate,pmt,pv,[fv],[type])
Financial Calculations
Loans & Installments
Financial
★ 30 · ◉ 15 · Excel 2013
=RRI()
Returns an equivalent interest rate for the growth of an investment.
=RRI(nper, pv, fv)
Forecasting & Trends
Financial Calculations
Financial
★ 4 · ◉ 14 · All versions
=COUPDAYBS()
The COUPDAYBS function returns the number of days from the beginning of a coupon period until its settlement date.
=COUPDAYBS(settlement, maturity, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 30 · ◉ 14 · All versions
=DDB()
Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify.
=DDB(cost, salvage, life, period, [factor])
Financial Calculations
Depreciation
Financial
★ 10 · ◉ 14 · All versions
=DURATION()
Returns the Macauley duration for an assumed par value of $100. Duration is defined as the weighted average of the present value of cash flows, and is used as a measure of a bond price's response to changes in yield.
=DURATION(settlement, maturity, coupon, yld, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 45 · ◉ 14 · All versions
=IPMT()
Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
=IPMT(rate, per, nper, pv, [fv], [type])
Financial Calculations
Loans & Installments
Financial
★ 3 · ◉ 14 · All versions
=ODDFYIELD()
Returns the yield of a security that has an odd (short or long) first period.
=ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 20 · ◉ 14 · All versions
=VDB()
Returns the depreciation of an asset for any period you specify, including partial periods, using the double-declining balance method or some other method you specify. VDB stands for variable declining balance.
=VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])
Financial Calculations
Depreciation
Financial
★ 4 · ◉ 13 · All versions
=COUPDAYS()
Returns the number of days in the coupon period that contains the settlement date.
=COUPDAYS(settlement, maturity, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 45 · ◉ 13 · All versions
=FV()
FV, one of the financial functions, calculates the future value of an investment based on a constant interest rate. You can use FV with either periodic, constant payments, or a single lump sum payment.
=FV(rate,nper,pmt,[pv],[type])
Financial Calculations
Loans & Installments
Financial
★ 3 · ◉ 13 · All versions
=ODDLYIELD()
Returns the yield of a security that has an odd (short or long) last period.
=ODDLYIELD(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 45 · ◉ 13 · All versions
=PPMT()
Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
=PPMT(rate, per, nper, pv, [fv], [type])
Financial Calculations
Loans & Installments
Financial
★ 15 · ◉ 13 · All versions
=PRICE()
Returns the price per $100 face value of a security that pays periodic interest.
=PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 8 · ◉ 12 · All versions
=ACCRINT()
Returns the accrued interest for a security that pays periodic interest.
=ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])
Financial Calculations
Securities & Bonds
Financial
★ 4 · ◉ 12 · All versions
=COUPDAYSNC()
Returns the number of days from the settlement date to the next coupon date.
=COUPDAYSNC(settlement, maturity, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 8 · ◉ 12 · All versions
=MDURATION()
Returns the modified Macauley duration for a security with an assumed par value of $100.
=MDURATION(settlement, maturity, coupon, yld, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 15 · ◉ 12 · Excel 2013
=PDURATION()
Returns the number of periods required by an investment to reach a specified value.
=PDURATION(rate, pv, fv)
Financial Calculations
Investment Appraisal
Financial
★ 5 · ◉ 11 · All versions
=AMORDEGRC()
Returns the depreciation for each accounting period. This function is provided for the French accounting system. If an asset is purchased in the middle of the accounting period, the prorated depreciation is taken into account. The function is similar to AMORLINC, except that a depreciation coefficient is applied in the calculation depending on the life of the assets.
=AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, [basis])
Financial Calculations
Depreciation
Financial
★ 4 · ◉ 11 · All versions
=COUPNCD()
Returns a number that represents the next coupon date after the settlement date.
=COUPNCD(settlement, maturity, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 4 · ◉ 11 · All versions
=DOLLARFR()
Use DOLLARFR to convert decimal numbers to fractional dollar numbers, such as securities prices.
=DOLLARFR(decimal_dollar, fraction)
Financial Calculations
Securities & Bonds
Financial
★ 20 · ◉ 11 · All versions
=FVSCHEDULE()
Returns the future value of an initial principal after applying a series of compound interest rates. Use FVSCHEDULE to calculate the future value of an investment with a variable or adjustable rate.
=FVSCHEDULE(principal, schedule)
Financial Calculations
Investment Appraisal
Financial
★ 45 · ◉ 11 · All versions
=NPV()
Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
=NPV(rate,value1,[value2],...)
Financial Calculations
Investment Appraisal
Financial
★ 3 · ◉ 11 · All versions
=ODDFPRICE()
Returns the price per $100 face value of a security having an odd (short or long) first period.
=ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 8 · ◉ 11 · All versions
=PRICEDISC()
Returns the price per $100 face value of a discounted security.
=PRICEDISC(settlement, maturity, discount, redemption, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 40 · ◉ 11 · All versions
=SLN()
Returns the straight-line depreciation of an asset for one period.
=SLN(cost, salvage, life)
Financial Calculations
Depreciation
Financial
★ 5 · ◉ 10 · All versions
=INTRATE()
Returns the interest rate for a fully invested security.
=INTRATE(settlement, maturity, investment, redemption, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 5 · ◉ 10 · All versions
=RECEIVED()
Returns the amount received at maturity for a fully invested security.
=RECEIVED(settlement, maturity, investment, discount, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 5 · ◉ 10 · All versions
=YIELDMAT()
Returns the annual yield of a security that pays interest at maturity.
=YIELDMAT(settlement, maturity, issue, rate, pr, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 5 · ◉ 9 · All versions
=AMORLINC()
Returns the depreciation for each accounting period. This function is provided for the French accounting system. If an asset is purchased in the middle of the accounting period, the prorated depreciation is taken into account.
=AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis])
Financial Calculations
Depreciation
Financial
★ 4 · ◉ 9 · All versions
=COUPNUM()
Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon.
=COUPNUM(settlement, maturity, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 30 · ◉ 9 · All versions
=CUMPRINC()
Returns the cumulative principal paid on a loan between start_period and end_period.
=CUMPRINC(rate, nper, pv, start_period, end_period, type)
Financial Calculations
Loans & Installments
Financial
★ 15 · ◉ 9 · All versions
=YIELD()
Returns the yield on a security that pays periodic interest. Use YIELD to calculate bond yield.
=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 4 · ◉ 8 · All versions
=COUPPCD()
Returns a number that represents the previous coupon date before the settlement date.
=COUPPCD(settlement, maturity, frequency, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 45 · ◉ 8 · All versions
=IRR()
A number that you guess is close to the result of IRR.
=IRR(values, [guess])
Financial Calculations
Investment Appraisal
Financial
★ 5 · ◉ 8 · All versions
=PRICEMAT()
Returns the price per $100 face value of a security that pays interest at maturity.
=PRICEMAT(settlement, maturity, issue, rate, yld, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 8 · ◉ 7 · All versions
=YIELDDISC()
Returns the annual yield for a discounted security.
=YIELDDISC(settlement, maturity, pr, redemption, [basis])
Financial Calculations
Securities & Bonds
Financial
★ 4 · ◉ 6 · All versions
=TBILLPRICE()
Returns the price per $100 face value for a Treasury bill.
=TBILLPRICE(settlement, maturity, discount)
Financial Calculations
Securities & Bonds