Transcript Note to trainers The videos in this presentation provide closed-caption files as it’s often easier for viewers of all abilities to follow.

Note to trainers
The videos in this presentation provide closed-caption files as it’s often easier for viewers of all
abilities to follow along.
If you wish to remove the closed captions, you must install the Subtitling Add-in for Microsoft
PowerPoint (STAMP). You can download the add-in from http://sourceforge.net/projects/stampaddin/files/Latest%20Version/
Once you install the add-in, to turn the captions off:
1.
Locate a slide that contains a video and click the video.
The Playback contextual tab appears on the top of the window. If you don’t click the video, the
Playback tab won’t be visible.
2.
On the Playback tab, click Hide.
3.
Go to the next slide and repeat until you’ve hidden the captions all slides or for any of the
videos with captions you wish to hide. Note that if you save the file, the captions will stay
hidden when you re-open the presentation.
4.
To show the captions again, repeat steps 1-3, but click Show in step 2. If you click Remove
during step 2, the captions will be permanently removed from the video.
Before you begin
If a yellow security bar appears at the top of the
screen in PowerPoint, click Enable Editing.
You need PowerPoint 2010 to view this
presentation. If you don’t have PowerPoint 2010,
download the PowerPoint Viewer (see the URL in
the notes pane).
®
®
Microsoft Excel 2010 Training
Plan payments and savings
Course contents
• Overview: Use financial functions in Excel 2010
• Lesson: Includes 7 instructional movies
• Quick Reference Card
Plan payments and savings
Overview: Use financial
functions
Learn how to figure out payments and
savings by using formulas in Excel. This
course explains how to calculate
mortgage and down payments, save for
a vacation, and calculate how much
your savings will grow over time.
Plan payments and savings
Course goals
•
Figure out the monthly payments to pay off a credit
card debt.
•
Figure out monthly mortgage payments.
•
Find out how much to save each month for a dream
vacation.
•
Find out long it will take to pay off a personal loan.
•
Figure out a down payment.
•
See how much your savings will add up to over time
Plan payments and savings
Calculate credit card debt (2:50)
that’s
You
Imagine
Suppose
First,
On
Its
That’s
I’ll
IIn
Which
To
I'll
The
Look
will
the
other
find
adjust
move
description
the
can
explanation
result
monthly
enter
at
let’s
over
Search
the
displays
is
out
ribbon,
the
use
words,
athe
my
also
the
see
credit
function
is2*12;
awhat
difference
aacurser
thousand
present
payment
payments
formula
that
for
financial
what
sum
says
I'll
all
Nper
for
card
ita2click
the
nothing
function
would
that
years
Ito
that
your
Rate
want.
value,
is
balance
in
the
required
in
would
the
dollars
function
the
would
must
it:
the
monthly
interest
multiplied
iscost
NPer
more
Calculates
I’ll
Formulas
down
total
that’s
box,
Formula
be
select
of
more
to
be
arguments
box,
in
$266.99
will
paid
$5,400
I’ll
number
pay
though
here.
$155.82
payments
the
atype
by
in
it
formula
which
be
tab.
the
bar,
the
out
Credit
in
interest
12
It’s
charged
with
the
monthly
over
debt
of
Then
to
payment
by
and
monthly
to
in
the
calculates
payments
pay
would
revising
card
list
to
pay
abold
thus
time.
off
interest
I’ll
17%
ifhelp
and
the
to
the
balance
payment,
in
click
face.
payments.
afor
be
this
annual
2debt
four
then
you
negative
debt
this
the
years
in
for
aInsert
rate
The
loan
2off
the
0f
is
off
to
figure
interest
account
order
Function.
then
based
click
curser
for
number
Iloan.
$5,400
number,
years,
4
in
equals
paid
Iwill
which
4
will
for
years
two
four
the
OK.
out
move
I’ll
four
enter
to
out
on
is
years.
ais
years.
loan.
dollars.
click
equals
rate.
while
of
pay
bit
in
over
That
indicated
constant
years,
how
payment
the
my
17%/12,
over
off
Go.
4opens
the
curser
Rate
I’ll
ato
years
the
and
bit
PMT
$6,400
pay
click
balance
payments
by
debt
box.
over
periods.
the
then
instead
to
aoff
isOK.
red
first
the
number
that
Function
in
$7,400.
credit
pressing
isfont
2Pv
in
being
you
and
of
years.
the
box.
card
2color
of
will
ayears.
Arguments
Enter.
paid
list.
constant
months
balances.
pay
and
off.
altogether.
parentheses.
ininterest
dialog
a year.box.
rate.
Point to the bottom of the video to see the video controls. Drag or point
along the progress bar to move forward or go back.
Plan payments and savings
Figure out mortgage payments (2:45)
I’ll type a comma to separate the rate argument from the
next
Saythe
and
You
Formula
Notice
The
Finally,
Now
To
I’ll
In
And
Since
find
type
enter
click
that
could
ScreenTip
first
you
which
in
let’s
this
Search
Rate
the
the
I’ll
PV,
my
wonder
you’ve
argument
AutoComplete
a30,
look
is
use
enter
comma
is
payment
arguments
function
the
box
equals
afor
Nper,
for
payment,
the
here
at
first
I’ll
looked
amy
what
athis
30
PMT
type
to
sign
function
the
isfunction
tells
closing
to
box
year
Rate,
separate
differently
in
the
use
total
at
to
function
5%/12,
I’ll
lists
me
I’ll
brackets,
$180,000
loan,
start
monthly
put
which
I’ll
parenthesis.
type
box
number
all
the
inclick
the
athe
multiplied
inI’ll
function.
to
by
the
negative
isthe
FV
arguments
the
type
formula
figure
arguments
list
payment,
finding
5%/12,
house
mortgage
of
and
number
which
Formulas
payments
monthly
The
out
sign
type.
I’ll
by
at
the
the
and
monthly
5%
double-click
twelve
returns
the
minus
and
of
in
for
payment
value
number
The
then
annual
tab
front
payments
monthly
for
the
the
brackets
because
on
of
start
the
final
ofthe
of
I the
can
the
to
interest
insurance
payments.
type
function
formula.
indicate
months
loan.
there
argument
payment
house
Ribbon
and
present
box,
afford
800,
Thisthen
and
$180,000;
Then
The
Nper,
house
enters
then
PMT.
time
helps
will
I’ll
to
based
and
in
value
that
to
click
pay
would
click
be
I’ll
and
aI’ll
prevent
the
Iget
the
then
year.
can
click
twelve
these
on
type
enter
each
type
OK.
of
Go.
taxes,
present
itopening
be
what
an
afford
click
into
OK.
the
month,
30*12.
is
are
$966.28.
any
investment.
monthly
would
Pv,
the
you
Insert
formula
optional
value
to
misspellings
parenthesis
formula.
buy
can
less
be.
Function.
of
payments
costs
afford
directly
insurance
the
arguments.
home.
just
of
of
tointo
function
the
pay
per
aand
little
the
formula
year.
each
taxes.
over
spreadsheet.
names
month.
for
$149,000.
you.
Point to the bottom of the video to see the video controls. Drag or point
along the progress bar to move forward or go back.
Plan payments and savings
Calculate vacation savings (1:25)
Imagine
and
You
Formula
First
The
I’ll
type
enter
earn
NPER
you
I’ll enter
the
aneed
AutoComplete
aan
argument,
dream
comma
formula
annual
the
to figure
vacation
Rate
and
interest
directly
total
argument,
then
out
offers
that
number
how
rate
enter
inthe
the
will
much
ofwhich
function
the
spreadsheet
of
cost
1.5%
payments,
PV,
FV
toyou
isand
save
argument,
or
1.5%/12,
and
$8,500
present
are
each
I’ll
by
which
starting
doublestarting
month
in
followed
Future
value
three
is to
years,
pay
from
with
click
by
3*12.
argument,
Value,
The
Then
You
afor
comma
can
would
function
it
my
aI’ll
which
to
zero
the
equal
use
enter
get
which
vacation.
need
balance
to
the
is
itarguments
sign,
my
into
8500.
separate
PMT
to
isclosing
0.
the
save
then
infunction
the
formula.
the
$230.99
are
I’ll
parenthesis
account.
begin
argument
here
to every
find
in
to the
type
out
and
from
month
ScreenTip.
PMT.
how
press
thefor
much
next.
Enter.
three
to save.
years.
Point to the bottom of the video to see the video controls. Drag or point
along the progress bar to move forward or go back.
Plan payments and savings
How a first deposit affects savings (1:26)
I would
Your
you
I’ll
and
-175,
use
double-click
enter
want
that’s
dream
followed
the
need
ato
3*12
comma
PV
vacation
find
to
function
by
PV
followed
deposit
out
another
to
in how
still
Formula
separate
which
$1,969.62
costs
by
much
comma
a will
comma,
AutoComplete
the
$8,500,
tocalculate
argument,
and
deposit
in order
next
but
finally
instead
now
the
to
my
to
which
the
pay
starting
PMT
to
enter
future
$175
keep
ofiswhich
starting
the
NPER,
ayour
value
with
monthly
deposit
function
number
has
of
month
The
I’ll
$8,500.
type
afirst
anegative
zero
and
that
of
an
my
savings
and
argument
payments,
equal
end
savings
closing
will
parenthesis
sign
up
at
yield
sign
with
$175
balance,
for
parenthesis
here
ain
afuture
$8,500
payment,
the
is
instead
into
Rate,
spreadsheet
the
value.
in
and
which
offormula.
three
$230.99
then
isyears.
1.5%/12.
press
and
per
then
Enter.
month.
type PV.
Point to the bottom of the video to see the video controls. Drag or point
along the progress bar to move forward or go back.
Plan payments and savings
Figure out a down payment (1:20)
The
Say
You
Once
In
from
I’ll
this
double-click
that
don’t
NPER,
the
again,
example,
you'd
purchase
want
orI’llthe
like
use
PV
the
the
number
to
price
the
monthly
result
buy
get
PV
to
into
aof
function,
of
find
$19,000
payments
payments,
the
the
the
PV
formula,
down
which
car
function
to
is
atbe
3*12
payment,
afigures
and
2.9%
more
is my
for
the
interest
out
than
arguments
the
loan
sothat
3I'll
$350,
year
rate
so
starting
amount,
begin
are
payment
The
and
I’ll
Your
you
type
enter
inrate
you
the
down
this
the
need
deposit
the
PMT
which
want
is
period
my
ScreenTip.
formula
2.9%/12
payment
equal
to
closing
argument
to
figure
isthat
you
pay
then
sign,
a followed
parenthesis
little
want,
will
would
the
out
subtracted
followed
isyield
total
differently.
your
entered
beby
aamount
down
$6,946.48.
future
aand
bycomma.
as19,000-PV,
press
payment.
-350.
value.
out Enter.
over 3 years.
Point to the bottom of the video to see the video controls. Drag or point
along the progress bar to move forward or go back.
Plan payments and savings
I’ll be paying on that loan until when? (1:41)
and
You’ve
To
using
The
It
Because
Use
I’ll
would
find
type
enter
first
PMT
the
I’llregular,
budgeted
end
out
my
PMT
argument
take
it’s
the
argument
I’ll
equal
up
more
rate
function
seventeen
identical
use
with
to
of
sign
than
the
isis
eighteen
pay
3%/12
Rate,
-150
NPER
followed
and
to
payments
seventeen
$150
months
find
then
which
followed
and
function,
payments
your
a month
enter
by
PMT,
and
is
with
months,
NPER.
monthly
3%/12
by
some
adouble-click
which
an
with
comma
aofcomma
I’ll
unchanging
$142.21
followed
days
you’ll
payment
adouble-click
calculates
3%
toto
and
annual
want
separate
Formula
pay
per
by
for
the
interest
to
the
aoff
an
the
PV,
interest
number
rate.
function
comma.
present
round
eighteen
AutoComplete
the
month.
Imagine
to
The
and
I’ll
let
type
loan.
arguments.
question
number
the
itupvalue,
rate.
enter
of
amy
present
to
that
month
to
closing
payments
get
closing
eighteen
of
you
is
the
isas
itpayments
how
term.
2500.
value
into
parenthesis
usual,
have
function
parenthesis
long
months.
the
of
a personal
the
formula.
itisand
will
eighteen,
loan
and
the
and
take
press
loan
isparenthesis
then
2500.
you
then
ofEnter
to
press
$2,500.
apay
comma,
enter.
for
off me.
the loan.
Point to the bottom of the video to see the video controls. Drag or point
along the progress bar to move forward or go back.
Plan payments and savings
I’ll be rich by the year ... (1:11)
In 10
You’d
and
I’ll
My
The
use
start
first
next
PMT
PV,
you
months
like
the
present
with
argument
were
argument
toFVknow
my
you
function
tovalue
equal
deposit
would
is
how
israte,
NPER
next,
argument
sign,
to
much
have
200
calculate
which
which
or
start
ayou
$2,517.57
number
month
istyping
would
is1.5%/12
the
the
-200
at
500
of
future
FV
in
have
an
followed
payments,
dollars
and
your
followed
annual
value
saved
let
savings
that
by
Formula
rate
of
in
that’s
by
athe
is
10
aof 10
months,
1.5%
investment.
AutoComplete
followed
comma.
already
account.
Say
if
and
your
that
that’s
interest.
account
inyou’re
by
account,
entered
a comma.
enter
saving
contained
asthe
-500
forfunction
new
$500
followed
furniture.
dollars
forby
me.
to
a closing
start with,
parenthesis.
Point to the bottom of the video to see the video controls. Drag or point
along the progress bar to move forward or go back.
Plan payments and savings
Quick Reference Card
How to enter functions into formulas
1. On the Formulas tab, click Insert Function.
2. In the Search for a function box, type what you are looking for and click GO.
3. Select a function in the list and read its description. If it’s the function you are looking
for, click OK to open the Function Arguments dialog box. Then enter the arguments
and click OK.
If you already know the name of the function you want, but want help with the
arguments, type the name of the function in the Search for a function box, and then
click GO. Select the function in the list, and then click OK to open the Function
Arguments dialog box.
Or, if you know the name of the function you want to use, you can type an equal (=) sign
in any empty cell in your spreadsheet. Then begin to type the function name. Formula
AutoComplete will offer you a list of functions.
Double-click the one you want to let Excel enter the function name and the formula’s
beginning parenthesis into the spreadsheet. After the function is entered in your
spreadsheet, a ScreenTip will show you the arguments you need to enter.
Plan payments and savings
Quick Reference Card 2
Pay off a credit card balance
Assume that the balance due is $5,400 at a 17% annual interest rate.
Nothing else will be purchased on the card while the debt is being paid
off.
=PMT(17%/12,2*12,5400) The result is a monthly payment of $266.99
to pay the debt off in two years.
• The Rate argument is the interest rate per period for the loan. For
example, in this formula the 17% annual interest rate is divided by
12, the number of months in a year.
• The NPER argument of 2*12 is the total number of payment periods
for the loan.
• The PV or present value argument is 5400.
Plan payments and savings
Quick Reference Card 3
Figure out monthly mortgage payments
Imagine a $180,000 home at 5% interest, with a 30-year mortgage.
=PMT(5%/12,30*12,180000) The result is a monthly payment (not
including insurance and taxes) of $966.28.
• The Rate argument is 5% divided by the 12 months in a year.
• The NPER argument is 30*12 for a 30 year mortgage with 12
monthly payments made each year.
• The PV argument is 180000 (the present value of the loan).
Plan payments and savings
Quick Reference Card 4
Figure how much to save for a vacation
You’d like to save for a vacation three years from now that will cost
$8,500. The annual interest rate for saving is 1.5%.
=PMT(1.5%/12,3*12,0,8500) To save $8,500 in three years would
require a savings of $230.99 each month for three years.
• The Rate argument is 1.5% divided by 12, the number of months in a
year.
• The NPER argument is 3*12 for twelve monthly payments over three
years.
• The PV (present value) is 0 because the account is starting from zero.
• The FV (future value) that you want to save is $8,500.
Plan payments and savings
Quick Reference Card 5
Figure out how much house you can afford to buy
Say that the interest rate is 5%, and that you can afford an $800
monthly mortgage payment, not including taxes and insurance.
=PV(5%/12,30*12,-800) The formula result is that an affordable house
would cost $149,025.29.
• The Rate argument is 5% divided by 12 months of the year.
• The NPER argument is 30*12 (or twelve monthly payments for 30
years).
• The PMT is -800 (you would pay out $800 a month).
Plan payments and savings
Quick Reference Card 6
See how a starting deposit affects savings
Imagine you have three years to save for an $8,500 vacation. You can
save $175 a month, so how much of an initial deposit will you need to
reach your goal? The PV function calculates how much of a starting
deposit you need to save that amount.
=PV(1.5%/12,3*12,-175,8500) An initial deposit of $1,969.62 is
needed if you save $175.00 per month and have $8500 in three years.
• The rate argument is 1.5%/12.
• The NPER argument is 3*12 (or twelve monthly payments for three
years).
• The PMT is -175 (you would pay $175 per month)
• The FV (future value) is 8500
Plan payments and savings
Quick Reference Card 7
Figure out a down payment
Say you’d want to buy a $19,000 car at a 2.9% interest rate over three years. You
want payments of $350 a month, so you need to figure out your down payment.
In this formula, the result of the PV function is the loan amount, which is then
subtracted from the purchase price to get the down payment.
=19000-PV(2.9%/12, 3*12,-350) The down payment required would be
$6,946.48
• The $19,000 purchase price is listed first in the formula. The result of the PV
function will be subtracted from the purchase price.
• The rate argument is 2.9% divided by 12.
• The NPER argument is 3*12 (or twelve monthly payments over three years).
• The PMT is -350 (you would pay $350 per month).
Plan payments and savings
Quick Reference Card 8
Figure out how long it will take to pay off a personal loan
You have a $2,500 personal loan, and you pay $150 a month at 3% annual
interest.
=NPER(3%/12,-150,2500) It would take 17 months and some days to pay off
the loan.
• The rate argument is 3%/12 monthly payments per year.
• The PMT argument is -150
• The PV (present value) argument is 2500
Tip
Because the payment period doesn’t come out to an even amount, you’d want
to round up to 18 months. You could do that using the PMT function.
=PMT(3%/12,18,2500) Results in 18 payments of $142.21 per month
In this formula the rate argument is 3%/12, the NPER (number of payments)
argument is 18, and the PV or present value argument is $2,500.
Plan payments and savings
Quick Reference Card 9
See how much your savings will add up to over time
Starting with $500 in your account, how much will you have in 10
months if you deposit $200 a month at 1.5% interest?
=FV(1.5%/12,10,-200,-500) In 10 months you would have $2,517.57 in
savings.
• The rate argument is 1.5%/12.
• The NPER argument is 10 (months)
• The PMT argument is -200
• The PV (present value) argument is -500
Plan payments and savings
Quick Reference Card 10
The online version of this Quick Reference Card provides
more information. To see that version, go to
http://office.microsoft.com/en-us/excel-help/quickreference-cardRZ102630204.aspx?section=10&mode=print
Plan payments and savings