## 财务公式

关键词:  财务公式

Formulas Description Examples
ACCRINT Syntax: ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])
Returns the accrued interest for a security that pays periodic interest.
=ACCRINT(A2,A3,A4,A5,A6,A7,A8)
COUPDAYS Syntax: COUPDAYS(settlement, maturity, frequency, [basis])
Returns the number of days in the coupon period that contains the settlement date.
=COUPDAYS(A2,A3,A4,2,1)
COUPDAYSNC Syntax: COUPDAYSNC(settlement, maturity, frequency, [basis])
Returns the number of days from the settlement date to the next coupon date.
=COUPDAYSNC(A2,A3,A4,A5)
COUPNCD Syntax: COUPNCD(settlement, maturity, frequency, [basis])
Returns a number that represents the next coupon date before the settlement date.
=COUPNCD(A2,A3,A4,2,1)
COUPNUM Syntax: COUPNUM(settlement, maturity, frequency, [basis])
Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon.
= COUPNUM(A2,A3,A4,2,1)
COUPPCD Syntax: COUPPCD(settlement, maturity, frequency, [basis])
Returns a number that represents the previous coupon date before the settlement date.
=COUPPCD(A2,A3,A4,2,1)
CUMIPMT Syntax: CUMIPMT(rate, nper, pv, start_period, end_period, type)
Returns the cumulative interest paid on a loan between start_period and end_period.
=CUMIPMT(A2/12,A3*12,A4,13,24,0)
CUMPRINC Syntax: CUMPRINC(rate, nper, pv, start_period, end_period, type)
Returns the cumulative principal paid on a loan between start_period and end_period.
=CUMPRINC(A2/12,A3*12,A4,13,24,0)
DB Syntax: DB(cost, salvage, life, period, [number_months])
Returns the depreciation of an asset for a given time period based on the fixed-declining balance method.
cost is the original cost of the asset.
salvage is the salvage value after the asset has been fully depreciated.
life is the useful life of the asset or the number of periods that you will be depreciating the asset.
period is the period that you wish to calculate the depreciation for. Use the same units as for the life.
number_months is optional. It is the number of months in the first year of depreciation. If this parameter is omitted, the DB function will assume that there are 12 months in the first year.
=DB(A2,A3,A4,5, 7 )
=DB(10000,10000,6,5, 7 )
DDB Syntax: DDB(cost, salvage, life, period, [factor])
Returns the depreciation of an asset for a given time period based on the fixed-declining balance method.
cost is the original cost of the asset.
salvage is the salvage value after the asset has been fully depreciated.
life is the useful life of the asset or the number of periods that you will be depreciating the asset.
period is the period that you wish to calculate the depreciation for. Use the same units as for the life.
factor is optional. It is the rate at which the balance declines. If this parameter is omitted, the DDB function will assume the factor to be 2.
=DDB(3000,300,10,1,2)
DOLLARDE Syntax: DOLLARDE(fractional_dollar, fraction)

Converts a dollar price expressed as an integer part and a fraction part, such as 1.02, into a dollar price expressed as a decimal number.
=DOLLARDE(1.125, 8)
DOLLARFR Syntax: DOLLARFR(decimal_dollar, fraction)

Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction.
=DOLLARFR(1.125, 8)
EFFECT Syntax: EFFECT(nominal_rate, npery)
This function returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.
=EFFECT(A2,A3)
FV Syntax: FV(interest_rate, number_payments, payment, [PV], [Type])
This function returns the future value of an investment based on an interest rate and a constant payment schedule.
interest_rate is the interest rate for the investment.
number_payments is the number of payments for the annuity.
payment is the amount of the payment for each period. This should be entered as a negative value.
PV is optional. It is the present value of the payments.
Type is optional. It indicates when the payments are due. Type can be one of the following values: 0 - Payments are due at the end of the period. (default), 1 - Payments are due at the beginning of the period.
=FV(6%/12, 10, -800, -2000)
FVSCHEDULE Syntax: FVSCHEDULE(principal, schedule)
This function returns the future value of an initial principal after applying a series of compound interest rates.
=FVSCHEDULE(1,a2:a5)
IPMT Syntax: IPMT(interest_rate, period, number_payments, PV, [FV], [type])
Returns the interest payment for an investment based on an interest rate and a constant payment schedule.
period is the period to calculate the interest rate. It must be a value between 1 and number_payments.
interest_rate is the interest rate for the loan.
number_payments is the number of payments for the loan.
PV is the present value or principal of the loan.
FV is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, the PMT function assumes a FV value of 0.
Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.
=IPMT(10%, 3, 5000, 20)
IRR Syntax: IRR(range, [estimated_irr])
This function returns the internal rate of return for a series of cash flows represented by the numbers in values. The cash flows must occur at regular intervals, but do not have to be the same amounts for each interval.
range is a range of cells that represent the series of cash flows.
estimated_irr is optional. It is the your guess at the internal rate of return.
=IRR(A1:A6)
MIRR Syntax: MIRR(range, finance_rate, reinvestment_rate)
This function returns the modified internal rate of return for a series of cash flows. The internal rate of return is calculated by using both the cost of the investment and the interest received by reinvesting the cash.
range is a range of cells that represent the series of cash flows.
finance_rate is the interest rate that you pay on the cash flow amounts.
reinvestment_rate is the interest rate that you receive on the cash flow amounts as they are reinvested.
=MIRR(A1:A9,A10,12%)
NOMINAL Syntax: NOMINAL(effect_rate, npery)
This function returns the nominal interest rate for a given effective interest rate and number of compounding periods per year.
=NOMINAL(10%, 4)
NPER Syntax: NPER(interest_rate, payment, PV, [FV], [Type])
This function returns the number of periods for an investment based on an interest rate and a constant payment schedule.
interest_rate is the interest rate for the investment.
payment is the amount of the payment for each period. This should be entered as a negative value.
PV is the present value of the payments.
FV is optional. It is the future value that you\'d like the investment to be after all payments have been made. If this parameter is omitted, the NPER function will assume a FV of 0.
Type is optional. It indicates when the payments are due. Type can be one of the following values: 0 - Payments are due at the end of the period. (default), 1 - Payments are due at the beginning of the period.
=NPER(12%/12, -100, 500, 10000)
=NPER(A1/12, A2, A3, A4)
NPV Syntax: NPV(discount_rate, value1, value2, ... value_n)
This function returns the net present value of an investment.
discount_rate is the discount rate for the period.
value1, value2, ... value_n are the future payments and income for the investment (ie: cash flows).
=NPV(A1, A2:A8)
PMT Syntax: PMT(interest_rate, number_payments, PV, [FV], [type])
Returns the payment amount for a loan based on an interest rate and a constant payment schedule.
interest_rate is the interest rate for the loan.
number_payments is the number of payments for the loan.
PV is the present value or principal of the loan.
FV is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, the PMT function assumes a FV value of 0.
Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.
=PMT(6%, -100, 500)
=PMT(a1, a2,a3)
PPMT Syntax: PPMT(interest_rate, period, number_payments, PV, [FV], [type])
Returns the payment on the principal for a particular payment based on an interest rate and a constant payment schedule.
interest_rate is the interest rate for the loan.
period is the period used to determine how much principal has been repaid. Period must be a value between 1 and number_payments.
number_payments is the number of payments for the loan.
PV is the present value or principal of the loan.
FV is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, the PPMT function assumes a FV value of 0.
Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.
=PPMT(A1, A2, 10, A3)
=PPMT(5%, 20, 8, 8000)
PV Syntax: PV(interest_rate, number_payments, payment, [FV], [Type])
This function returns the present value of an investment based on an interest rate and a constant payment schedule.
interest_rate is the interest rate for the investment.
number_payments is the number of payments for the annuity.
payment is the amount of the payment made each period.
FV (optional) is the desired value (future value) to be reached at the end of the periodic payments.
Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.
=PV(0.5, 50, 600, 1, 0)
RATE Syntax: RATE(NPER, PMT, PV, [FV], [type], [guess])
Returns the constant interest rate per period of an annuity.
NPER is the total number of periods, during which payments are made (payment period).
PMT is the constant payment (annuity) paid during each period.
PV is the cash value in the sequence of payments.
FV (optional) is the future value, which is reached at the end of the periodic payments.
Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.
Guess (optional) determines the estimated value of the interest with iterative calculation.
=RATE(A2*12, A3, A4)
SLN Syntax: SLN(cost, salvage, life)
This function returns the depreciation of an asset for a period based on the straight-line depreciation method.
cost is the original cost of the asset.
salvage is the salvage value after the asset has been fully depreciated.
life is the useful life of the asset or the number of periods that you will be depreciating the asset.
=SLN(A1, A2, A3)
=SLN(8000, 1000, 10)
SYD Syntax: SYD(cost, salvage, life, period)
This function returns the depreciation of an asset for a given time period based on the sum-of-years digits depreciation method.
cost is the original cost of the asset.
salvage is the salvage value after the asset has been fully depreciated.
life is the useful life of the asset or the number of periods that you will be depreciating the asset.
period is the period that you wish to calculate the depreciation for. Use the same units as for the life.
=SYD(A1,A2,A3,1)
=SYD(8000, 1000, 10,10)
TBILLEQ Syntax: TBILLEQ(settlement, maturity, discount)
Returns the bond-equivalent yield for a Treasury bill.
=TBILLEQ(A2,A3,A4)
TBILLPRICE Syntax: TBILLPRICE(settlement, maturity, discount)
Returns the price per \$100 face value for a Treasury bill.
=TBILLPRICE(A2,A3,A4)
TBILLYIELD Syntax: TBILLYIELD(settlement, maturity, pr)
Returns the yield for a Treasury bill.
=TBILLYIELD(A2,A3,A4)
VDB Syntax: VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])
Returns the depreciation of an asset for any period you specify, including partial periods, using the double-declining balance method or some other method you specify. VDB stands for variable declining balance.

Cost: The initial cost of the asset.

Salvage: The value at the end of the depreciation. This value can be 0.

Life: the number of periods over which the asset is depreciated.

Start_period: The starting period for which you want to calculate the depreciation. Start_period must use the same units as life.

End_period: The ending period for which you want to calculate the depreciation. End_period must use the same units as life.

Factor: The rate at which the balance declines. If factor is omitted, it is assumed to be 2.

No_switch: A logical value specifying whether to switch to straight-line depreciation when depreciation is greater than the declining balance calculation.
=VDB(A2, A3, A4, 0, 0.875, 1.5)
XIRR Syntax: XIRR(values, dates, [guess])
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
=XIRR(A3:A7, B3:B7, 0.1)
XNPV Syntax: XNPV(rate, values, dates)
Returns the net present value for a schedule of cash flows that is not necessarily periodic.
=XNPV(0.09, A3:A7, B3:B7)
VDB Syntax: VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])
Returns the depreciation of an asset for any period you specify, including partial periods, using the double-declining balance method or some other method you specify. VDB stands for variable declining balance.
=VDB(A2, A3, A4*12, 0, 1)