0:00
hello and welcome to tutorial's point in
0:02
this video you will learn how to
0:04
calculate monthly installment payments
0:06
for a loan here we have taken the
0:08
example of a home loan let's consider
0:11
that you are taking a home loan of
0:14
$350,000 with an annual interest rate of
0:17
7% and you're supposed to pay this back
0:20
in 30 years now before we calculate the
0:22
monthly installment amount here we have
0:25
the number of years in which we have to
0:26
repay our loan that is 30 but we have to
0:29
pay loan amount in monthly installments
0:31
right so we need to calculate number of
0:33
months in these 30 years since 1 year
0:36
equals 12 months so here number of
0:39
months will be equal to this 30
0:43
multiplied by 12 hit enter and we have
0:47
the number of months so we can say that
0:49
this is the number of months in which we
0:51
have to repay our loan or we can simply
0:53
name it number of payments that we have
0:55
to make right let me adjust it
1:00
now to calculate the monthly payment
1:03
amount we will use excel's builtin
1:05
function called BMT function so I will
1:08
WR here monthly payment
1:10
amount and let me adjust this a
1:13
bit so we will use the formula equals
1:18
PMT open parenthesis now here we have to
1:22
write rate which is the annual interest
1:24
rate which is 7% but here we have to
1:26
write the monthly interest rate so what
1:29
we'll do is will select this annual
1:31
interest rate and divide it by 12 to get
1:34
our monthly interest rate next we have
1:37
to write the NP which means the number
1:39
of payments which we just calculated 360
1:42
so instead of writing 360 we can just
1:45
simply select this cell with the value
1:47
of 360 comma then we have to write PV
1:50
which is the principal value in our case
1:51
it is the loan amount of
1:53
$350,000 so I'll select this Sale close
1:56
the parenthesis and hit enter and we
2:00
just found out the monthly payment
2:03
amount now as you can see here this is
2:05
in red color and in baren thesis because
2:08
Excel treats this money as outgoing to
2:11
get rid of this red color and
2:13
parenthesis simply just before the
2:15
formula we can use the negative sign and
2:18
hit enter and the red color and
2:20
parentheses are gone next we would like
2:24
to calculate the total amount we are
2:25
paying to the bank so I'll write here
2:30
and uh this will be equal
2:32
to the monthly payment multiplied by the
2:38
payments and hit enter and this is the
2:43
total cost so basically for a loan
2:48
$350,000 we are paying back to the bank
2:57
2814 we can also calculate the toal
2:59
total interest we are paying to the bank
3:01
here so I'll write here total interest
3:04
equals this total cost minus the loan
3:08
amount we are taking hit enter and this
3:11
is the total interest we are paying to
3:13
the bank that's it for now I hope this
3:16
video has provided you with a clear
3:19
understanding of how to calculate
3:20
monthly loan payments if you have any
3:22
questions feel free to comment down
3:24
below make sure to like this video share
3:26
it with your friends and I will see you
