Excel Functions and Formulas

 

 

Excel tips on functions and formulas

Excel Functions: Examples and Tutorial

Excel macros (VBA)

Excel and VBA Expert

Excel fonctions et formules

Excel Functions and Formulas

 

 

Lesson 4: Excel Spreadsheets Financial Functions (53)

Frequency * Functions What it Does
Rarely Used FV Returns the future value of an investment
Rarely Used NPER Returns the number of periods for an investment
Rarely Used PMT Returns the periodic payment for an annuity
Rarely Used RATE Returns the interest rate per period of an annuity
Rarely Used ACCRINT Returns the accrued interest for a security that pays periodic interest
Rarely Used ACCRINTM Returns the accrued interest for a security that pays interest at maturity
Rarely Used AMORDEGRC Returns the depreciation for each accounting period by using a depreciation coefficient
Rarely Used AMORLINC Returns the depreciation for each accounting period
Rarely Used COUPDAYBS Returns the number of days from the beginning of the coupon period to the settlement date
Rarely Used COUPDAYS Returns the number of days in the coupon period that contains the settlement date
Rarely Used COUPDAYSNC Returns the number of days from the settlement date to the next coupon date
Rarely Used COUPNCD Returns the next coupon date after the settlement date
Rarely Used COUPNUM Returns the number of coupons payable between the settlement date and maturity date
Rarely Used COUPPCD Returns the previous coupon date before the settlement date
Rarely Used CUMIPMT Returns the cumulative interest paid between two periods
Rarely Used CUMPRINC Returns the cumulative principal paid on a loan between two periods
Rarely Used DB Returns the depreciation of an asset for a specified period using the fixed-declining balance method
Rarely Used DDB Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify
Rarely Used DISC Returns the discount rate for a security
Rarely Used DOLLARDE Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
Rarely Used DOLLARFR Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
Rarely Used DURATION Returns the annual duration of a security with periodic interest payments
Rarely Used EFFECT Returns the effective annual interest rate
Rarely Used FVSCHEDULE Returns the future value of an initial principal after applying a series of compound interest rates
Rarely Used INTRATE Returns the interest rate for a fully invested security
Rarely Used IPMT Returns the interest payment for an investment for a given period
Rarely Used IRR Returns the internal rate of return for a series of cash flows
Rarely Used ISPMT Returns the interest paid during a specific period of an investment
Rarely Used MDURATION   Returns the Macauley modified duration for a security with an assumed par value of $100
Rarely Used MIRR Returns the internal rate of return where positive and negative cash flows are financed at different rates
Rarely Used NOMINAL Returns the annual nominal interest rate
Rarely Used NPV Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
Rarely Used ODDFPRICE Returns the price per $100 face value of a security with an odd first period
Rarely Used ODDFYIELD Returns the yield of a security with an odd first period
Rarely Used ODDLPRICE Returns the price per $100 face value of a security with an odd last period
Rarely Used ODDLYIELD Returns the yield of a security with an odd last period
Rarely Used PPMT Returns the payment on the principal for an investment for a given period
Rarely Used PRICE Returns the price per $100 face value of a security that pays periodic interest
Rarely Used PRICEDISC Returns the price per $100 face value of a discounted security
Rarely Used PRICEMAT Returns the price per $100 face value of a security that pays interest at maturity
Rarely Used PV Returns the present value of an investment
Rarely Used RECEIVED Returns the amount received at maturity for a fully invested security
Rarely Used SLN Returns the straight-line depreciation of an asset for one period
Rarely Used SYD Returns the sum-of-years' digits depreciation of an asset for a specified period
Rarely Used TBILLEQ Returns the bond-equivalent yield for a Treasury bill
Rarely Used TBILLPRICE Returns the price per $100 face value for a Treasury bill
Rarely Used TBILLYIELD Returns the yield for a Treasury bill
Rarely Used VDB Returns the depreciation of an asset for a specified or partial period using a declining balance method
Rarely Used XIRR Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
Rarely Used XNPV Returns the net present value for a schedule of cash flows that is not necessarily periodic
Rarely Used YIELD Returns the yield on a security that pays periodic interest
Rarely Used YIELDDISC Returns the annual yield for a discounted security for example, a Treasury bill
Rarely Used YIELDMAT Returns the annual yield of a security that pays interest at maturity

The financial functions are among the less user friendly, the less intuitive in Excel. Here are 4 examples trying to clarify the matter.

The RATE Function

The question to which RATE  brings an answer to is:
- What is the real interest rate if they ask me for a certain amount each period to pay a loan?

 

A

Descriptions

1

48

Number of periods (years, months, weeks..etc)

2

$550

Periodic payment

3

$24,000

Total amount of loan

4

0

The balance left to pay at the end of the period. If you omit this argument Excel uses "0".

5

0

Payment made at the beginning of the period (1) or at the end of the period (0). If you omit this argument Excel uses "0" saying that the payment is made at the end of each period which is usually the reality when you borrow money.

6

5.00%

The result with the formula using the RATE function.
Note: the format of this cell must be "Percentage" with any number of decimals. In this example the number of decimals is 2

Here is the formula in cell A6:
=RATE(A1,-A2,A3,A4,A5)*12

Notes on the formula: The payment argument is negative (-A2); If you use months as periods and you want an annual rate you multiply by 12, if you use a years as periods and you want an annual rate you don't multiply......; If you don't use the "Percentage" format in cell A6 the result of this example will be 0.05; The formula could also be =RATE(A1,-A2,A3)*12 the arguments in A4 and A5 being optional

The PMT Function

The question to which PMT brings an answer to is:
- If I borrow a certain amount of money and I want it repaid at the end of a certain period of time what will be the periodic payment?

 

A

Descriptions

1

5.00%

The annual interest rate.
Note: the format of this cell must be "Percentage" with any number of decimals. In this example the number of decimals is 2

2

48

Number of periodic payments (years, months, weeks)

3

$24,000

Total amount of loan

4

0

The balance left to pay at the end of the period. If you omit this argument Excel uses "0".

5

0

Payment made at the beginning of the period (1) or at the end of the period (0). If you omit this argument Excel uses "0" saying that the payment is made at the end of each period which is usually the reality when you borrow money.

6

-$550.41

The result with the formula using the PMT function.

Here is the formula in cell A6:
=PMT(A1/12,A2,A3,A4,A5)

Notes on the formula: If you don't use the "Percentage" format in cell A1 enter 0.05; If you use months as periods the rate must be divided by 12 (A1/12), if you use weeks then you divide by 52 (A1/52), if there are 4 payments per year you will divide the rate by 4 (A1/4)and if the payment is annual you don't divide the rate argument (A1) ; The formula could also be =PMT(A1/12,A2,A3) the arguments in A4 and A5 being optional; If you want the payment to show as a positive value add a minus sign before the equal sign (=-PMT(A1/12,A2,A3,A4,A5))

The FV Function (Future value)

The question to which FV  brings an answer to is:
- If I put a certain amount of money in the bank each month how much money will I have saved at the end of a certain period of time?

 

A

Descriptions

1

5.00%

The annual interest rate.
Note: the format of this cell must be "Percentage" with any number of decimals. In this example the number of decimals is 2

2

48

Number of periodic deposits (years, months, weeks)

3

$550

Amount of periodic deposits

4

$0

Beginning balance. If you omit this argument Excel uses "0".

5

1

Deposits made at the beginning of the period (1) or at the end (0). If you omit this argument Excel uses "0". In the case of the FV function make sure that you enter "1".

6

-$29,279.68

The result with the formula using the FV function.

Here is the formula in cell A6:
=FV(A1/12,A2,A3,A4,A5)

Notes on the formula: If you don't use the "Percentage" format in cell A1 enter 0.05; If you use months as periods the rate must be divided by 12 (A1/12), if you use weeks then you divide by 52 (A1/52), if there are 4 payments per year you will divide the rate by 4 (A1/4)and if the payment is annual you don't divide the rate argument (A1) ; The formula could also be =FV(A1/12,A2,A3) the arguments in A4 and A5 being optional; If you want the RESULT to show as a positive value add a minus sign before the equal sign (=-FV(A1/12,A2,A3,A4,A5))

The NPER Function

The question to which NPER  brings an answer to is:
- How many months would it take me to repay a certain loan at a certain interest rate if I pay a certain amount each month?

 

A

Descriptions

1

5.0%

The annual interest rate.
Note: the format of this cell must be "Percentage" with any number of decimals. In this example the number of decimals is 2

2

$550

Periodic payment

3

$24,000

Total amount of loan

4

0

The balance left to pay at the end of the period. If you omit this argument Excel uses "0".

5

0

Payment made at the beginning of the period (1) or at the end (0). If you omit this argument Excel uses "0".

6

48.26

The result with the formula using the NPER function.

Here is the formula in cell A6:
=NPER(D1/12,-D2,D3,D4,D5)

Notes on the formula: If you don't use the "Percentage" format in cell A1 enter 0.05; The second argument MUST BE NEGATIVE; If you use months as periods the rate must be divided by 12 (A1/12), if you use weeks then you divide by 52 (A1/52), if there are 4 payments per year you will divide the rate by 4 (A1/4)and if the payment is annual you don't divide the rate argument (A1) ; The formula could also be =NPER(A1/12,A2,A3) the arguments in A4 and A5 being optional;

Financial Functions in Excel
Links to the Top of the Page


If you are using a version of Excel earlier than 2007 you need to activate the "Excel Analysis Toolpack" to make some of the functions above available.


Excel Functions and Formulas Sitemap

Downloadable Tutorials, Examples and Templates

 

 

Excel tips on functions and formulas

Excel Functions: Examples and Tutorial

Excel macros (VBA)

Excel and VBA Expert

Excel fonctions et formules