Financial functions
[ ] indicates optional parameters
ACCRINT(issue_date, first_interest_date, settlement_date, rate, par, frequency, [basis])ACCRINT(issue_date, first_interest_date, settlement_date, rate, par, frequency, [basis])
Returns accrued interest for a security that pays periodic interest.
issue_date | The issue date. | ||||||||||
first_interest_date | The first interest date. | ||||||||||
settlement_date | The settlement date. | ||||||||||
rate | The annual coupon rate. | ||||||||||
par | The par value. | ||||||||||
frequency | The number of interest payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
ACCRINTM(issue_date, maturity_date, rate, [par], [basis])
Returns accrued interest for a security that pays interest at maturity.
issue_date | The issue date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
rate | The annual coupon rate. | ||||||||||
par | The par value. If this parameter is omitted it defaults to 1000. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
AMORDEGRC(cost, purchase_date, first_period_date, salvage, period, rate, [basis])
Returns the depreciation for each accounting period.
cost | The cost. | ||||||||||
purchase_date | The purchase date. | ||||||||||
first_period_date | The end date of the first period. | ||||||||||
salvage | The salvage value. | ||||||||||
period | The period for which you want to calculate the depreciation. | ||||||||||
rate | The depreciation rate. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
AMORLINC(cost, purchase_date, first_period_date, salvage, period, rate, [basis])
Returns the depreciation for each accounting period.
cost | The cost. | ||||||||||
purchase_date | The purchase date. | ||||||||||
first_period_date | The end date of the first period. | ||||||||||
salvage | The salvage value. | ||||||||||
period | The period for which you want to calculate the depreciation. | ||||||||||
rate | The depreciation rate. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
COUPDAYBS(settlement_date, maturity_date, frequency, [basis])
Returns the number of days from the beginning of the coupon period to the settlement date.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
COUPDAYS(settlement_date, maturity_date, frequency, [basis])
Returns the number of days in the coupon period that contains the settlement date.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
COUPDAYSNC(settlement_date, maturity_date, frequency, [basis])
Returns the number of days from the settlement date to the next coupon date.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
COUPNCD(settlement_date, maturity_date, frequency, [basis])
Returns the next coupon date after the settlement date.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
COUPNUM(settlement_date, maturity_date, frequency, [basis])
Returns the number of coupon periods between the settlement date and the maturity date.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
COUPPCD(settlement_date, maturity_date, frequency, [basis])
Returns the coupon date before the settlement date.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
CUMIPMT(rate, nper, pv, start_period, end_period, type)
Returns the cumulative interest paid on a loan in the specified periods.
rate | The interest rate. | ||||
nper | The total number of periods. | ||||
pv | The present value. | ||||
start_period | The first period number for which to calculate interest. | ||||
end_period | The last period number for which to calculate interest. | ||||
type | The timing of the payment. The possible values are: | ||||
|
CUMPRINC(rate, nper, pv, start_period, end_period, type)
Returns the cumulative principal paid on a loan in the specified periods.
rate | The interest rate. | ||||
nper | The total number of periods. | ||||
pv | The present value. | ||||
start_period | The first period number for which to calculate interest. | ||||
end_period | The last period number for which to calculate interest. | ||||
type | The timing of the payment. The possible values are: | ||||
|
DB(cost, salvage, life, period, [month])
Returns the depreciation in a specified period using the fixed declining balance method.
cost | The cost. |
salvage | The salvage value. |
life | The total number of periods. |
period | The period number for which to calculate depreciation. |
month | The number of months in the first year. If this parameter is omitted it defaults to 12. |
DDB(cost, salvage, life, period, [factor])
Returns the depreciation in a specified period using the double declining balance method.
cost | The cost. |
salvage | The salvage value. |
life | The total number of periods. |
period | The period number for which to calculate depreciation. |
factor | The rate at which the balance declines. If this parameter is omitted it defaults to 2. |
DISC(settlement_date, maturity_date, pr, redemption, [basis])
Returns the discount rate for a security.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
pr | The price per $100 value. | ||||||||||
redemption | The redemption per $100 value. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
DOLLARDE(fractional_dollar, fraction)
Returns the decimal equivalent of a dollar price expressed as a fraction.
fractional_dollar | The value expressed as a fraction. |
fraction | The fraction denominator. |
DOLLARFR(decimal_dollar, fraction)
Returns the fraction equivalent of a dollar price expressed as a decimal.
decimal_dollar | The value expressed as a decimal. |
fraction | The fraction denominator. |
DURATION(settlement_date, maturity_date, coupon, yield, frequency, [basis])
Returns the Macauley duration for a value of $100.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
coupon | The interest rate. | ||||||||||
yield | The annual yield rate. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
Returns the effective annual interest rate.
nominal_rate | The nominal annual interest rate. |
npery | The number of compound interest payments per year. |
FV(rate, term, [payment], [pv], [type])
Returns the future value of an investment at a fixed rate.
rate | The interest rate per period. | ||||
term | The total number of periods. | ||||
payment | The payment amount each period. If this parameter is omitted it is assumed to be zero. | ||||
pv | The present value. If this parameter is omitted it is assumed to be zero. | ||||
type | The timing of the payment. The possible values are: | ||||
| |||||
If this parameter is omitted it defaults to 0. |
FVSCHEDULE(principal, schedule_range)
Returns the future value of an investment at a variable rate.
principal | The initial value of the investment. |
schedule_range | The list (array or reference) of interest rates to be applied. |
INTRATE(settlement_date, maturity_date, investment_amount, redemption_amount, [basis])
Returns the interest rate for a fully invested security.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
investment_amount | The initial value. | ||||||||||
redemption_amount | The final value. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
IPMT(rate, period, term, pv, [fv], [type])
Returns the interest payment for a given period.
rate | The interest rate per period. | ||||
period | The period for which you want the interest amount. | ||||
term | The total number of periods. | ||||
pv | The present value. | ||||
fv | The future value. If this parameter is omitted it is assumed to be zero. | ||||
type | The timing of the payment. The possible values are: | ||||
| |||||
If this parameter is omitted it defaults to 0. |
Returns the internal rate of return.
values_range | The list (array or reference) of payment and income values. |
guess | The estimated rate of return. If this parameter is omitted it defaults to 0.1. |
MDURATION(settlement_date, maturity_date, coupon, yield, frequency, [basis])
Returns the modified Macauley duration for a value of $100.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
coupon | The interest rate. | ||||||||||
yield | The annual yield rate. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
MIRR(values_range, finance_rate, reinvest_rate)
Returns the modified internal rate of return.
values_range | The list (array or reference) of payment and income values. |
finance_rate | The interest rate on the payment values. |
reinvest_rate | The interest rate on the income values. |
Returns the nominal annual interest rate.
effect_rate | The effective annual interest rate. |
npery | The number of compound interest payments per year. |
NPER(rate, payment, pv, [fv], [type])
Returns the number of periods required for an investment.
rate | The interest rate per period. | ||||
payment | The payment amount per period. | ||||
pv | The present value. | ||||
fv | The future value. If this parameter is omitted it defaults to 0. | ||||
type | The timing of the payment. The possible values are: | ||||
| |||||
If this parameter is omitted it defaults to 0. |
NPV(rate, value1, [value2, ...])
Returns the net present value of an investment.
rate | The discount rate per period. |
value1, ... | The payment and income amounts. |
ODDFPRICE(settlement_date, maturity_date, issue_date, first_coupon_date, rate, yield, redemption, frequency, [basis])
Returns the price per $100 face value of a security having an odd (short or long) first period.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
issue_date | The issue date. | ||||||||||
first_coupon_date | The first coupon date. | ||||||||||
rate | The annual coupon rate. | ||||||||||
yield | The annual yield rate. | ||||||||||
redemption | The redemption value per $100. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
ODDFYIELD(settlement_date, maturity_date, issue_date, first_coupon_date, rate, price, redemption, frequency, [basis])
Returns the yield of a security having an odd (short or long) first period.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
issue_date | The issue date. | ||||||||||
first_coupon_date | The first coupon date. | ||||||||||
rate | The annual coupon rate. | ||||||||||
price | The price per $100. | ||||||||||
redemption | The redemption value per $100. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
ODDLPRICE(settlement_date, maturity_date, last_coupon_date, rate, yield, redemption, frequency, [basis])
Returns the price per $100 face value of a security having an odd (short or long) last period.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
last_coupon_date | The last coupon date. | ||||||||||
rate | The annual coupon rate. | ||||||||||
yield | The annual yield rate. | ||||||||||
redemption | The redemption value per $100. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
ODDLYIELD(settlement_date, maturity_date, last_coupon_date, rate, price, redemption, frequency, [basis])
Returns the yield of a security having an odd (short or long) last period.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
last_coupon_date | The last coupon date. | ||||||||||
rate | The annual coupon rate. | ||||||||||
price | The price per $100. | ||||||||||
redemption | The redemption value per $100. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
PMT(rate, term, [pv], [fv], [type])
Returns the payment amount for a loan.
rate | The interest rate per period. | ||||
term | The total number of periods. | ||||
pv | The present value of the loan. If this parameter is omitted it defaults to 0. | ||||
fv | The future value of the loan. If this parameter is omitted it defaults to 0. | ||||
type | The timing of the payment. The possible values are: | ||||
| |||||
If this parameter is omitted it defaults to 0. |
PPMT(rate, period, term, pv, [fv], [type])
Returns the payment on the principal for a specified period.
rate | The interest rate per period. | ||||
period | The period for which you want the payment amount. | ||||
term | The total number of periods. | ||||
pv | The present value of the loan. | ||||
fv | The future value of the loan. If this parameter is omitted it defaults to 0. | ||||
type | The timing of the payment. The possible values are: | ||||
| |||||
If this parameter is omitted it defaults to 0. |
PRICE(settlement_date, maturity_date, rate, yield, redemption, frequency, [basis])
Returns the price per $100 of a security.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
rate | The annual coupon rate. | ||||||||||
yield | The annual yield rate. | ||||||||||
redemption | The redemption value per $100. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
PRICEDISC(settlement_date, maturity_date, discount, redemption, [basis])
Returns the price per $100 of a discounted security.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
discount | The discount rate. | ||||||||||
redemption | The redemption value per $100. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
PRICEMAT(settlement_date, maturity_date, issue_date, rate, yield, [basis])
Returns the price per $100 of a security that pays interest at maturity.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
issue_date | The issue date. | ||||||||||
rate | The interest rate. | ||||||||||
yield | The annual yield rate. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
PV(rate, term, [payment], [fv], [type])
Returns the present value of an investment at a fixed rate.
rate | The interest rate per period. | ||||
term | The total number of periods. | ||||
payment | The payment amount each period. If this parameter is omitted it is assumed to be zero. | ||||
fv | The future value. If this parameter is omitted it is assumed to be zero. | ||||
type | The timing of the payment. The possible values are: | ||||
| |||||
If this parameter is omitted it defaults to 0. |
RATE(term, payment, pv, [fv], [type], [guess])
Returns the interest rate per period of an annuity.
term | The total number of periods. | ||||
payment | The payment amount each period. | ||||
pv | The present value. | ||||
fv | The future value. If this parameter is omitted it is assumed to be zero. | ||||
type | The timing of the payment. The possible values are: | ||||
| |||||
If this parameter is omitted it defaults to 0. | |||||
guess | The estimated rate of return. If this parameter is omitted it defaults to 0.1. |
RECEIVED(settlement_date, maturity_date, investment, discount, [basis])
Returns the amount received at maturity for a fully invested security.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
investment | The investment amount. | ||||||||||
discount | The discount rate. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
Returns the straight line depreciation.
cost | The cost. |
salvage | The salvage value. |
life | The total number of periods. |
SYD(cost, salvage, life, period)
Returns the sum of years depreciation.
cost | The cost. |
salvage | The salvage value. |
life | The total number of periods. |
period | The period for which you want the depreciation. |
TBILLEQ(settlement_date, maturity_date, discount)
Returns the bond-equivalent yield for a treasury bill.
settlement_date | The settlement date. |
maturity_date | The maturity date. |
discount | The discount rate. |
TBILLPRICE(settlement_date, maturity_date, discount)
Returns the price per $100 for a treasury bill.
settlement_date | The settlement date. |
maturity_date | The maturity date. |
discount | The discount rate. |
TBILLYIELD(settlement_date, maturity_date, price)
Returns the yield for a treasury bill.
settlement_date | The settlement date. |
maturity_date | The maturity date. |
price | The price per $100. |
VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])
Returns the depreciation in a specified range of periods using the variable declining balance method.
cost | The cost. | ||||
salvage | The salvage value. | ||||
life | The total number of periods. | ||||
start_period | The first period number for which to calculate depreciation. | ||||
end_period | The last period number for which to calculate depreciation. | ||||
factor | The rate at which the balance declines. If this parameter is omitted it defaults to 2. | ||||
no_switch | Specifies whether to switch to straight-line depreciation when the straight-line depreciation is greater than the declining balance depreciation. The possible values are: | ||||
| |||||
If this parameter is omitted it defaults to FALSE. |
XIRR(values_range, dates_range, [guess])
Returns the internal rate of return.
values_range | The list (array or reference) of payment and income values. |
dates_range | The list (array or reference) of the dates of the payment and income values. |
guess | The estimated rate of return. If this parameter is omitted it defaults to 0.1. |
XNPV(rate, values_range, dates_range)
Returns the net present value of an investment.
rate | The discount rate per period. |
values_range | The list (array or reference) of payment and income values. |
dates_range | The list (array or reference) of the dates of the payment and income values. |
YIELD(settlement_date, maturity_date, rate, price, redemption, frequency, [basis])
Returns the yield on a security.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
rate | The annual coupon rate. | ||||||||||
price | The price per $100. | ||||||||||
redemption | The redemption value per $100. | ||||||||||
frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
YIELDDISC(settlement_date, maturity_date, price, redemption, [basis])
Returns the annual yield for a discounted security.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
price | The price per $100. | ||||||||||
redemption | The redemption value per $100. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |
YIELDMAT(settlement_date, maturity_date, issue_date, rate, price, [basis])
Returns the annual yield of a security that pays interest at maturity.
settlement_date | The settlement date. | ||||||||||
maturity_date | The maturity date. | ||||||||||
issue_date | The issue date. | ||||||||||
rate | The interest rate. | ||||||||||
price | The price per $100. | ||||||||||
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
If this parameter is omitted it defaults to 0. |