Financial Functions

The functions that relate to financial calculations such as interest calculations are:

 

ACCRINT(issue,first,settle,rate,par,freq,basis)

This function calculates the accrued interest for a security that pays periodic interest.

argument

description

issue

Date that the security is issued

first

First date for calculating the interest for the security

settle

Settlement date for the security

rate

Annual interest rate for the security

par

[Optional] Par value for the security; if omitted, the calculation uses a value of $1,000

frequency

Frequency of payment, number of payments per year

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

example: ACCRINT(DATE(2003,1,1),DATE(2003,1,7),DATE(2005,1,7),0.5,1000,2) gives the result 1008.3333

 

ACCRINTM(issue,maturity,rate,par,basis)

This function calculates the accrued interest at maturity for a security that pays periodic interest.

argument

description

issue

Date that the security is issued

maturity

Maturity date for security

rate

Annual interest rate for the security

par

[Optional] Par value for the security; if omitted, the calculation uses a value of $1,000

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

 

AMORDEGRC(cost,datepurchased,firstperiod,salvage,period,drate,basis)

This function returns the depreciation for an accounting period, taking into consideration prorated depreciation, and applies a depreciation coefficient in the calculation based on the life of the assets.

argument

description

cost

Cost of the asset

datepurchased

Purchase date of the asset

firstperiod

End date of the first period

salvage

Salvage value at the end of the life of the asset

period

Accounting period

drate

Rate of depreciation

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis)

 

AMORLINC(cost,datepurchased,firstperiod,salvage,period,drate,basis)

This function calculates the depreciation for an accounting period, taking into account prorated depreciation.

 

COUPDAYS(settlement,maturity,frequency,basis)

This function returns the number of days in the coupon period that contains the settlement date.

argument

description

settlement

Settlement date for the security

maturity

Maturity date for the security

frequency

Frequency of payment, number of coupon payments per year; must be 1, 2, or 4

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

 

COUPDAYBS(settlement,maturity,frequency,basis)

This function calculates the number of days from the beginning of the coupon period to the settlement date.

 

COUPDAYSNC(settlement,maturity,frequency,basis)

This function calculates the number of days from the settlement date to the next coupon date.

 

COUPNCD(settlement,maturity,frequency,basis)

This function returns a date number of the next coupon date after the settlement date.

 

COUPNUM(settlement,maturity,frequency,basis)

This function returns the number of coupons due between the settlement date and maturity date.

 

COUPPCD(settlement,maturity,frequency,basis)

This function returns a date number of the previous coupon date before the settlement date.

 

CUMIPMT(rate,nper,pval,startperiod,endperiod,paytype)

This function returns the cumulative interest paid on a loan between the starting and ending periods.

argument

description

rate

Interest rate

nper

Total number of payment periods

pval

Present value

startperiod

Starting period

endperiod

Ending period

paytype

Type of payment timing; can be any of:

 

0 - Payment at end of the period

 

1 - Payment at beginning of the period

 

CUMPRINC(rate,nper,pval,startperiod,endperiod,paytype)

This function returns the cumulative principal paid on a loan between the start and end periods.

 

DB(cost,salvage,life,period,month)

This function calculates the depreciation of an asset for a specified period using the fixed-declining balance method.

argument

description

cost

Initial cost of the asset

salvage

Value at the end of the depreciation period

life

Number of periods over which the asset is being depreciated

period

Period for which you want to calculate the depreciation; use the same units as the life argument

month

[Optional] Number of months in the first year; if omitted, the calculation uses 12 months

 

DDB(cost,salvage,life,period,factor)

This function calculates the depreciation of an asset for a specified period using the double-declining balance method or another method you specify.

argument

description

cost

Initial cost of the asset

salvage

Value at the end of depreciation

life

Number of periods over which the asset is being depreciated

period

Period for which you want to calculate the depreciation in the same units as the life argument

factor

[Optional] Rate at which the value declines; if omitted, the calculation uses 2 (double-declining method)

example: DDB(500000,5000,5,1,4) gives the result $40,0000

 

DISC(settle,mature,pricep,redeem,basis)

This function calculates the discount rate for a security.

argument

description

settle

Settlement date for the security

mature

Maturity date for the security

pricep

Amount invested in the security

redeem

Amount to be received at maturity

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

example: DISC("5/15/2004","9/1/2004",98.2,100,3) gives the result 0.0602752294

 

DOLLAR(value,digits)

This function converts a number to text using currency format, with the decimals rounded to the specified place.

argument

description

value

Numeric value to convert to text using the currency format

digits

[Optional] Number of decimal places to maintain; if negative, the value is rounded to the left of the decimal point; if omitted, the function rounds to two decimal places

example: DOLLAR(123.45,1) gives the result $123.5

 

DOLLARDE(fractionaldollar,fraction)

This function converts a fraction dollar price to a decimal dollar price.

argument

description

fractionaldollar

Numeric value expressed as a fraction

fraction

Denominator of the fraction; if not an integer, the number is truncated

 

DOLLARFR(decimaldollar,fraction)

This function converts a decimal number dollar price to a fraction dollar price.

argument

description

decimaldollar

Decimal number

fraction

Denominator of the fraction; if not an integer, the number is truncated

example: DOLLARFR(1.125,16) gives the result 1.02

 

 

DURATION(settlement,maturity,coupon,yield,frequency,basis)

This function returns the Macauley duration for an assumed par value of $100.

argument

description

settlement

Settlement date for the security

maturity

Maturity date for the security

coupon

Annual coupon rate

yield

Annual yield for the security

frequency

Frequency of payment, number of coupon payments per year; must be 1, 2, or 4

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

 

EFFECT(nomrate,comper)

This function calculates the effective annual interest rate for a given nominal annual interest rate and the number of compounding periods per year.

argument

description

nomrate

Nominal interest rate

comper

Number of compounding periods; if not an integer, the number is truncated

example: EFFECT(6.5%,8) gives the result 0.66878782

 

EURO(code)

This function returns the equivalent of one Euro based on the ISO currency code.

Country/Region

ISO Currency Code

Belgium

BEF

Luxembourg

LUF

Germany

DEM

Spain

ESP

France

FRF

Ireland

IEP

Italy

ITL

Netherlands

NLG

Austria

ATS

Portugal

PTE

Finland

FIM

Euro member state

EUR

 

EUROCONVERT(currency,source,target,fullprecision,triangulation)

This function converts currency from a Euro member currency (including Euros) to another Euro member currency (including Euros).

Argument

Description

currency

Number to convert

source

ISO currency code for the number to convert

target

ISO currency code for the result of the conversion

fullprecision

[Optional] Logical value representing whether to display the value in full precision or not; if omitted, the value is not displayed in full precision

triangulation

[Optional] Integer greater than or equal to 3 that specifies the number of significant digits to be used for the intermediate Euro value when converting between two Euro member currencies

example: EUROCONVERT(1,"EUR","FRF") gives the result 6,56

 

FV(rate,numper,paymt,pval,type)

This function returns the future value of an investment based on a present value, periodic payments, and a specified interest rate.

argument

description

rate

Interest rate expressed as percentage (per period)

numper

Total number of payment periods

paymt

Payment made each period

pval

[Optional] Present value; if omitted, uses zero and the calculation is based on the paymt argument.

type

[Optional] Indicates when payments are due; at the end (0) or beginning (1) of the period; if omitted, the calculation uses the end (0)

example: FV(0.005,60,-100,100,1) gives the result $6877.00

 

FVSCHEDULE(principal,schedule)

This function returns the future value of an initial principal after applying a series of compound interest rates. Calculate future value of an investment with a variable or adjustable rate.

argument

description

principal

Present value of the principal

schedule

Schedule, column or array of interest rates to apply

example: FVSCHEDULE(1000,{0.8,0.6,0.7}) gives the result 4896

 

INTRATE(settle,mature,invest,redeem,basis)

This function calculates the interest rate for a fully invested security.

argument

description

settle

Settlement date for the security.

mature

Maturity date for the security.

invest

Amount invested in the security.

redeem

Amount to be received at maturity.

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

example: INTRATE("3/1/2003","5/31/2003",65000,70000,2) gives the result 0.304311074

 

IPMT(rate,per,nper,pval,fval,end)

This function calculates the payment of interest on a loan.

argument

description

rate

Value of interest rate per period.

per

Number of the period for which to find the interest, between 1 and nper

nper

Total number of payment periods in an annuity.

pval

Present value, worth now

fval

[Optional] Future value, cash value after the last payment; if omitted, the calculation uses zero

type

[Optional] Indicates when payments are due; at the end (0) or beginning (1) of the period; if omitted, the calculation uses the end (0)

example: IPMT(0.45, 2, 30, 6000) gives the result -$2,699.98

 

IRR(arrayvals,estimate)

IRR(columnvals,estimate)

This function returns the internal rate of return for a series of cash flows represented by the numbers in an array.

argument

description

arrayvals

Column or an array of numbers for which you want to estimate the internal rate of return representing payments and income occurring at regular intervals (and use positive for income and negative for payment)

estimate

[Optional] An estimate of the internal rate of return; if omitted, the calculation uses 0.1 (10 percent)

 

ISPMT(rate,per,nper,pv)

This function calculates the interest paid during a specific period of an investment.

argument

description

rate

Interest rate for the investment.

per

Number of the period for which to find the interest, between 1 and nper.

nper

Total number of payment periods for the investment.

pv

Present value of the investment.

 

MDURATION(settlement,maturity,coupon,yield,frequency,basis)

This function calculates the modified Macauley duration of a security with an assumed par value of $100.

argument

description

settlement

Settlement date for the security

maturity

Maturity date for the security

coupon

Annual coupon rate

yield

Annual yield for the security

frequency

Frequency of payment, number of coupon payments per year; must be 1, 2, or 4

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

 

MIRR(arrayvals,payment_int,income_int)

MIRR(columnvals,payment_int,income_int)

This function returns the modified internal rate of return for a series of periodic cash flows.

argument

description

arrayvals

Column or an array of numbers for which you want to estimate the internal rate of return representing payments and income occurring at regular intervals (and use positive for income and negative for payment)

payment_int

Interest rate on money in cash flows

income_int

Interest rate on money invested from cash flows

 

NOMINAL(effrate,comper)

This function returns the nominal annual interest rate for a given effective rate and number of compounding periods per year.

argument

description

effrate

Value representing the effective interest rate

comper

Number of compounding periods per year; if not an integer, the number is truncated

example: NOMINAL(6.2336%,6) gives the result 0.060776004

 

NPER(rate,paymt,pval,fval,type)

This function returns the number of periods for an investment based on a present value, future value, periodic payments, and a specified interest rate.

argument

description

rate

Interest rate expressed as percentage (per period)

paymt

Payment made each period; cannot change over life of the annuity

pval

Present value

fval

[Optional] Future value; if omitted, the calculation uses zero (0)

type

[Optional] Indicates when payments are due; at the end (0) or beginning (1) of the period; if omitted, the calculation uses the end (0)

example: NPER(0.005,-790,90000,0,1) gives the result 167.7227522114

 

NPV(discount,value1,value2,...)

NPV(discount,column)

This function calculates the net present value of an investment by using a discount rate and a series of future payments and income.

argument

description

discount

Rate of discount for one period

value1,...

Column or values for money paid out (as for a payment) are negative numbers; values for money you receive (as for income) are positive numbers

example: NPV(6.5%, -10000, 3000, 3400, 7700) gives the result $2,055.38

 

ODDFPRICE(settle,maturity,issue,first,rate,yield,redeem,freq,basis)

This function calculates the price per $100 face value of a security with an odd first period.

argument

description

settle

Settlement date for the security

maturity

Maturity date for the security

issue

Issue date for the security

first

First coupon date

rate

Annual interest rate

yield

Annual yield for the security

redeem

Redemption value per $100 face value for the security

freq

Frequency of payment, number of payments per year

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

 

ODDFYIELD(settle,maturity,issue,first,rate,price,redeem,freq,basis)

This function calculates the yield of a security with an odd first period.

 

ODDLPRICE(settle,maturity,last,rate,yield,redeem,freq,basis)

This function calculates the price per $100 face value of a security with an odd last coupon period.

 

ODDLYIELD(settle,maturity,last,rate,price,redeem,freq,basis)

This function calculates the yield of a security with an odd last period.

 

PMT(rate,nper,pval,fval,end)

This function returns the payment amount for a loan given the present value, specified interest rate, and number of terms.

argument

description

rate

Value of interest rate per period

nper

Total number of payment periods

pval

Present value, worth now

fval

[Optional] Future value, cash value after the last payment; if omitted, the calculation uses zero

type

[Optional] Indicates when payments are due; at the end (0) or beginning (1) of the period; if omitted, the calculation uses the end (0)

example: PMT(0.005, 15, 5000, 0, 1) gives the result -$345.10

 

PPMT(rate,per,nper,pval,fval,end)

This function returns the amount of payment of principal for a loan given the present value, specified interest rate, and number of terms.

argument

description

rate

Value of interest rate per period.

per

Number of the period for which to find the interest, between 1 and nper

nper

Total number of payment periods in an annuity.

pval

Present value, worth now

fval

[Optional] Future value, cash value after the last payment; if omitted, the calculation uses zero

type

[Optional] Indicates when payments are due; at the end (0) or beginning (1) of the period; if omitted, the calculation uses the end (0)

example: PPMT(0.45, 22, 30, 6000, 7000) gives the result -$206.47

 

PRICE(settlement,maturity,rate,yield,redeem,frequency,basis)

This function calculates the price per $100 face value of a periodic interest security.

argument

description

settle

Settlement date for the security

maturity

Maturity date for the security

rate

Annual coupon rate

yield

Annual yield for the security

redeem

Redemption value per $100 face value for the security

frequency

Frequency of payment, number of payments per year; must be 1, 2, or 4

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

 

PRICEDISC(settle,mature,discount,redeem,basis)

This function returns the price per $100 face value of a discounted security.

argument

description

settle

Settlement date for the security.

mature

Maturity date for the security.

discount

Amount invested in the security.

redeem

Amount to be received at maturity.

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

example: PRICEDISC("5/15/2004","9/1/2004",0.06,100,3) gives the result 98.20822

 

PRICEMAT(settle,mature,issue,rate,yield,basis)

This function returns the price at maturity per $100 face value of a security that pays interest.

argument

description

settle

Settlement date for the security

mature

Maturity date for the security

issue

Issue date for the security

rate

Interest rate for the security at the issue date

yield

Annual yield for the security

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

example: PRICEMAT("5/15/2004","9/1/2004","5/15/2003",0.06,0.07,3) gives the result 99.5842915904314

 

PV(rate,numper,paymt,fval,type)

This function returns the present value of an investment based on the interest rate, number and amount of periodic payments, and future value. The present value is the total amount that a series of future payments is worth now.

argument

description

rate

Interest rate expressed as percentage (per period)

numper

Total number of payment periods

paymt

Payment made each period; cannot change over the life of the annuity

fval

[Optional] Future value; if omitted, the calculation is based on the payments

type

[Optional] Indicates when payments are due; at the end (0) or beginning (1) of the period; if omitted, the calculation uses the end (0)

example: PV(0.005,60,-100,0,1) gives the result $5,198.42

 

RATE(nper,pmt,pval,fval,type,guess)

This function returns the interest rate per period of an annuity.

argument

description

nper

Total number of payment periods in an annuity

pmt

Value representing the payment made each period

pval

Present value, worth now

fval

Future value, cash value after the last payment

type

[Optional] Indicates when payments are due; at the end (0) or beginning (1) of the period; if omitted, the calculation uses the end (0)

guess

Guess for what the rate will be (optional)

example: RATE(360, -600, 80000) gives the result 0.686%

 

 

RECEIVED(settle,mature,invest,discount,basis)

This function returns the amount received at maturity for a fully invested security.

argument

description

settle

Settlement date for the security

mature

Maturity date for the security

invest

Amount invested in the security

discount

Discount rate for the security

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

example: RECEIVED("3/01/2004","6/01/2004",600000,0.03,2) gives $604,635.50

 

 

SLN(cost,salvage,life)

This function returns the straight-line depreciation of an asset for one period.

argument

description

cost

Initial cost of the asset

salvage

Value at the end of the depreciation

life

Number of periods over which the asset is being depreciated

example: SLN(500000,20000,5) gives the result $96,000

 

 

SYD(cost,salvage,salvage,period)

This function returns the sum-of-years' digits depreciation of an asset for a specified period.

argument

description

cost

Initial cost of the asset

salvage

Value at the end of the depreciation

life

Number of periods over which the asset is being depreciated

period

Period for depreciation; must use the same units as the life argument.

This function calculates the digits depreciation as follows:

Func-SYD

example: SYD(100000,10000,5,2) gives the result $2,4000

 

 

TBILLEQ(settle,mature,discount)

This function returns the equivalent yield for a Treasury bill (or T-bill).

argument

description

settle

Settlement date for the Treasury bill

mature

Maturity date for the Treasury bill

discount

Discount rate for the Treasury bill

example: TBILLEQ("3/31/2003","6/1/2003",0.0532) gives the result 0.054437659 (or 5.44%)

 

 

TBILLPRICE(settle,mature,discount)

This function returns the price per $100 face value for a Treasury bill (or T-bill).

argument

description

settle

Settlement date for the Treasury bill

mature

Maturity date for the Treasury bill

priceper

Price per $100 face value for the Treasury bill

example:TBILLPRICE("3/31/2003","6/1/2003",0.065) gives the result $98.88055556

 

 

TBILLYIELD(settle,mature,priceper)

This function returns the yield for a Treasury bill (or T-bill).

argument

description

settle

Settlement date for the Treasury bill

mature

Maturity date for the Treasury bill

priceper

Price per $100 face value for the Treasury bill

example: TBILLYIELD("3/31/2003","6/1/2003",98.65) gives the result 0.0794598041299475 (or 5.80%)

 

 

VDB(cost,salvage,life,start,end,factor,switchnot)

This function returns the depreciation of an asset for any period you specify using the variable declining balance method.

argument

description

cost

Initial cost of the asset

salvage

Value at the end of the depreciation period

life

Number of periods over which the asset is being depreciated

start

Number representing the starting period for which to calculate the depreciation in the same units as life; if not an integer, the number is truncated

end

Number representing the ending period for which to calculate the depreciation in the same units as life; if not an integer, the number is truncated

factor

[Optional] Rate at which the balance declines; if omitted, uses two (2)

switchnot

[Optional] Logical value specifying whether to switch to straight-line depreciation when depreciation is greater than the declining balance calculation; if omitted uses FALSE

example: VDB(50000,500,1200,100,1000,1) gives the result $37,122.94

 

XIRR(values,dates,guess)

This function calculates the internal rate of return for a schedule of cash flows that may not be periodic.

argument

description

values

Series of cash flows that correspond to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment

dates

Schedule of payment dates that corresponds to the cash flow payments in values

guess

[Optional] Estimate of the internal rate of return that you guess is close to the result of this function; if omitted, the calculation uses 0.1 (10 percent)

 

XNPV(rate,values,dates)

This function calculates the net present value for a schedule of cash flows that may not be periodic.

argument

description

rate

Discount rate to apply to the cash flows

values

Series of cash flows that correspond to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment

dates

Schedule of payment dates that corresponds to the cash flow payments in values

 

YIELD(settle,maturity,rate,price,redeem,frequency,basis)

This function calculates the yield on a security that pays periodic interest.

argument

description

settle

Settlement date for the security

maturity

Maturity date for the security

rate

Annual coupon rate

price

Price per $100 face value for the security

redeem

Redemption value per $100 face value

frequency

Frequency of payment, number of coupon payments per year; must be 1, 2, or 4

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

 

YIELDDISC(settle,maturity,price,redeem,basis)

This function calculates the annual yield for a discounted security.

argument

description

settle

Settlement date for the security

maturity

Maturity date for the security

price

Price per $100 face value for the security

redeem

Redemption value per $100 face value

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

 

YIELDMAT(settle,maturity,issue,issrate,price,basis)

This function calculates the annual yield of a security that pays interest at maturity.

argument

description

settle

Settlement date for the security

maturity

Maturity date for the security

issue

Issue date for the security

issrate

Interest rate for the security at the date of issue

price

Price per $100 face value for the security

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

 

 

 

See Also:


Formula Functions | Day Count Basis