Transcripts
1. Introduction to EMI & Loan : Hello friends, welcome
to this class. In this class, you
will learn how to calculate EMI in excel sheet. How and how to reach a desired
variable amount of EMI. Do any loans, let's
say personal loans, student loans, home loans, vehicle loan, and so on. Let me go to an example which is one of the projects of
this Skillshare class. Here, in this scenario, you are taking a home loan of 400,012% interest
rate for ten years. And after calculating the EMI, it comes to $5,738
in some sense. Now, the condition is
that you don't want to pay an EMI of more than
$5 thousand a month. Now, how would you find
a desired amount of loan if it cannot make changes to the interest
rates and the tenure. If you watch this
class on Skillshare, you will be able to perform
all such calculations. I'm looking forward to see you. Thank you.
2. EMI Calculation in Excel: Dear friends,
Welcome to my class. In this class we will
learn what is EMI. Emi stands for equated
monthly installment. It's a monthly payment. Mostly it is a monthly payments and sometimes it can
be a yearly payment, but it's a monthly
payment provided by the borrower to the creditors. The amount include
principal repayment plus the interest payment. So for Lehman, an EMI
is just an expense. The amount is just an expense. But for accountants
and those who know, it has two components in it. One is the principal
repayment and the access to surplus is
the interest repayments. Now in this example, we will, we are taking a postman loan of $1000 with five-year-old
installments and the rate of interest is 10%. Now how to calculate EMI? In order to calculate
the BMI in Excel, we have a dedicated
function called PMT. Let me just walk you
through the syntax of the function that I've
written it for you. This detailed information I
have written is mentioned in your project project
Excel file as well. You have that as well
for your reference. Now be PMT has read NPER PV FV and type rate is the rate of interest that we are
taking loan for. Nper is the total
number of installments. Now here PER is the
current installment, NPER is the total
number of payments, total number of installments that we are making the payment. And PV is the present value of the loan value
that we are taking. The loan for. Fe stands for the future
value and type is actually 0 or 10
is mostly the end. I mean, 0 is the end
of the period and one is the beginning of the period when we
are making payments. Some EMI's appeared on the first starting off demands and some EMI's appeared at
the end of demand. So the proportion of
interests will default. When, once we write nothing, then it is assume it is 0. Let me come back to my ear, my calculation type PMT as the function for the rate and select ten person
because that's the rate. Nper is the number of
installments so that I have five. And I'll write
this present value in minus because
that's an outflow. Here I'm getting my EMI, that is the yearly EMA is
263 $7 and some change. Now as we just discussed, EMI has interests as
well as principal. Now, what is the interests and what is the proportion
of interests in? What is the proportion
of principle in there? So for that we have
different functions. For that we have IP empty. Now, I is actually
for the principal, sorry for the interests
here and I'll start I it is asking me for
the rate all-time. The street no, PER
is installments. So first this is the
first payment for STEMI, so I'll write one. Then NPR is the total
number of payments. So we have that fives. I'll select the five and I'll write the same minus
because that's an outflow. So it's saying that 10000 $0 is the interest
amount in this EMI, That should be the principal. So we have one more
dedicated function for principle that is TPMT. Now P for principal and
interests in PMT function. We have to add i when we want to know the
interests and we have to add p before BNP when we want to know
the principal amount. Now I'll start with this PMT. It's asking me for the
rate that is ten person. This is the first installment. Total number is five, and this will go in minus. Now if I add this both, It's exactly the same. 637. So that's how we can
come to know that our EMA has this much
interest in principle. Now I want to know what
is the total payment. Now, EMF remains constant. You may, does not change. So what I can do, I have to make this payment of EMI five times. So skinny, I simply
do like this. This multiplied by 513,189 in some change that this
much amount of dollar, I'm supposed to be
the total payment. What is the total
interests and picking? So how do we come to know
the total payment is this and the amount we are taking the loan for
his 10 thousand, I can simply deduct this
minus the loan amount. Access more time making
payment of is the interests. Now you will see that. So normally the EMA
is paid monthly rate. So in our project, we are calculating your LEA, but I'll teach you how monthly EMA can also be calculated. It is really very simple. We divide the interest rate in two months and the
installment also in humans. Now five years is
actually 60 minutes. So here it will calculate EMI for monthly e-mail, that is PMT. I'll select the red, I'll divide it by 12. I'll select demands this time. And this minus loan amount. This is 212, $212 and some change is
the monthly EMI am getting in this scenario. If I multiplied this with 12, when I get the same answer as the yearly EMI
know, let me check. No, not of my students asked me, sir, why this is less I mean, this should be seeing
when we're calculating, calculating for
one year and we're calculating for one month
and multiplying by 12. Why this is less? A lot of students
have this confusion. Let me delete from
my experience, this ear mine that we
have calculated on a monthly basis and
multiply this EMI is less because we
have started making payments from the very first
month of picking the loan. Hence, after your means, after 12 monthly payments, the loan amount due is less. The interest on the
outstanding loan is also less. Hence we get a reduced EMI. I hope you understand why we
are getting introduced EMI. Now, let me walk you through what is the
project that you're supposed to do after learning the skills that we just
learned in this class. I'll give you some hint. This is going to
be your project. I have the files in my gallery that you
can find it there. You have to calculate
all this information. And here you are to calculate principal and interest
amount for every year. Here we just learned that
we are making payments usually for all the
five years you are to calculate principle as
well as interest amount. I know you're going to use VBM, DNI, PMT, but the catches, but the project
is about that you don't have to type the
functions ten times. You have to type it
only once for TPMT, for the principal and
for the interests. And you have to drag this and all the answer
should appear correctly. Now how will it happen
that you have to lock the sum of the cells and you have to
unlock some of the cells. That is called locking
system that we call a relative absolute reference and mixed reference in Excel. If you know how to lock it, you can skip my next video and directly go to the project. And if you don't know,
so I've explained in much detail what is
locking system in? Excellent, how you will get it. So once you do this, the principle will be the increasing farming
interests will be on the decreasing form once
you get the answer. So I request you to take
a screenshot and share it in the project gallery
with all the other students. I'll be much happier to
talk to you that thank you.
3. Relative, Absolute & Mixed Reference in Excel: Hello my friends.
Welcome to this class. In this class, we will clear the concepts of what is
really given reference, absolute reference, and
mixed reference in Excel. How does it work and how can
it make our work easier? Now let me tell you from
experience that axle is by default a
relative reference. Let me prove it to you here. Suppose if I type some wound digital age,
let's say again, and if I drag it, so it is
all the way, then everywhere, if I take 1015 and if I select this boot
cells for dragging, it will come up here in the range and the
difference of five. If our type, let's say
Monday and drag this, It's going to fetch me to 0. And so XL is by default
or relative reference. Now, how to do this here in
our example, four months, I'll take gen and I'll
drag this for revenues. I'll take 100000.1500 and I'll take incremental
of $500 for expenses, I'll take five hundred
and ten hundred dollars and I have to select
this board and drag it. Now for net income, I have to deduct expenses from the revenue of each
particular month. Now I don't have good IF
functions or formulas in each particular columns for
the number of months we have, what I can simply
do, the revenue minus expense and press
enter on the cell D3. I've just typed b3 minus C3. That is revenue minus
expenses. I'll press Enter. I'm getting $500,
which is correct. Now, all the differences
is find a dollar only that we know,
and I'll drag it. This is absolutely correct. Now here we know that only
cell D3 on this cell, we have just type b3
minus c3 it on before. We never taken before minus x4. On D5, we never Table B5 minus S5 in Excel intelligently
took the relative reference. So you don't understood
that on this cell, if this is the case
on the next cell or subsequential, it will come up. You're like, this is how
a relative reference, but let me tell you what
is absolute reference. Here is a fantastic example
to learn that I have some names or initials of some students names and the monks that
they've forgot out, out of a 100, they've secured 7526426068 out of a 100, all
different students. Now, what is the percentage
they're getting? Now of course, 75
is out of a 175%, is that we know Sixty-five, Sixty-five out of
a 165 percentage. But how do we calculate with the help of absolute reference? So first of all, I'll
select all the range and make it a
percentage from it. We have just made it in
the percentage format. The information
now will appear in the percentage format
only. How does it work? Let me tell you this is the general format and
this is the percentage for me because I have changed the format from
you for this end. Now for type anything
here, Let's attend. So it will be only ten, but if I type anything here, it will be appearing in
the percentage for me. So I can change the
formulas from here. Now here I have made the format so I don't have to
multiply by a 100. I can simply do my work. Here. I want the percentage, so I will divide 75, that is Thirty-six, divided by the out-of-town
work that is B35. And I'll press
Enter. I'm getting perfectly 75
percentage as demand. Correct answer.
But if I drag it, I'm not going to get the answer. Because again, Excel is by default are really do
reference as I just explained. So it's taking the
relative reference of the cell above it
and not of a 100. So we need to fix a 100. How do we fix a 100? So I'll click this
B35, that is 100. And I'll press F4. That is dollar sign. So it's going to lock this. Now, if you're using laptop, if f or doesn't work, it is function f,
the function button. You have to keep it on and off so that you can manually type the dollar sign before
B and 35 board, that is rows and column board. B35 locked it and I'll press Enter NLL,
read ragged normal. Now it is giving me
all the correct answer because B35 is fixed, that is called
absolute reference. So this is how we can lock
a particular cell and then drag in order to
save a lot of our time, I'm sure that you are learning the skills how to do
this in Excel with an M, M sure that you're also enjoying this very interesting examples. There is one more
topical mixed reference. I have a little project
for that as well. Let me show you this here. I have some numbers
here in each column, 1020304050 and the
72nd row, it is 12345. What actually I want is I want the time to be
multiplied by one, then 2345, again for
this Duan De 30. And here also, I want the same, wanted to be multiplying
by 1020304050 and so on. But I don't have that much time to type different functions
and formulas here. What I want, I want to type
something only once here, and then I'll drag it
vertically as well as audio. So that is also possible with the help of mixed reference. I'll show you how Select
I'll put the function as, I'll put the formulas
is equals to, that is one that is 72. What are, do not
lock B7, T2, both. If I look be 72, then both will be fixed. What I want, I won't
be to go to C then go to D because all the other columns are
different videos. I'm not log b, I log only 72. So I'll put it like this. I have logged only 72 and I
want to multiply it by 73. Now, I will not
lock a mean sorry, I'm not locked 73 but because I wanted to
go to seventy four, seventy five,
seventy six. Right. So I'll not lock 73, so I lock only. This is mixed reference. Every cell has two things in it. One is the row and
one is the column. So a, B, C, these other
columns in this 1234 rows. So you can lock both. That is absolute reference and Luca LOG either that
is mixed reference. If you lock none, then that is relative reference
that we learned the initial press Enter, what are dragging it vertically, I'm getting
correct answers. I'll drag your horizontally,
I'm getting correct answers. I want one more project I
have for this, this project. When you open the project file, you will get this to practice the skills
that you've learned. There are some different
hypothetical banks with different interests
providing for the interest rates, compassion. And this is the number of loans that you are
going to take. And you have to type
this and drag it vertically and horizontally and z are getting correct
answer for all. Do this as well. And I'm sure that you
will now be able to calculate the interest and principal amounts for
the five years of EMI with the help of
this locking system, requested to do both projects and uploaded in the
project gallery. I'll be so much pleased to
talk to you over there. Thank you so much, my friend.
4. Whatif Analysis in Excel: Dear friends, welcome
to my final class. This class will see what
is What-If Analysis. And I'm sure that after
learning the skills, you will be in a
position to complete the final project for
this particular classes. Now, what varies? What if in the under the data
tab we have this forecast. That is what if analysis. For our project, we are going
to learn what is Goalseek? For Goal Seek. I have taken this
amazing example that this is one of the groups, or group one for an
examination where student has to score 14
in subject and total 200. That is an aggregate
to clear the group. Now this is actually a scenario where a student has
been two exams. He has been to three exams
and written the max himself. Right. So after every exam when we become
examiner for a while, he estimated that he
is going to get fifty, six forty one, forty one. Now suppose the scores 40 here. So the total is not
going to be 200. So total is going to
be less than 200. So he will feel group
one field the exam. Now, this is the
scenario where he's standing here and
whatever he could do, he could do only in
the history subject. Here we will use
What-If Analysis, naughty bit Celsius,
It's a forecast. What if it's a condition? And it has various usage. It can be used for the
salespeople who are looking for the quarterly
targets and they are unable to achieve in
first couple of months. So, you know what, how much
target they should achieve in the last man in order to get
the quarter Target achieved. And scenarios like that. Now here we have
type of function. Some will keep the cursor here on our formula
and function. And we'll go here to Goal Seek. Now, this cell,
the selected cell, It's actually some, we want to value 200 now by
changing which cell. So we can only
change the history because these exams
have gone, right. So here I'll press Okay. This what-if we'll do some permutation
combination and fetch me that he has to score 62 in
order to get 200, right. So that is how you
can do like that. Let me do it once more until you it works with different
functions as well. Let me take average. Now, total 204 marks
wherever it should be 50. I'll put my cursor here on
the average. I'll go here. What if Goal Seek the
average asset cell to value 50 by
changing the cell. So again, it will get
me the same answer is 62. Exactly the same. I'm sure that now
you have understood what his goal see that we are we have a particular goal
in a couple of criteria. And we're going to see
that I'm sure that now you are in a position to
do our final project. I'll give you a hint
for the final project. Now, final project will include all your skills that
you have learned so far. Now, this is an example of a final project that the loan
amount that you're taking, It's like a home loan
or $400 thousand with the 12% yearly interest
rate for 120 months, that is for ten years. But if you now, how will you find
the installment? Of course, we will
use your skills that you learned and you
will find what is the EMI, that is the early EMI for
this particular scenario or monthly EMI's because this
information is in months. So you will get a hint
that you have defined in a monthly scenario now. But I'll tell you that
in this calculation, what you will do
with your functions, you will get monthly EMI
for more than $5 thousand. And this is the only
project that you can't be more than $5 thousand
EMI per month. Now in this scenario, find the maximum of home loan that you can
afford or you can take. In this scenario, how
will you use your Goal, Seek your financial
functions for EMI that we learned and how
you're going to get me. I'll be so happy to see in the project gallery that
you are able to tell me that how much is the loan amount that a person or
you should borrow in order to pay a monthly EMI of not more than $5 thousand. I'm so happy that
you have learned and gone through my
classes so far here. I'm sure that you
have enjoyed and I look forward to you in my
future coming classes. Thank you so much. God bless you all.
Aamir Shaikh, Freelancer, Youtuber, Trainer
