# 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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])
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)
 number_of_periods (number) The number of payments to be made. payment_per_period (number) The amount per period to be paid.

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

 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)
 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)
 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)
 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)
 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)
 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)
 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)
 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)])
 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)
 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)
 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.