—
This useful tutorial from http://www.KnowledgeCity.com shows you how to calculate payments and interest rates when making a large purchase such as a home or car. Know how much you can afford before you shop.
—
—
Transcript Provided by YouTube:
00:00
let’s take advantage of another
00:03
sophisticated building formula something
00:06
very near and dear to most people’s
00:08
hearts is to finance a home or finance
00:11
the purchase of a car and the issue is
00:14
what are your options and what is it
00:16
going to cost so I’m going to begin in
00:19
the a one cell by typing my interest
00:23
rate per year which I’ll expand to be
00:29
that wide and I’m going to say that my
00:32
interest rate is 8% per year on this
00:35
particular loan my principal will be
00:43
$100,000 then I’m going to borrow and
00:46
I’ll create this and format it in
00:50
accounting formatting the term of the
00:55
loan is going to be for 30 years what I
01:02
now like to find out is what is my
01:09
monthly payment the monthly payment is
01:14
calculated with an elaborate formula
01:17
that is built-in
01:18
fortunately in Excel and is called PMT
01:22
for payment you will notice in the
01:26
tooltip that it tells you that it needs
01:28
the rate the number of periods present
01:32
value an optional future value and the
01:36
type of calculation that’s going to be
01:38
done which is whether the payment is
01:40
going to be at the beginning of the
01:41
month or the beginning of the period or
01:43
at the end of the period if you know
01:47
this information you could begin typing
01:49
it in directly if you’re not sure of
01:51
what exactly to type you can always then
01:54
click the function icon on the formula
01:58
bar and it will open up a dialog box
02:01
that will allow you to insert the
02:04
formula and do your own creating and
02:07
prompting one by one so what I’m going
02:10
to do is type equals PMT
02:15
and I want to open up the function
02:19
arguments dialog box and fill in the
02:22
information you will notice that the
02:25
rate first edit item is the interest
02:29
rate per period for the loan and as a
02:32
reminder they indicate that you would
02:35
use 6% divided by four for quarterly
02:38
payments we’re going to make annual
02:41
payments and the interest rate is 8%
02:44
we’re going to make monthly payments I’m
02:47
sorry so it’s going to be the 8% which
02:49
is in the b1 cell divided by 12 to show
02:53
us the interest rate per month based on
02:57
an 8% annual interest rate the number of
03:03
periods will be 12 months times the 30
03:08
years and the present value of our loan
03:14
is $100,000 we’re going to liquidate the
03:20
loan so there’s going to be no future
03:22
value it’s going to go to zero and the
03:24
type is whether or not we’re going to be
03:27
paying at the beginning of the period or
03:29
at the end of the period or omitted it’s
03:33
at the end of the period and you will
03:36
see that it has stepped us through the
03:38
various calculations and telling us that
03:41
the payment is going to be negative is
03:44
out of our pocket and the period of 30
03:47
years
03:48
12 payments per year will cost us seven
03:52
hundred and thirty three dollars and
03:53
seventy six cents per month
—
Previously published on YouTube.
—
Photo credit: Screenshot from video