Microsoft Excel Financial Functions

1
Microsoft Excel Financial Functions
Objectives

• Understanding and using Financial Functions
• the time value of money
• PV, FV, Rate, NPER, PMT
• problem solving

2
Simple Interest vs. Compound Interest

• Simple interest always calculates interest based
  on the original amount.
• So 1000 at 4 per year for 2 years
• Year 1 1000 4 ? 40 in interest for the
  first year.
• Year 2 1000 4 ? 40 in interest for the
  second year.

So after 2 years you would have 1000 4 2 ?
80 interest For a total of 1080
3
Simple Interest vs. Compound Interest

• Compound interest - always calculate interest
  based on latest amount
• Year 1 1000 at 4/yr for 1 year is 40
• Year 2 1040 4 41.60

so now after 2 years you have 1081.60
4
Compounding Interest Quarterly
What if we compound our interest quarterly
instead of yearly? 1000 at 4 per year
compounded quarterly for one year is actually 4
separate calculations Each quarter updating the
principa1 and using the rate 1 per quarter.
Principal Interest 1st quarter
1000.001 10.00 2nd quarter 1010.001
10.10 3rd quarter 1020.101 10.201 4th
quarter 1030.3011 10.30
Total interest for year 1 40.60 vs. 40 for
simple interest
5
Financial Functions

• Functions that can be used to calculate values
  based on compounded interest
• - Taking a loan
• - Investing in a savings account
• The basic financial functions use these 5 basic
  variables
• PV, FV, RATE, PMT, NPER
• Other functions are also available NPV, PPMT,
  IPMT

6
The Basics

• PV present value, what you get/pay at the
  beginning of the financial transaction
• FV future value, what you are going to get OR
  what you will have to pay at the end of the
  financial transaction
• PMT payment made each period. It remains
  constant over life of annuity
• RATE interest rate per period
• NPER number of payment periods

7
100 Loan for 2 Years Compounded Quarterly at 8
per year
End FV 0
Beginning PV 100
PMT
PMT
PMT
PMT
PMT
PMT
PMT
PMT
2 RATE for each of 8 Quarters 13.65 PMT for
each of 8 Quarters
Interest RATE per compounding period (8 per
yr/4 qtr per year) for NPER periods (2yrs 4
Qtr/yr) with Payments PMT(13.65) - In/Out at
Equal Intervals
8
PV ( ) Present Value - What I have at the
beginning

• How much money would I have to set aside now to
  have a 5000 down payment on a car when I
  graduate in 2 years? I plan to put the money in
  a CD that pays 3 annual interest compounded
  yearly.
• PV(ltrategt, ltnpergt, ltpmtgt, ltfvgt, lttypegt)
• RATE 3 (per year) interest per period
• NPER 2 (years) number of periods
• PMT 0 (per year) payment per period
• FV 5000 - amount at the end of the
  transaction
• PV(0.03, 2, 0,5000)

3 RATE per period
9
When using Financial Functions remember to..

• Use consistent units of time
• RATE per quarter, NPER number of quarters and PMT
  payment per quarter.
• Use consistent signs
• outgoing cash (- ), incoming cash ( )
• For arguments that are zero at least a comma must
  be put into the function to maintain the argument
  order, unless no other non-zero arguments follow
  then it many be deleted.
• PV(0.03, 2, 0, 5000,0) same as PV(0.03, 2, ,
  5000)

10
FV ( ) Future Value - What I have at the end

• I plan on depositing 5000 into a CD that
  pays 3 annual interest compounded monthly. I
  plan to add an additional 50 each month. How
  much will I have at the end of 2 years?
• FV(ltrategt, ltnpergt, ltpmtgt, ltpvgt, lttypegt)
• RATE 3/12 ? .025 (per month) interest per
  period
• NPER 2 12 ? (months) number of periods
• PMT -50 (per month) payment per period
• PV -5000 - amount at the beginning of the
  transaction

FV(0.03/12, 212, -50 , -5000)
?
50
50
5000
50
.025 RATE per period for 24 periods
11
PMT( ) Returns the periodic payment

• I have been offered a 5 year car loan of
  15,000 at 9 annual interest rate compounded
  monthly. What is the monthly payment is needed to
  completely pay off the loan at the end of the 5
  years?
• PMT(ltrategt, ltnpergt, ltpvgt, ltfvgt, lttypegt)
• PMT(B3/B5, B4B5, B1, B2)

Will your payment be a positive or negative value?
12
Rate( ) Returns Rate per Period
18,999 for a new Chevy XP 2000 down and
350/month For 5 years

• What is the annual rate of interest of this loan
  assuming it is compounded monthly.

RATE(ltnpergt, ltpmtgt, ltpvgt, ltfvgt, lttypegt)
RATE(512, -350, 18999-2000) 12
months/yr
Remember to get the correct compounding -
calculate rate per period (month) ? then convert
it to rate per year.
13
NPER( ) Payment Periods

• Write an Excel formula to determine how many
  years will it take to save 12,000 if I put
  10,000 into a savings account paying 4 annual
  interest compounded quarterly.
• NPER(ltrategt, ltpmtgt, ltpvgt, ltfvgt, lttypegt)
• NPER(4/4, , -10000, 12000) /4 quarters/yr

Remember to get the correct compounding -
calculate the number of periods (quarterly) and
then convert to years.
14
The type argument

• Type If payments are made
• 0 (default) At the end of the period
• 1 At the beginning of the period
• Example
• Type 0 You make a car payment to the bank at the
  end of each month to pay down the principal
• Type 1 An annuity pays you a set amount each
  month at the beginning of the month

Unless specifically mentioned assume type 0
15
The type argument

• I have been putting 100 per quarter in the bank
  for the past 10 years in an effort to save money
  for my childs college education. How much money
  is currently in this account assuming the bank
  has paid a 3 annual interest rate compounded
  quarterly?

Make Payments at the End of Each
Quarter FV(.03/4, 410, -100,0,0)
Make Payments at the Beginning of Each
Quarter FV(.03/4, 410, -100,0,1)
16
Another problem

• Write an Excel formula in cell D4 that can be
  copied down the column to calculate the monthly
  payment for each of the mortgages listed. The
  annual interest rate is 4 compounded monthly.
  Note A balloon payment is an amount due at the
  end of the loan.
• PMT(ltrategt, ltnpergt, ltpvgt, ltfvgt, lttypegt)
• PMT(B1/12, B412, A4, -C4)

17
A Summary of Financial Functions

• Financial Function can be used to calculate
  financial transactions with compound interest.
• PV, FV, PMT, NPER, RATE are all dependent on the
  values of the other four
• Use positive values for cash flow back to you,
  and negative values for cash flow from you to a
  financial institution..
• Use correct compounding periods for your values
  of NPER, PMT and RATE.
• Use the correct type argument