馃攳
EMI Calculation - Excel Formula & Expert EMI Calculator [Hindi] - YouTube
Channel: Asset Yogi
[0]
Hello, my name is Mukul
[2]
And welcome to the Asset Yogi
[4]
We unlock the knowledge of finance here
[7]
Whenever you go to take a loan or you are planning to take a loan
[12]
Like a home loan, car loan, personal loan, education loan
[16]
Or some other loan
[18]
Or you may want to purchase something on EMI
[22]
Then you will want to make a budget, how much your EMI will come for how much loan amount
[28]
Then for this calculation, you don't have to go to the bank
[31]
You can do it by yourself
[33]
So in this video, I will teach you how to calculate EMI for loans of any type
[37]
In Microsoft Excel by using the PMT function
[42]
Now this function has some limitations too
[45]
I will tell you that also
[46]
And with that, I have made a detailed calculator
[49]
Which I am going to share with you at the end
[51]
In that, all the options are available, the prepayment option is also available
[54]
How much is your total interest payable?
[56]
It will tell you that
[58]
Please watch this video till the end
[61]
Let's go to my computer screen straight
[64]
Firstly we will see that how to calculate the EMI in Microsoft Excel
[68]
By using the PMT function
[70]
For that, you'll need 3 input values
[73]
I have marked it with yellow color here
[75]
The first thing you should know is the loan amount
[77]
How much amount of a loan you want to take?
[79]
Assume you want to take a loan of 5 lacs rs
[81]
You will add the value of 5 lac here
[83]
So I have added a value of 5 lac Rs
[87]
Then you have to add interest rate here
[89]
How much is your annual interest rate?
[91]
What interest rate are you getting from the market?
[93]
So assume you are taking a loan on 10 % interest rate
[97]
After that, you have to put a time period
[99]
Assume you are taking that loan for 5 years
[102]
Then we will enter 5 here
[104]
After that, to calculate EMI, you have to use the PMT function
[108]
So you will type "=PMT("
[112]
And you will open the bracket then you will get all the values
[114]
that you have to enter
[115]
Firstly you are seeing that you have to fill in the value of the rate
[118]
So this is the interest rate
[120]
Be careful, you have to put the monthly rate here
[124]
Select the interest rate cell here
[128]
And divide it by 12
[132]
So you will get a monthly interest rate
[134]
Then you have to put a comma and you have to put the number of periods
[137]
What is the number of periods?
[139]
Again you have to put that in months
[141]
Firstly we will select the cell and multiply it by 12
[146]
Then you have to put a comma and after that, you have to put the present value
[151]
The present value is your loan amount
[153]
So in this case, you select this cell, and here is your present value
[158]
After this, you have to put a comma and add the future value
[162]
The future value in our case will be zero because the loan will be finished after 5 years
[167]
So if we don't put anything there then there is no problem
[170]
Because it will take automatically zero
[172]
We can close the bracket here
[174]
Then if we press enter, we will get our EMI value, 10624
[179]
Now you can see that the value is coming in the bracket
[183]
So this bracket means that the value is negative
[186]
And the value is negative because this payment is outflow
[190]
This money will be spent, that's why it is a negative value
[193]
There will be an outflow of cash that's why it is a negative value
[196]
So in this way, you can calculate the EMI of any loan
[200]
And for yourself, if you think that it will work for you in less loan also
[205]
Then you can change the value of the loan here
[207]
Assume that you have put the value of 4 lac
[210]
So you can see that in this case what will be your EMI
[213]
So in this case your EMI will be 8500, then your EMI is less
[218]
If you think that you can pay a little more EMI
[221]
Then you can decrease the time
[224]
Assume that you have taken the time like 3 years
[227]
In this case, your EMI will be 12,907 Rs
[231]
Now you can decide the loan amount and period but
[235]
You cannot play around with interest rate
[238]
Because this is the market-driven rate
[240]
So there is not much scope for negotiations
[242]
Whatever the lowest interest rate you find, you can go with that
[246]
So you can see your EMI is 12907
[250]
But now there are two problems with this case
[254]
First is, here you don't get a month-wise schedule
[256]
Month wise or year wise
[258]
That how much you have paid in 1 month or how much principal you've paid
[262]
Or how much you've paid annually, principal or interest
[267]
The second problem is that if you want to do prepayment
[272]
Suppose you have 1 lac Rs after 18 months and you want to repay the loan
[278]
So whatever will be the calculation, in that case, it cannot be calculated in this
[282]
So for that case, I've made a quite detailed and comprehensive calculator
[286]
All the options are available in that
[288]
I will show you the calculator and you can download it also
[292]
It is free to use
[293]
You can download it and use it
[297]
So I will take you to the calculator
[300]
So this is the calculator in which you can see the three input values
[306]
The ones which are marked with yellow color
[309]
So I have used the same values here
[311]
Assume you have taken the loan for 5 lacs at a 10% interest rate for 5years
[315]
So you can see here that the EMI is 10624
[320]
So assume that we will make this value 4 lac or we will make this 3 year
[329]
So see it has become 12907
[332]
So see, in a way, the calculator has been tested
[334]
So you can see in this case
[337]
It will tell you the total interest payable
[340]
So you will have to pay the interest of 64647 in 3 years
[345]
So you got the total interest payable
[348]
What will you pay in total
[350]
After that, you will get this graph
[352]
So you can see that the interest payment is more in the initial years
[356]
Gradually it decreases
[358]
So this is the chart and when the period is long
[362]
When the tenure is long
[364]
Like in the home loans, it starts to matter more
[367]
And the interest payment increase in that case
[370]
I will show you an example
[373]
Assume you are taking a loan of 40 lacs instead of 4 lacs
[377]
Assume you are taking the home loan
[379]
So I will enter the value of 40 lacs here, and it is 9 % instead of 10%
[385]
Because home loans generally have less rate as compared to other loans
[388]
If we assume the loan tenure is 20 years
[393]
So what will be the values ?
[395]
So you can see here, that you will get an EMI of approx 36000
[400]
And you can also see that the total interest payable is how much it became
[404]
It has become 46,37,000
[406]
So you will pay more interest than your principal in 20 years
[412]
So you can see how much interest you are paying in the initial years
[417]
So see, we can see that by graph
[420]
That you are paying interest
[422]
Gradually the interest amount is decreasing
[424]
And the ones which are marked as blue
[426]
This is the principal amount and it is increasing
[428]
So here interest payment in initial years is quite high
[432]
And gradually the interest payment starts decreasing
[435]
And in this calculator, you'll also get year wise schedule and a month-wise schedule
[441]
So you can say that this is 20 years schedule
[443]
This calculator is made for a total of 30 years
[447]
So you can see how much you'll pay for interest and principal for 20 years
[453]
How much will be the principal outstanding?
[455]
How much you have given the cumulative interest for any year?
[459]
How much you have given cumulative principal?
[462]
And here the prepayments are reflected if you do any prepayments
[465]
You have to add these prepayments month-wise
[470]
If the month-wise schedule is given here
[473]
Then you will get a monthly principal payment
[475]
Interest payment and you will find all the payments
[478]
Assume if you want to prepay after 36 months
[482]
After 3 years, you think you are getting a payment of 2 lac Rs
[487]
So you'll put the value of 2 lac
[490]
So now we will see what will be calculation
[495]
So we will go back
[497]
The loan which was ending in 20 years, now we can see
[500]
It will end in approx 18 years
[503]
You can see by this chart
[505]
So here in 3rd year, you have made a payment of 2 lac
[509]
You can see that the principal payment has increased suddenly here
[512]
You can also see that the interest rate has decreased so much
[516]
Now you are seeing in this case
[518]
That it has become 39,52,940 Rs
[521]
So approx you are saving more than 6 lacs
[524]
So you can see that if you pay about 2 lac in 3 years
[528]
So you will see that you'll save 6 lac Rs
[531]
So you can play around here, whatever options that you want to enter
[536]
So you can put all the options in that calculator
[539]
So this is quite a comprehensive and accurate calculator
[543]
Please download it and I will put the link in the description
[546]
If you see any problem with this then tell me
[549]
I will correct you for sure
[551]
And how you liked the calculator please comment below!
[555]
I hope you liked the video
[558]
Then like the video and share the video
[560]
If you want to suggest a topic for future videos
[563]
Then comment it below
[565]
To watch the latest finance videos
[568]
Subscribe to the channel and press the bell icon
[571]
Let's meet in the next video
[573]
Till then keep learning, keep earning
[575]
And be happy
Most Recent Videos:
You can go back to the homepage right here: Homepage