Excel 2013 & 2016: Financial Functions

Updated 2013.08.07

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