SpreadCE Help

Financial functions

[ ] indicates optional parameters

ACCRINT(issue_date, first_interest_date, settlement_date, rate, par, frequency, [basis])
ACCRINTM(issue_date, maturity_date, rate, [par], [basis])
AMORDEGRC(cost, purchase_date, first_period_date, salvage, period, rate, [basis])
AMORLINC(cost, purchase_date, first_period_date, salvage, period, rate, [basis])
COUPDAYBS(settlement_date, maturity_date, frequency, [basis])
COUPDAYS(settlement_date, maturity_date, frequency, [basis])
COUPDAYSNC(settlement_date, maturity_date, frequency, [basis])
COUPNCD(settlement_date, maturity_date, frequency, [basis])
COUPNUM(settlement_date, maturity_date, frequency, [basis])
COUPPCD(settlement_date, maturity_date, frequency, [basis])
CUMIPMT(rate, nper, pv, start_period, end_period, type)
CUMPRINC(rate, nper, pv, start_period, end_period, type)
DB(cost, salvage, life, period, [month])
DDB(cost, salvage, life, period, [factor])
DISC(settlement_date, maturity_date, pr, redemption, [basis])
DOLLARDE(fractional_dollar, fraction)
DOLLARFR(decimal_dollar, fraction)
DURATION(settlement_date, maturity_date, coupon, yield, frequency, [basis])
EFFECT(nominal_rate, npery)
FV(rate, term, [payment], [pv], [type])
FVSCHEDULE(principal, schedule_range)
INTRATE(settlement_date, maturity_date, investment_amount, redemption_amount, [basis])
IPMT(rate, period, term, pv, [fv], [type])
IRR(values_range, [guess])
MDURATION(settlement_date, maturity_date, coupon, yield, frequency, [basis])
MIRR(values_range, finance_rate, reinvest_rate)
NOMINAL(effect_rate, npery)
NPER(rate, payment, pv, [fv], [type])
NPV(rate, value1, [value2, ...])
PMT(rate, term, [pv], [fv], [type])
PPMT(rate, period, term, pv, [fv], [type])
PRICE(settlement_date, maturity_date, rate, yield, redemption, frequency, [basis])
PRICEDISC(settlement_date, maturity_date, discount, redemption, [basis])
PRICEMAT(settlement_date, maturity_date, issue_date, rate, yield, [basis])
PV(rate, term, [payment], [fv], [type])
RATE(term, payment, pv, [fv], [type], [guess])
RECEIVED(settlement_date, maturity_date, investment, discount, [basis])
SLN(cost, salvage, life)
SYD(cost, salvage, life, period)
TBILLEQ(settlement_date, maturity_date, discount)
TBILLPRICE(settlement_date, maturity_date, discount)
TBILLYIELD(settlement_date, maturity_date, price)
VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])
XIRR(values_range, dates_range, [guess])
XNPV(rate, values_range, dates_range)
YIELD(settlement_date, maturity_date, rate, price, redemption, frequency, [basis])
YIELDDISC(settlement_date, maturity_date, price, redemption, [basis])
YIELDMAT(settlement_date, maturity_date, issue_date, rate, price, [basis])

Financial functions

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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | Payment is made at the end of each period.
1 | Payment is made at the start of each period.

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:

0 | Payment is made at the end of each period.
1 | Payment is made at the start of each period.

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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| If this parameter is omitted it defaults to 0.

EFFECT(nominal_rate, npery)

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:

0 | Payment is made at the end of each period.
1 | Payment is made at the start of each period.

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | Payment is made at the end of each period.
1 | Payment is made at the start of each period.

| If this parameter is omitted it defaults to 0.

IRR(values_range, [guess])

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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

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

NOMINAL(effect_rate, npery)

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:

0 | Payment is made at the end of each period.
1 | Payment is made at the start of each period.

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

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:

0 | Payment is made at the end of each period.
1 | Payment is made at the start of each period.

| 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:

0 | Payment is made at the end of each period.
1 | Payment is made at the start of each period.

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | Payment is made at the end of each period.
1 | Payment is made at the start of each period.

| 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:

0 | Payment is made at the end of each period.
1 | Payment is made at the start of each period.

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| If this parameter is omitted it defaults to 0.

SLN(cost, salvage, life)

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:

FALSE | Switch to straight-line depreciation.
TRUE | Do not switch to straight-line depreciation.

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| 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:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| If this parameter is omitted it defaults to 0.