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.

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.

Factors that affect the EMI amount

The Equated Monthly Installment, or EMI, is a set sum of money that a borrower pays each month to repay a debt. The amount of EMI is determined by several factors, including:

1.    Loan amount: The greater the loan amount, the more the EMI.
2.    Interest rate: A higher interest rate leads to a higher monthly installment.
3.    Loan term: A longer loan tenure lowers EMI, whilst a shorter loan tenure raises it.
4.    Down payment: A bigger down payment reduces the loan amount and, as a result, the EMI.
5.    Credit score: A higher credit score might lead to a cheaper interest rate and, as a result, a lower EMI.
6.    Income: A higher income can lead to a larger loan amount and, consequently, a higher EMI.

Why should you calculate your EMI?

Determining your EMI before taking out a loan is critical since it allows you to organise your finances and make informed decisions. Here are some of the benefits of calculating your EMI:

1. Budgeting: Understanding your EMI ahead of time allows you to plan your monthly budget and expenses accordingly.
2. Affordability: Calculating your EMI allows you to decide whether you can afford the loan you intend to take out.
3. Loan comparison: Calculating EMI allows you to evaluate several lenders' offers and select the one that best fits your budget and repayment capacity.
4. Avoiding default: Understanding your EMI helps you prevent loan repayment default, which can result in penalties, a worse credit score, and legal troubles.
5. Early repayment: Calculating EMI helps you determine if you can make early repayments and reduce the interest cost of your loan. Calculate your EMIs to understand this better.

Which function in Excel is used to calculate EMI?

To calculate EMI, many people turn to formulae and online calculators. The simplest way to calculate how much monthly payment you owe to your lender is to do it through Excel sheets. You can use the PMT function or the monthly payment for a loan.

To make it even easier, you may simply visit the online website of an EMI calculator that takes in parameters like your income, interest rate, tenure, and line of work. If you talk about SMFG India Credit’s calculator, you can drag and drop the option given and find out the dynamic results of the EMI obligation.

How do I calculate loan EMI in Excel?

Calculating loan EMI in Excel is a straightforward process, and it involves using the PMT function. Here are the steps to follow:

1. Open an Excel spreadsheet and enter the loan amount in one cell.
2. Enter the loan tenure in another cell, keeping the time period consistent (months or years).
3. Enter the annual interest rate in another cell.
4. In a new cell, type "=PMT(interest rate/12, loan tenure*12, -loan amount)" without quotes. The PMT function calculates the EMI.
5. Press Enter, and the cell will show the EMI amount.

This will be even more straightforward with an online calculator to do the calculations for you.

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.