Excel 2016: Financial Functions

Updated 2017.07.30

·
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** 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** is the regular amount that is paid when
repaying a loan

**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** 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** 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** 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.