How to Calculate Personal Loan EMI in Excel?

Apply for Personal Loan

How to Calculate Personal Loan EMI in Excel?

Personal loans are now some of the most popular types of loans being availed by people. These are short or medium term unsecured loans that people take out to meet their financial needs. Most commonly they are used to meet expenses of debt consolidation, vacations, unexpected medical expenses, amongst others. Personal loans are offered without keeping any asset as collateral, however, your eligibility will depend on your credit history, income, age, amongst other factors. There are no real restrictions on what you can use the money you get from a personal loan for. Usually, borrowers get a flexible tenure of up to 5 years* to repay the loan amount. This is why personal loans are preferred by customers as compared to the credit cards or other loans.

KC PL Content Banner

You can avail a personal loan for up to Rs. 25 Lakhs* from with an instant online application. Personal loans have a flexible repayment tenure of up to 60 months*. Borrowers can avail attractive interest rates, and have a hassle-free online documentation process for quick loan disbursal. If you are planning to take out a personal loan, the first step is to calculate how much EMI (easy monthly instalment) you will be paying, as this will greatly affect your repayment capacity.

Calculate EMI Using Excel

One of the easiest ways to calculate the EMI on your loan is by using Microsoft Excel. Excel provides a simple formula for this purpose: PMT (rate, nper, pv, [fv], [type]).

PMT stands for Payment – and gives periodic loan payment or EMI value as an output. The arguments required for this function are:

  • rate - The interest rate for the loan.
  • nper - The total number of payments for the loan.
  • pv - The present value, or the total value of all loan payments now, i.e. the outstanding loan amount.
  • fv - [optional] The future value, or a cash balance you want after the last payment is made. Defaults to 0, i.e. the outstanding loan amount after all the payments are made.
  • type - [optional] When payments are due. 0 = end of the period. 1 = beginning of the period. Default is 0. 

When using this function, make sure that the units for rate and nper are the same. For example, if you are looking to calculate monthly EMI, the rate should be the monthly interest rate and not the annual interest rate. Let us look at an example to understand this:

Consider a loan of ₹10 lakh at 10% interest per annum for 20 years. If it is repaid in quarterly instalments:

Rate = 10%/4 per quarter

Npr = 20X4

EMI calculation in excel will be:

= PMT(10%/4, 20*4, 10,00,000)

Calculate EMI Using Mathematical Formula 

If you do not have access to MS Excel, worry not! You can still calculate the EMI by using a calculator or doing some math yourself. The standard formula for calculating the EMI due is:

EMI = [P x R x (1+R)^N]/[(1+R)^N-1] 

Where P is the loan amount or principal, R is the interest rate per period, and N is the number of periodic installments.

Just like in the PMT formula, you must ensure that the units for rate and EMI period are the same. For example, if you are calculating monthly EMI and the interest rate charged is 10% per annum, you should use [10/(12X100)] as the R in the formula. EMI calculation formula will give you the same result as the PMT function, as the function uses the same calculation in the background. 

It is important to remember that these calculations are based on a flat-rate being charged for the duration of the loan. Further, any other fees that may need to be paid along with the Principal repayment and Interest payment are not included in the results.

Calculate EMI Using Online Calculator

Most banks and financial institutions use a mathematical formula to calculate personal loan EMI. This will depend on your personal loan amount, tenure of the loan and the interest rate. The process of calculating and recalculating the EMI that would suit your repayment capacity can be quite tedious and time-consuming. Here is when you can use an online personal loan EMI calculator to assist you in the process. You can use the EMI calculator to reach an amount which you can easily pay back without causing a strain on your finances.

3 Facts You Should Know About Loan EMI Calculation

1. Your EMI payment includes the principal amount and interest due on the amount

An important factor to understand is that when you are paying your EMI you are repaying a part of the main personal loan amount due, also known as the principal, and also paying the interest due on the loan amount. Personal loan EMIs are the monthly repayment you make to repay the loan over the tenure of the loan. Usually, when you start making EMI payments, you contribute more to interest as compared to the principal. However, as you keep getting closer to the end tenure of the loan, your EMIs contribute more to the principal amount than interest payments

2. Ensure the EMI amount is not more than 40% of your monthly income

As a rule of thumb, market experts advise that your EMI amount for any loan, including a personal loan should not be more than 40% of your monthly income. This is because most people have a relatively fixed income every month and have other expenses and financial obligations to account for. Additionally, there is always unpredictability about income if you are a self-employed businessman and unpredictability about losing your job or getting a salary cut if you are a salaried individual. Hence, being on the safer side and ensuring that your EMI payments don’t exceed 40% of your monthly income is the financially prudent decision to take.

3. Your interest rate will vary on a range of factors

One of the key factors that influence the amount of EMI you pay every month is the rate of interest on your personal loan. The interest rate that you will be charged depends on a range of factors including your income, credit history, amount being borrowed, age amongst several others. Your credit history is one of the most important factors influencing the amount. Your credit history is mainly influenced by your repayment of past or current loans and repayment of your credit card bills. Typically, lenders and banking institutions will offer more lucrative personal loan interest rates to customers they perceive as being low credit risk - that is those who have repaid their previous loans and bills on time.

Must Read: How to Apply for a Personal Loan Online

Hence, when you are calculating your personal loan EMI ensure you are strategic and take into consideration the impact that this will have on your monthly and overall financial budget. Your repayment capacity is greatly influenced by your other fixed monthly expenses and you should take those into consideration before making your decision.

*Terms and Conditions apply. Loans are disbursed at the discretion of Fullerton India.