Excel 2016: Financial Functions

Updated 2017.07.30

Objectives

·        To learn how to determine the payment on a loan using the PMT() function.

·        To learn how to use the FV() function to determine how much money you will have in the future.

·        To learn how to use the PV() function to figure out much money you need to have right now if you want to reach a given savings goal.

·        To learn how to use the RATE() function to determine what interest rate you are paying.

·        To learn how to use the NPER() function to determine how long it will take to pay off a loan.

Microsoft page: Building Formulas Faster

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:

FV

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.

PMT

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

PV

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.

RATE

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 the annual rate by 2. If payments are quarterly, divide the annual rate by 4. And if payments are monthly, divide the annual rate by 12.

NPER

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.

TYPE

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.