Excel Financial Functions

Objectives

To learn how to use the five basic financial functions: FV(), PMT(), PV(), RATE(), and NPER().

Using Excel help (F1):

In the Search for box, enter the name of the function, and press Enter.

In the Search results list, click on the name of the function and read the topics.

Excel 2003 Bible: Chapter 13

Excel is often used for financial calculations. Excel has a number of built-in financial functions. We will look at five of the most useful ones:

FV()
PMT()
PV()
RATE()
NPER()

Each financial function uses the other four values as its arguments. The arguments used in the financial functions are:

PMT is the regular amount that is paid when repaying a loan or the amount that is deposited into an investment (like a savings account). It is always a negative number (it is negative because it is a quantity that is going out of your checking account, even if it is going into your savings account!).

RATE is the interest rate per period for the investment. While interest rates are normally quoted as annual rates, all of the financial functions depend on a per-period interest rate. If payments are annual, the rate does not need to be modified. If payments are semiannual, divide by 2. If payments are quarterly, divide by 4. And if payments are monthly, divide by 12.

NPER is the total number of periods for the investment. Usually you will be given the number of years and the number of payments per year. These two numbers must be multiplied together to determine the number of periods. If payments are semiannual, multiply years by 2 to get the number of payment. If payments are quarterly, multiply years by 4 to get the number of payments. And if payments are monthly, multiply years by 12 to get the number of payments.

PV is the amount of the investment today. When borrowing money, the present value is the amount of the loan, also called the principal and is a positive number. When adding to a savings account, this is the initial balance in the savings account, and is a negative number.

FV is the future value of the investment. If you are paying off a loan, you want the future value (amount you owe at the end of the loan) to be 0. If you are adding to a savings account, this is the desired savings goal.

TYPE is the number 0 (zero) or 1 and indicates when payments are due. Zero (or omitted) means at the end of the period, 1 indicates the beginning of the period.

Updated 2006.04.05