The article shows how to make a two wheeler loan EMI Calculator in Excel. In today’s business world, EMI (Equated Monthly Installment) has become a vital part of purchasing products. Customers are now interested in buying products with EMI because it can relieve them from the burden of the instant cost of money. Suppose you need to buy an expensive product but you don’t have that amount of money to buy it or if you spend all the money on that, you are going to suffer for a month or more. In order to save you from this burden, banks provide you the EMI service where you can purchase an expensive product by paying a monthly amount to the seller for a certain period. Excel has its own function to calculate the EMI amount. We will provide you with this simple calculator at the end of this article.
Download Practice Workbook
2 Ways to Make Two Wheeler Loan EMI Calculator in Excel
Suppose you bought a product which costs 10000 dollars. You will return the money after 12 months with EMI at an interest of 5%. We depicted this scenario in the dataset.
You may wonder if we’ve already calculated the EMI without showing you the process, don’t worry, end of the article you will be able to make a sheet like this.
1. Using Excel Formula to Make Two Wheeler Loan EMI Calculator
We can use the Excel PMT function to calculate the EMI amount. I provided you with a detailed dataset so that you can see not only the EMI amount but also other parameters in the dataset. Let’s go through the following part of this section.
The formula here uses the PMT function, which automatically calculates the EMI amount by taking the interest rate, time, and principal parameters sequentially. I divided the interest rate by 12 because we will be paying the EMI for 12 months, if you’re paying interest for any other number of months divide by that number. I also used the ABS function to keep the value positive.
Make sure that you’ve filled the Remaining column for this row before using the formula, otherwise, you will not get a value.
- After that, press the ENTER button and you will see the EMI amount for the purchase.
- Next, type the following formula to calculate the Interest amount for the first installment.
- Once we’ve found these values, we can calculate the Current Principal value of the loan and for that, the following formula will be used.
Now, let’s proceed to calculate EMI for the next month. But before that, we need to calculate the Remaining Principal after the previous payment. You have to do this every month as the EMI amount depends on the remaining value.
- Write down the following formula to calculate the Remaining Principal.
- Now, drag the formula from the previous cell to get the EMI amount for this month.
- Similarly, you can use the Fill Handle to AutoFill all the blank cells in the dataset. You will see the Interest, Current Principal, and Remaining Principal for each month. This helps you to to understand the summary of the EMI
The main data here is the EMI amount which is always constant over the 12 months, which means you have to pay the salesman or sales organization 856.07 dollars each month.
Thus you can make a two wheeler loan EMI Calculator in Excel.
Note: The month number will begin from 0 and end one less than the number of months. You can see the interest amount is getting lower as the month progresses.
Read More: Create Home Loan EMI Calculator in Excel Sheet with Prepayment Option
2. Applying Arithmetic Formula to Make Two Wheeler Loan EMI Calculator
If you know the EMI formula and don’t want to use the built-in Excel function, you can replicate the formula in Excel too. The formula for EMI Calculation is given below.
P = Principal
r = Interest Rate
n = Number of Months for Installment
We will be using the same steps in Section 1. We will only change the EMI formula. Let’s go through the following description in order to understand the process.
- Using the above concept, type the following formula instead of using the PMT function that we used in Section 1.
The rest of the calculations and steps are completely the same as in the previous section.
Thus you can make a two wheeler loan EMI Calculator in Excel by using an arithmetic formula of EMI.
Read More: Reducing Balance EMI Calculator in Excel Sheet (Create with Easy Steps)
Here, I’ve prepared a simple and effective EMI Calculator so that you can calculate any EMI amount for any principal and interest rate. Provide your value to the respective fields and you’ll get the EMI.
By the end of the article, we can conclude that you will learn how to make a two wheeler loan EMI Calculator in Excel by using both Excel and arithmetic formulas. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.