Greetings everyone. In this video session we'll talk about two components of Excel. One is PMT formula, which is often used to find the easy monthly installments for any loan that you have taken in the past or wish to take in the future. And go see how do you back calculate certain desired numbers. So first, let me look at the various variables which are going to go into the ingredients of this recipe. The first thing I have learned amount, interest percentage and the duration that means, in two years flat, I'm going to pay these loan back fully.
And this one variable which is going to be a part of the formula which says if you want to pay towards the end of the month, then the input should be zero equals one. Now, let me write the PMT formula equals PMT. I go to the FX button Insert Function click on it. Now notice I am writing equal PMT and then opening the brackets and then going to FX button if you had not opened the brackets or parenthesis If you then go to FX, it will not lead you directly to the PMT formula. So equal to PMT pressing TAB key clicking on FX button now rate choosing the cell cutting the percentage annual interest percentage divided by 12 thereafter number of periods which indicates the duration and this to make it consistent with the formula inputs I'm going to multiply with 12 you are paying monthly installments present value I am getting a loan worth of five lakhs today's term and that's the present value.
There are two other optional conditions, which if I won't I can ignore for the time being. So I press OK. Oh, I get an amount of credit 3007 71 that is the monthly check amount that you need to deposit towards the bank loan repayment. Now, let's for a moment assume that you have another loan in the backdrop and you only have 20,000 per month as the school residual income, which you can use this to service the debt. Now, how do I know that? How do I increase the duration to let's say 2.5 years or 2.6 years or three years? What is the exact number of years at which point the exact Am I going to be 23 when he tells him so let's find out, I keep my cursor on the cell, the cell which contains the amount of EMI, I then go to data.
What if analysis and let me click on Goal Seek, I click on Goal Seek. So, I see set this cell, the cell containing the calculation for the PMT, to what value now notice it doesn't say cell which means you cannot choose another cell. It will give you an error. It has to be a value which needs to be hard coded. So I wish to write minus 20,000. Why so because this is an outflow, this is a cash outflow which is going out of pocket, hence my It's pretty tough to be careful about that.
Set the cell because one containing PMT formula to a value of negative 20,000 by changing cell by changing. Let's see amount. Let's see what comes up next. As I press OK. It says it has back calculated Goal Seek has found the solution. Do you want to keep the solution I say okay.
So basically, if you have 20,000 disposable income for the loan repayment and you wish to pay this back in two and a half years, then this is the amount of loan that you can avail against these money variables. On the other hand, let's say I want to take only five lakh worth of loan, I press Ctrl Z to bring it back. Now I want to see exactly in how many years can I repay the loan back in if 20,000 is the amount I can pay towards the loan repayment. So I go back to go seek again. I say set this cell to negative 20,000. If you write a positive 20,000 it will go in in an indefinite loop, which you will not want.
So, you go ahead by saying by changing cell and this time we take the cell containing the duration, okay? it back calculates, and it tells you exactly 2.44 years is when you'll be able to repay the loan back if you are paying getting the installment of 20,000 each every month. So that was go seek that was PMT formula, often used for that calculation.