﻿ Financial Functions

# 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%

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

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:

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