Greetings everyone. In this video session we'll learn about a formula which is going to be extremely beneficial for personal financial planning. And we are talking about PMT formula which is often used to find the calculation for EMI EMI for any loan that you would have taken will combine this learning another technique of data tables, which is where we see that at various levels of loan and duration duration means the amount of years required to repay the loan back and we'll see at those different levels while the different EMI is going to be. So here we go. The different variables that are required for any loan calculation is loan amount, interest that you wish to pay. In fact, you're forced to pay and a duration the number of years in which you want to stretch the entire EMI is two.

So the first blank cell that lies before us we start with a PMT formula PMT. I click on this FX button and the couple of variables that comes with Before us starts with rate now I'm going to choose the 13 person cell and divided by 12. Now why do we do that? Because it's a monthly payment. So the easy monthly installment the EMI to make it consistent with the formula and calculation I divide the rate the rate per annum divided by 12 number of periods, which means how many installments of payment will go out. So I'm going to choose the year two and multiply that with 12.

Ensure that when you're dividing by 12, the annual interest rate you must also multiply the number of periods with 12. So present value the amount of money that you have received today the present value and I'm choosing the five lakh it is positive because the money comes to your bank account. Now there are couple of other options which for the moment we can leave out because those are optional. Although By default, the type suggests 00 means you are going to pay the amount towards the end of the month. Although we can see the sample answer, but I will press OK to find the answer in the cell. So, basically for a five lakh loan, half a million dollars or half a million rupees are 13% interest per annum and duration being two years you are supposed to be an EMI off grade 80,007 $71 or rupees per month.

Now, you notice if I change the interest to 5%, the EMI decreases. And if I increase the duration, let's say five years, it also decreases further, which is logical with the entire calculation. In fact, using the definition and technique of data tables, we are going to extrapolate all these calculation and see that if we take a loan of higher amount and prolong the repayment period, what are the different emails that I will have to pay? And in fact, if you go to any bank for loan and they give you a statement, they give you a calculation you can verify that those calculations with your own calculation. So, let's see what are the steps required for completing this data table. In fact, for users who are watching this video for the first time and have not come across our videos on data table, I recommend that you must see those first.

So I first have populated the values vertically and horizontally keeping this particular cell blank. These are all formula which means if you change the first value to let's say six lakhs, it will change the subsequent value and Same goes for duration. So I press Ctrl Z it and bring back those initial values. Now, in this particular cell, which has been kept blank, I put a formula equal to and straightaway point to the cell containing the formula of BMT, that's how connection is established and the information disseminated to the table values. So step two done step one was populating the value step two, establishing the connection. Step three is choosing the entire table.

Since they are all connected, I can also press the shortcut key Ctrl A. At this point, it will choose the entire table. Now comes the final part. I go to Data tab within which I go to what if analysis and click on Data Table. Now, we see HR that was the mnemonic which we had discussed earlier in one of the videos, let's see what this is going to hold in this particular session. So, V is vertically placed data that is lunar mount must be paired up with three or column input cell one cell and that cannot be range, it cannot be a value from the table, it has to be a value from the main model, I call it as a neutral venue.

Similarly, by that logic, HR horizontally placed information is the years in which you wish to pay the loan back. So, in row input cell h are horizontally paired up with row our input cell. So I choose the cell which contains the duration or the years as I press OK, there you go. This are the different levels of EMI that you will have to pay for different amounts of loan and different duration If you just want to test this out, in fact, what I'm gonna do is I'm going to put five years here, notice the EMI that you are paying here is the same as the value here, which verifies the accuracy of the entire model. So, this is PMT formula for EMI calculation as well as data table to see the what if analysis