How to Calculate Personal Loan EMI in Excel?

A Simple Guide to Calculating Personal Loan EMIs Using Excel

Personal loans are one of the most popular types of loans as they do not require any collateral and can be used for meeting a variety of expenses. Based on your eligibility and creditworthiness, personal loans can give you substantial funds for tackling everything from medical emergencies and debt consolidation to travel and wedding expenses. When planning for a loan, Equated Monthly Instalments (EMIs) are one of the most crucial considerations as they greatly impact your repayment capacity. In this article, we will understand the basics of EMI and how to calculate EMI in Excel easily.

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.

What Is EMI?

EMI is the fixed amount of money that a borrower must pay to a lender on a specified date each month until the loan is paid off in full. The EMI covers 2 major components - the principal which is the actual amount borrowed and the interest which is the cost charged by the lender for lending the money. 

Unless the interest rate changes, the amount you have to pay each month will usually stay the same over the loan term. Knowing how much amount needs to be allocated for the loan allows for easier budgeting and makes loan repayment manageable.

EMIs commonly come into the picture for different types of loans such as personal loans, car loans, and home loans. They allow you to spread out the costs of large expenses such as homes or education over a longer period, making such purchases more accessible.

Formula for EMI Calculation & How to Calculate EMI Using Tools and in Excel

EMI calculation requires a simple formula, which is given below:

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

Where, 

P = The principal loan amount

R = The monthly interest rate (annual interest rate divided by 12)

N = The loan tenure in months

If you do not wish to calculate the EMI manually, you have 2 options - using tools such as an online EMI calculator or using an EMI calculator Excel sheet. Many lenders or financial institutions offer free EMI calculators where you can simply input the 3 components of EMI calculation and get the EMI estimate instantly. 

EMI calculation in Excel is also quick, easy, and gives accurate results as long you enter the correct formula. EMI calculator Excel sheets are popular among borrowers as they can easily create reusable templates to compare different loan options. Let us see how to calculate EMI in Excel in more detail.

How Do I Calculate Loan EMI in Excel

Personal loan EMI calculation in Excel is straightforward and involves using the PMT function. PMT stands for Payment – and gives periodic loan payments or EMI value as an output. Let us learn more about the function and how the EMI calculator in Excel works through the following steps:

1. Open Excel and Set Up Your Spreadsheet

Open a new Excel sheet and input the parameters required for calculating EMI in separate cells. For instance:

• A1: Enter the loan amount (e.g., 1,00,000)
• B1: Enter the annual interest rate (e.g., 12)
• C1: Enter the loan tenure in months(e.g., 36)

2. Calculate the Monthly Interest Rate

In cell D1, calculate the monthly interest rate by dividing the annual interest rate by 12:

= B1/12/100

Thus, the monthly rate for a 12% annual interest rate will be 0.01.

3. Use the PMT Function

The EMI calculation formula in Excel is: PMT (rate, nper, pv, [fv], [type])

Where

rate - The monthly interest rate for the loan.
nper - The total number of payments for the loan (the loan tenure in months).
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.

In cell E1, enter the function as follows:

= PMT(D1, C1, - A1)

Press enter to calculate the EMI. With our example, the result with be approximately INR 3,321. 

Please note that for accurate results for an EMI calculator in Excel, the units for the interest rate and number of payments match, i.e., the monthly rate should be used against the number of months).

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.

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.

Conclusion

Planning to take out a personal loan? One of your first considerations should be the monthly EMI amount, as it will greatly affect your repayment capacity. 

Calculating EMI in advance will help you plan your monthly budget and determine whether you can comfortably afford the loan amount you are planning to apply for. To do so, you can either use an EMI calculator Excel sheet or use SMFG India Credit’s free-to-use online EMI calculator. We offer personal loans of up to INR 30 Lakhs* with attractive interest rates and flexible repayment tenures of up to 60 months*. Apply with a hassle-free online application process and get one step closer to realising your financial goals!