Advanced Excel Functions 简明教程

S.No.

Function and Description

1

ACCRINTReturns the accrued interest for a security that pays periodic interest

2

ACCRINTMReturns the accrued interest for a security that pays interest at maturity

3

AMORDEGRCReturns the depreciation for each accounting period

4

AMORLINCReturns the depreciation for each accounting period (the depreciation coefficient depends on the life of the assets)

5

COUPDAYBSReturns the number of days from the beginning of the coupon period to the settlement date

6

COUPDAYSReturns the number of days in the coupon period that contains the settlement date

7

COUPDAYSNCReturns the number of days from the settlement date to the next coupon date

8

COUPNCDReturns the next coupon date after the settlement date

9

COUPNUMReturns the number of coupons payable between the settlement date and maturity date

10

COUPPCDReturns the previous coupon date before the settlement date

11

CUMIPMTReturns the cumulative interest paid between two periods

12

CUMPRINCReturns the cumulative principal paid on a loan between two periods

13

DBReturns the depreciation of an asset for a specified period, using the fixed-declining-balance method

14

DDBReturns the depreciation of an asset for a specified period, using the double-declining-balance method or some other method that you specify

15

DISCReturns the discount rate for a security

16

DOLLARDEConverts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number

17

DOLLARFRConverts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction

18

DURATIONReturns the annual duration of a security with periodic interest payments

19

EFFECTReturns the effective annual interest rate

20

FVReturns the future value of an investment

21

FVSCHEDULEReturns the future value of an initial principal after applying a series of compound interest rates

22

INTRATEReturns the interest rate for a fully invested security

23

IPMTReturns the interest payment for an investment for a given period

24

IRRReturns the internal rate of return for a series of cash flows

25

ISPMTCalculates the interest paid during a specific period of an investment

26

MDURATIONReturns the Macauley modified duration for a security with an assumed par value of $100

27

MIRRReturns the internal rate of return where positive and negative cash flows are financed at different rates

28

NOMINALReturns the annual nominal interest rate

29

NPERReturns the number of periods for an investment

30

NPVReturns the net present value of an investment based on a series of periodic cash flows and a discount rate

31

ODDFPRICEReturns the price per $100 face value of a security with an odd first period

32

ODDFYIELDReturns the yield of a security with an odd first period

33

ODDLPRICEReturns the price per $100 face value of a security with an odd last period

34

ODDLYIELDReturns the yield of a security with an odd last period

35

PDURATIONReturns the number of periods required by an investment to reach a specified value

36

PMTReturns the periodic payment for an annuity

37

PPMTReturns the payment on the principal for an investment for a given period

38

PRICEReturns the price per $100 face value of a security that pays periodic interest

39

PRICEDISCReturns the price per $100 face value of a discounted security

40

PRICEMATReturns the price per $100 face value of a security that pays interest at maturity

41

PVReturns the present value of an investment

42

RATEReturns the interest rate per period of an annuity

43

RECEIVEDReturns the amount received at maturity for a fully invested security

44

RRIReturns an equivalent interest rate for the growth of an investment

45

SLNReturns the straight-line depreciation of an asset for one period

46

SYDReturns the sum-of-years’ digits depreciation of an asset for a specified period

47

TBILLEQReturns the bond-equivalent yield for a Treasury bill

48

TBILLPRICEReturns the price per $100 face value for a Treasury bill

49

TBILLYIELDReturns the yield for a Treasury bill

50

VDBReturns the depreciation of an asset for a specified or partial period using a declining-balance method

51

XIRRReturns the internal rate of return for a schedule of cash flows that is not necessarily periodic

52

XNPVReturns the net present value for a schedule of cash flows that is not necessarily periodic

53

YIELDReturns the yield on a security that pays periodic interest

54

YIELDDISCReturns the annual yield for a discounted security, for example, a Treasury bill

55

YIELDMATReturns the annual yield of a security that pays interest at maturity