Documentation/How Tos/Calc: Financial functions

Financial Functions

The Financial functions provide common business calculations.

(Note to translators: some of these function descriptions may be rewritten for improved clarity.)

Depreciation
AMORDEGRC	Returns depreciation for a settlement period as degressive amortization.
AMORLINC	Returns depreciation for a settlement period as linear amortization.
DB	Returns the depreciation of an asset for a given year using the fixed rate declining-balance method.
DDB	Returns the depreciation of an asset for a given year using the double (or other factor) declining-balance method.
SLN	Returns the depreciation of an asset in a single period using the straight-line depreciation method.
SYD	Returns the depreciation of an asset for a given year using the sum-of-years'-digits method.
VDB	Returns the depreciation of an asset for a given year using a variable declining-balance method.
Payment Streams, Annuities, Loans
CUMIPMT	Returns the total interest paid on a loan in specified periods.
CUMIPMT_ADD	Returns the total interest paid on a loan in specified periods.
CUMPRINC	Returns the total capital repaid on a loan in specified periods.
CUMPRINC_ADD	Returns the total capital repaid on a loan in specified periods.
EFFECTIVE	Returns the effective compounded interest rate given a nominal interest rate.
EFFECT_ADD	Returns the effective compounded interest rate given a nominal interest rate.
FV	Returns the future value of an initial sum with a subsequent stream of payments.
FVSCHEDULE	Returns the future value of an initial sum, with changing future interest rates.
IPMT	Returns the interest paid in a period for a fixed rate loan.
IRR	Calculates the internal rate of return for an investment. The values represent cash flow values at regular intervals, at least one value must be negative (payments), and at least one value must be positive (income).
ISPMT	Returns the interest paid in a period for a fixed rate loan.
MIRR	Calculates the modified internal rate of return of a series of investments.
NPER	Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
NPV	Returns the net present value of an investment given cash payments and a discount rate.
PMT	Returns the payment per period for a fixed rate loan.
PPMT	Returns the capital repaid in a period for a fixed rate loan.
PV	Returns the present value of a stream of future payments with a final lump sum.
RATE	Returns the constant interest rate per period of an annuity.
RRI	Calculates the interest rate resulting from the profit (return) of an investment.
XIRR	Calculates the internal rate of return for a list of payments which take place on different dates. The calculation is based on a 365 days per year basis, ignoring leap years.
XNPV	Returns the net present value of an investment with irregular cash payments.
Securities
ACCRINT	Calculates the accrued interest for a security with periodic interest payments.
ACCRINTM	Calculates the accrued interest for a security that pays at maturity.
DISC	Calculates the allowance (discount) of a security as a percentage.
DURATION	Calculates the number of periods required by an investment to attain the desired value.
DURATION_ADD	Calculates the duration of a fixed interest security in years.
INTRATE	Returns the equivalent annual interest rate for an item bought at one price and sold at another.
MDURATION	Calculates the modified Macauley duration of a fixed interest security in years.
NOMINAL	Returns a nominal interest rate given the effective compounded interest rate.
NOMINAL_ADD	Returns a nominal interest rate given the effective compounded interest rate.
ODDFPRICE	Returns the value of a security per 100 currency units of face value, where the time to the first interest payment is not a whole period.
ODDFYIELD	Returns the yield of a security, where the time to the first interest payment is not a whole period.
ODDLPRICE	Returns the value of a security per 100 currency units of face value, where the last interest payment is not a whole period.
ODDLYIELD	Returns the yield of a security, where the last interest payment is not a whole period.
PRICE	Calculates a quoted price for an interest-bearing security.
PRICEDISC	Calculates the price of a non-interest-bearing security.
PRICEMAT	Calculates the price of a security that pays interest at maturity.
RECEIVED	Calculates the amount received that is paid for a fixed-interest security at a given point in time.
TBILLEQ	Returns the bond-equivalent-yield (BEY) for a US Treasury bill.
TBILLPRICE	Returns the issue price for a US Treasury bill, per $100 par value, given a discount rate.
TBILLYIELD	Returns the yield for a US Treasury bill.
YIELD	Calculates the yield of a security.
YIELDDISC	Calculates the annual yield of a non-interest-bearing security.
YIELDMAT	Calculates the annual yield of a security, the interest of which is paid on the date of maturity.
Coupons
COUPDAYBS	Returns the number of days between the coupon date preceding the settlement, and the settlement date.
COUPDAYS	Returns the number of days in the coupon period that contains the settlement date.
COUPDAYSNC	Returns the number of days between the settlement date and the next coupon date.
COUPNCD	Returns the coupon date next after the settlement date.
COUPNUM	Returns the number of coupons (interest payments) between the settlement date and maturity.
COUPPCD	Returns the coupon (interest payment) date which precedes the settlement date.
Miscellaneous
DOLLARDE	Converts a fractional number representation of a number into a decimal number.
DOLLARFR	Converts a decimal number into a fractional representation of that number.

See also

Derivation of Financial Formulas

Financial date systems

Functions listed by category