A personal loan, when availed by a borrower, needs to be repaid through EMIs or Equated Monthly Instalments. The monthly instalments go on till the entire principal amount is paid off. Alongside principal, the other component in the instalments comprises the interest accruing on the loan.
Lenders now provide various online tools on their websites to assist customers in availing as well as repaying loans via EMIs so calculated. One such tool is the EMI calculator through which a borrower can check the monthly estimates of repayment. Alongside MS Excel can also be used to get the EMI estimates. With the help of an Excel EMI calculator, you can gain a fair idea of your monthly outgo on loan repayment even before availing one.
Variables included in EMI calculation via Excel formula
Calculating personal loan EMIs using Microsoft Excel is one of the simplest ways to do it. The first and foremost point to keep in mind here is that the PMT formula is to be used in Excel instead of EMI. PMT stands for payment. The formula used in Excel EMI calculator gives the estimates of periodic loan repayment or EMIs as output.
Here are the variables required in this formula to calculate EMI estimates:
- Rate of Interest
Every lender charges a certain amount of interest on the funds, such as a personal loan, lent to a borrower. The EMI amount also varies as per this interest rate.
- NPR or Number of Periods
NPR in Excel EMI calculator is the total number of instalments or payments included in a loan repayment tenure. The number will be higher in case of an extended tenure and vice versa.
- PV or Present Value
Present Value refers to the gross value of loan repayment done till a point of time. It can also be referred to as the outstanding loan amount.
- FV or Future Value
Future Value is the cash balance you wish to keep after all the payments are made. You can also consider the value as zero if you want to omit this variable.
- Type
Type in Excel EMI Calculator denotes the number of payments due. If this number is 0, it indicates payments due at the end of a repayment period. In case the payment is due at the start of a month, the type will be number 1.
You need to make sure that the units of NPER and the rate of interest are the same. It means the rate of interest used to calculate the EMIs is the monthly rate and not the annual interest rate.
Let’s say, a personal loan of Rs.20 lakh has been borrowed at 10% interest rate per annum for 30 years. In this case, if the loan is to be paid quarterly, the rate will be 10%/4 per quarter and NPER will be 30×4.
Thus, PMT variables in Excel EMI calculator will be 10%/4, 30×4, and 20,00,000.
As a responsible borrower, you must know the use of EMI calculator to assess your repayment liability. It can be of significant help when availing unsecured advances like applying for a personal loan. It would help you understand as well as plan your loan repayment better.
How to calculate personal loan EMIs using the formula?
The standard formula to calculate personal loan EMIs in Excel is:
EMI = P*r*(1+r) ^n/((1+r) ^n – 1)
Here, P stands for the gross loan amount or the principal sum. R is the rate of interest charged by the lender. N is the total number of equated monthly instalments in a loan.
This formula is also used in the personal loan EMI Calculator to compute the required estimates. One of the most important things to know before signing a personal loan agreement is the EMI estimates you will have to bear once the repayment tenure commences.
Availing personal loans is now even more convenient with a range of pre-approved offers. These offers are designed to simplify availing funds for borrowers and are available on several financial products, such as business loans and credit cards alongside personal loans. Provide only your name and contact details to check your pre-approved offer now and stay a step closer to quick funds.
As a borrower, you also need to be mindful about the fact that personal loan EMIs will determine your overall financial burden and need to plan your personal loan EMI repayment better. You need to assess your repayment capacity on the basis of monthly instalments of a loan. You can do so either through Excel EMI calculator or online via the loan EMI calculator.
