Use Excel What If Analysis for Personal Finance and Calculate Home Loan EMI in Excel

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 use to find the calculation for EMI EMI for any loan that you would have taken well combine this learning with 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 what are 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 we force 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 you start with a PMD formula PMT I click on this FX button and the couple of variables that comes before all starts with rate now I'm going to choose the 13% cell and divide 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 period which means how many installments of payment will go out so I'm going to choose the year 2 and multiply that with 12 then sure that when you are 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've received today the present value and I'm choosing the 5 lakh it is positive because the money comes through your bank account now there couple of other options which for the moment we can leave out because those are optional although by default the type suggest 0 0 means you are going to pay the amount towards end of the month all we can see the sample answer but I will press ok to find the answer in the cell so basically for a 5 lakh loan half a million dollars or half a million rupees 13% interest per annum and duration being two years you are supposed to pay an EMI of twenty three thousand seven seventy one dollars or rupees per month now you notice if I chain the interest to five percent 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 EMI is 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 one of 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 6 lakhs it will change the subsequent value and same goes for duration so I press ctrl-z and bring back those initial values now in this particular cell which has been kept blank I put a formula equal to and straight away point to the cell containing the formula of be empty that's how connection is established and the information disseminated to the table values so step 2 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 loan amount must be paired up with three or column input cell one cell and that not be arranged 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 H are 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 Earth's 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 going to 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 I hope you enjoyed our YouTube videos and I really really appreciate that if you could subscribe to our videos which will also encourage me to give more such videos in the times to come and you can also watch some of our popular programs which have been liked by lot of viewers see you soon

test attribution text

Add Comment