Financial Functions

Cash Flow Functions. Interest Rate. Securities. Depreciation & Amortisation Functions. Coupon Date Functions. Dollar Conversion. Treasury Bill Functions.
  • ACCRINT

    Calculates the accrued interest of a security that has periodic payments.

    ACCRINT(DATE(2010,01,01),DATE(2010,02,01),DATE(2012,12,31),0.05,100,4)
    Try this example
    issue (date)
    The date the security was initially issued.
    first_payment (date)
    The first date interest will be paid.
  • ACCRINTM

    Calculates the accrued interest of a security that pays interest at maturity.

    ACCRINTM(DATE(1969,12,31),DATE(1999,12,31),0.05,100,0)
    Try this example
    issue (date)
    The date the security was initially issued.
    maturity (date)
    The maturity date of the security.
  • AMORDEGRC

    Returns the depreciation for a given accounting using a depreciation coefficient determined by asset life.

    AMORDEGRC(10000,DATE(2019,6,30),DATE(2019,12,31),1000,3,0.2,4)
    Try this example
    cost (number)
    Asset cost.
    purchase (date)
    Asset purchase date.
  • AMORLINC

    The AMORLINC function returns the depreciation for an accounting period, or the prorated depreciation if the asset was purchased in the middle of a period.This function is available for users of the French accounting system.

    AMORLINC(1234.56, DATE(1969, 7, 20), DATE(1969, 8, 20), 123.45, 6.5, 0.15, 1)
    Try this example
    cost (number)
    The asset's purchase cost
    purchase_date (date)
    The date the asset was purchased - The purchase date should be before the first period end date.
  • COUPDAYBS

    Calculates the number of days from the first coupon, or interest payment, until settlement.

    COUPDAYBS(DATE(2010,02,01),DATE(2019,12,31),4)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • COUPDAYS

    Calculates the number of days in the coupon, or interest payment, period that contains the specified settlement date.

    COUPDAYS(DATE(2010,02,01),DATE(2019,12,31),4)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • COUPDAYSNC

    Calculates the number of days from the settlement date until the next coupon, or interest payment.

    COUPDAYSNC(DATE(2010,02,01),DATE(2019,12,31),4)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • COUPNCD

    Calculates next coupon, or interest payment, date after the settlement date.

    COUPNCD(DATE(2010,02,01),DATE(2019,12,31),4)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • COUPNUM

    Calculates the number of coupons, or interest payments, between the settlement date and the maturity date of the investment.

    COUPNUM(DATE(2010,02,01),DATE(2019,12,31),4)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • COUPPCD

    Calculates last coupon, or interest payment, date before the settlement date.

    COUPPCD(DATE(2010,02,01),DATE(2019,12,31),4)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • CUMIPMT

    Calculates the cumulative interest over a range of payment periods for an investment based on constant-amount periodic payments and a constant interest rate.

    CUMIPMT(0.12,12,100,1,5,0)
    Try this example
    rate (number)
    The interest rate.
    number_of_periods (number)
    The number of payments to be made.
  • CUMPRINC

    Calculates the cumulative principal paid over a range of payment periods for an investment based on constant-amount periodic payments and a constant interest rate.

    CUMPRINC(0.12,12,100,1,5,0)
    Try this example
    rate (number)
    The interest rate.
    number_of_periods (number)
    The number of payments to be made.
  • DB

    The DB function calculates the depreciation of an asset for a specified period using the arithmetic declining balance method.

    DB(100,50,10,2)
    Try this example
    cost (number)
    The initial cost of the asset.
    salvage (number)
    The value of the asset at the end of depreciation.
  • DDB

    The DDB function calculates the depreciation of an asset for a specified period using the double-declining balance method.

    DDB(100,50,10,2)
    Try this example
    cost (number)
    The initial cost of the asset.
    salvage (number)
    The value of the asset at the end of depreciation.
  • DISC

    Calculates the discount rate of a security based on price.

    DISC(DATE(2010,01,02),DATE(2039,12,31),90,100)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • DOLLARDE

    Converts a price quotation given as a decimal fraction into a decimal value.

    DOLLARDE(100.10,32)
    Try this example
    fractional_price (number)
    The price quotation given using fractional decimal conventions.
    unit (number)
    The units of the fraction, e.g. 8 for 1/8ths or 32 for 1/32nds.
  • DOLLARFR

    Converts a price quotation given as a decimal value into a decimal fraction.

    DOLLARFR(100.125,32)
    Try this example
    decimal_price (number)
    The price quotation given as a decimal value..
    unit (number)
    The units of the desired fraction, e.g. 8 for 1/8ths or 32 for 1/32nds.
  • EFFECT

    Calculates the annual effective interest rate given the nominal rate and number of compounding periods per year.

    EFFECT(0.99,12)
    Try this example
    nominal_rate (number)
    The nominal interest rate per year.
    periods_per_year (number)
    The number of compounding periods per year.
  • FV

    The FV function calculates the future value of an annuity investment based on constant-amount periodic payments and a constant interest rate.

    FV(2,12,100,400)
    Try this example
    rate (number)
    The interest rate.
    number_of_periods (number)
    The number of payments to be made.
  • FVSCHEDULE

    The FVSCHEDULE function calculates the future value of some principal based on a specified series of potentially varying interest rates.

    FVSCHEDULE(10000,[0.1,0.95,0.9,0.85])
    Try this example
    principal (number)
    The amount of initial capital or value to compound against.
    rate_schedule (array)
    A series of interest rates to compound against the principal.
  • INTRATE

    Calculates the effective interest rate generated when an investment is purchased at one price and sold at another with no interest or dividends generated by the investment itself.

    INTRATE(DATE(2010,01,02),DATE(2019,12,31),90,140,2)
    Try this example
    buy_date (date)
    The date of purchase of the investment.
    sell_date (date)
    The date of sale of the investment.
  • IPMT

    The IPMT function calculates the payment on interest for an investment based on constant-amount periodic payments and a constant interest rate.

    IPMT(0.05/12, 1, 30*12, 100000)
    Try this example
    rate (number)
    The interest rate.
    period (number)
    The amortization period, in terms of number of periods.
  • IRR

    Calculates the internal rate of return on an investment based on a series of periodic cash flows.

    IRR([-4000,200,250,300,350],0.1)
    Try this example
    cashflow_amounts (array)
    An array or range containing the income or payments associated with the investment.
    rate_guess (number)
    Optional - 0.1 by default - An estimate for what the internal rate of return will be.
  • ISPMT

    The ISPMT function calculates the interest paid during a particular period of an investment.

    ISPMT(15%, 2, 5, 1000)
    Try this example
    rate (string)
    The interest rate.
    period (number)
    The time frame for which you want to view the interest payment.
  • MIRR

    Calculates the modified internal rate of return on an investment based on a series of periodic cash flows and the difference between the interest rate paid on financing versus the return received on reinvested income.

    MIRR([-4000,200,250,300,350],0.08,0.11)
    Try this example
    cashflow_amounts (array)
    An array or range containing the income or payments associated with the investment.
    financing_rate (number)
    The interest rate paid on funds invested.
  • NOMINAL

    Calculates the annual nominal interest rate given the effective rate and number of compounding periods per year.

    NOMINAL(0.85,12)
    Try this example
    effective_rate (number)
    The effective interest rate per year.
    periods_per_year (number)
    The number of compounding periods per year.
  • NPER

    The NPER function calculates the number of payment periods for an investment based on constant-amount periodic payments and a constant interest rate.

    NPER(2,500,40000)
    Try this example
    rate (number)
    The interest rate.
    payment_amount (number)
    The amount of each payment made.
  • NPV

    Calculates the net present value of an investment based on a series of periodic cash flows and a discount rate.

    NPV(0.08,200,250,300)
    Try this example
    discount (number)
    The discount rate of the investment over one period.
    cashflow1 (number)
    The first future cash flow.
  • PDURATION

    The PDURATION function returns the number of periods for an investment to reach a specific value at a given rate. Mathematically, PDURATION = (log(futureValue) - log (currentValue))/(log(1 + rate)).

    PDURATION(0.25, 10, 15)
    Try this example
    rate (number)
    The rate at which the investment grows each period.
    present_valuepresent_value (number)
    The investment's current value.
  • PMT

    The PMT function calculates the periodic payment for an annuity investment based on constant-amount periodic payments and a constant interest rate.

    PMT(0.05/12, 30*12, 100000)
    Try this example
    rate (number)
    The interest rate.
    number_of_periods (number)
    The number of payments to be made.
  • PPMT

    The PPMT function calculates the payment on the principal of an investment based on constant-amount periodic payments and a constant interest rate.

    PPMT(0.05/12, 1, 30*12, 100000)
    Try this example
    rate (number)
    The interest rate.
    period (number)
    The amortization period, in terms of number of periods.
  • PRICE

    Calculates the price of a security paying periodic interest, such as a US Treasury Bond, based on expected yield.

    PRICE(DATE(2010,1,2),DATE(2039,12,31),3,1.2,100,2,0)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • PRICEDISC

    Calculates the price of a discount (non-interest-bearing) security, based on expected yield.

    PRICEDISC(DATE(2010,01,02),DATE(2039,12,31),3,100)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • PRICEMAT

    Calculates the price of a security paying interest at maturity, based on expected yield.

    PRICEMAT(DATE(2010,01,02),DATE(2039,12,31),DATE(2010,01,01),3,1.2)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • PV

    Calculates the present value of an annuity investment based on constant-amount periodic payments and a constant interest rate.

    PV(2,12,100)
    Try this example
    rate (number)
    The interest rate.
    number_of_periods (number)
    The number of payments to be made.
  • RATE

    Calculates the interest rate of an annuity investment based on constant-amount periodic payments and the assumption of a constant interest rate.

    RATE(12,-100,400,0,0,0.1)
    Try this example
    number_of_periods (number)
    The number of payments to be made.
    payment_per_period (number)
    The amount per period to be paid.
  • RECEIVED

    Calculates the amount received at maturity for an investment in fixed-income securities purchased on a given date.

    RECEIVED(DATE(2010,02,01),DATE(2019,12,31),1000,0.05)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • RRI

    The RRI function returns the interest rate needed for an investment to reach a specific value within a given number of periods.

    RRI(10.5, 10, 3)
    Try this example
    number_of_periods (number)
    The number of periods.
    present_value (number)
    The present value of the investment
  • SLN

    The SLN function calculates the depreciation of an asset for one period using the straight-line method.

    SLN(100,50,10)
    Try this example
    cost (number)
    The initial cost of the asset.
    salvage (number)
    The value of the asset at the end of depreciation.
  • SYD

    The SYD function calculates the depreciation of an asset for a specified period using the sum of years digits method.

    SYD(100,50,10,2)
    Try this example
    cost (number)
    The initial cost of the asset.
    salvage (number)
    The value of the asset at the end of depreciation.
  • TBILLEQ

    Calculates the equivalent annualized rate of return of a US Treasury Bill based on discount rate.

    TBILLEQ(DATE(2010,1,2), DATE(2010,12,31), .09)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • TBILLPRICE

    Calculates the price of a US Treasury Bill based on discount rate.

    TBILLPRICE(DATE(2010,1,2), DATE(2010,12,31), .0125)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • TBILLYIELD

    Calculates the yield of a US Treasury Bill based on price.

    TBILLYIELD(DATE(2010,1,2), DATE(2010,12,31), 98.45)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.
  • XIRR

    Calculates the internal rate of return of an investment based on a specified series of potentially irregularly spaced cash flows.

    XIRR([-4000,200,250,300],[DATE(2012,01,01),DATE(2012,06,23),DATE(2013,05,12),DATE(2014,02,09)],0.09)
    Try this example
    cashflow_amounts (array)
    An array or range containing the income or payments associated with the investment.
    cashflow_dates (array)
    An array or range with dates corresponding to the cash flows in cashflow_amounts.
  • XNPV

    Calculates the net present value of an investment based on a specified series of potentially irregularly spaced cash flows and a discount rate.

    XNPV(0.08,[200,250,300],[DATE(2012,06,23),DATE(2013,05,12),DATE(2014,02,09)])
    Try this example
    discount (number)
    The discount rate of the investment over one period.
    cashflow_amounts (array)
    A range of variables containing the income or payments associated with the investment.
  • YIELDDISC

    Calculates the annual yield of a discount (non-interest-bearing) security, based on price.

    YIELDDISC(DATE(2010,01,02),DATE(2010,12,31),98.45,100)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face, or par value.
  • YIELDMAT

    The YIELDMAT function calculates the annual yield of a security paying interest at maturity, based on price.

    YIELDMAT(DATE(2010,01,02),DATE(2039,12,31),DATE(2010,01,01),3,100.47)
    Try this example
    settlement (date)
    The settlement date of the security, the date after issuance when the security is delivered to the buyer.
    maturity (date)
    The maturity or end date of the security, when it can be redeemed at face or par value.