How to Calculate EMI for Bike Loan in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

MS Excel is a powerful software. Here, we can perform many different mathematical operations. It provides numerous built-in functions which are highly useful. In our life, we buy various things in installments. It’s very beneficial when we are unable to pay the whole fee at once. But, we’ve to be careful about the Loan Interest. So, it’ll be advantageous if we can calculate the remaining loan amounts and interests by ourselves. In this article, we’ll show you the easy and effective methods to Calculate EMI for Bike Loan in Excel.


How to Calculate EMI for Bike Loan in Excel: 2 Easy Methods

EMI stands for Equated Monthly Installment. That means, we have to pay a fixed amount monthly. This amount includes the interest too. Suppose, you take out a Loan to buy a Bike. And you’ll pay the loan in EMI. This article will show the simple ways to determine the EMI for the Bike Loan in Excel. To illustrate, we’ll use a sample dataset as an example. For instance, in the following dataset, we have a Principal ($10,000). The fixed Interest rate is 12% per annum. The tenure is 12 Months. Here, we’ll find out the EMI, Remaining Principal, etc.


1. Calculate EMI for Bike Loan with Simple Formula in Excel

There’s a general formula to compute the EMI. The formula is:

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

Here,

P = Loan amount or Principal

R = Interest rate per month

N = Number of monthly installments

In our first method, we’ll create the above-described formula to Calculate the EMI for the Bike Loan in Excel. We’ll divide the given Interest (12%) by 12. This is because we have to convert the annual interest rate to the per month rate. Therefore, follow the steps below to perform the task.

STEPS:

  • First, select cell C8.
  • Then, type the formula:
=($B$5*$D$5/12)*((1+$D$5/12)^$F$5)/((1+$D$5/12)^$F$5-1)
  • Subsequently, press Enter to return the EMI value.

Calculate EMI for Bike Loan with Simple Formula in Excel

  • After that, use AutoFill to complete the rest.
  • As expected, the EMI values are the same.

Calculate EMI for Bike Loan with Simple Formula in Excel

  • Now, we’ll find the remaining principal amounts.
  • In cell F8, type the formula:
=B5
  • At the start of the tenure, the remaining amount is the same as the mail principal amount.

Calculate EMI for Bike Loan with Simple Formula in Excel

  • Next, we’ll determine the interest in each installment.
  • Select cell E8 and type the formula:
=$D$5/12*F8
  • Press Enter to return the result.

Calculate EMI for Bike Loan with Simple Formula in Excel

  • Afterward, we’ll find the actual Principal amount that is paid.
  • For that purpose, in cell D8, type the below formula:
=C8-E8
  • Press Enter.

Calculate EMI for Bike Loan with Simple Formula in Excel

  • Consequently, choose cell F9. Type the formula:
=F8-D8
  • Press Enter to get the remaining.

Calculate EMI for Bike Loan with Simple Formula in Excel

  • Then, use AutoFill to fill the series.
  • The values will be the same at first.
  • But it’ll return precise results later on.

Calculate EMI for Bike Loan with Simple Formula in Excel

  • Similarly, apply AutoFill to get the Interests.
  • For now, the values are equal for all.
  • It’ll get modified in the next step.

Calculate EMI for Bike Loan with Simple Formula in Excel

  • Accordingly, return the rest of the principal amounts by using AutoFill.
  • Immediately, you’ll notice that the Interest column and the Remaining column get modified automatically.

Calculate EMI for Bike Loan with Simple Formula in Excel

  • However, in the last row, the remaining principal is 0.
  • Therefore, the EMI is completed in 11 months.
  • Thus, you can calculate the Bike Loan EMI by yourself.

Read More: How to Make EMI Formula in Excel


2. Insert Excel PMT Function to Calculate Bike Loan EMI

Moreover, the Excel PMT function can reduce our load of creating the formula. This function returns the fixed payment amount for a fixed interest rate. Hence, learn the following process to figure out the Bike Loan EMI in Excel.

STEPS:

  • Firstly, select cell C8.
  • Then, type the below formula:
=ABS(PMT($D$5/12,$F$5,$B$5))
  • Next, press Enter.
  • Thus, it’ll return the EMI output.

NOTE: The ABS function returns the value only. It doesn’t consider the signs.
  • After that, use the AutoFill tool to return the rest of the series.
  • Get the Principal, Interest, and Remaining outputs by following the same steps described in the previous method.

  • Subsequently, in cell F9, type the formula:
=F8-D8
  • Get the result by pressing Enter.
  • Consequently, use AutoFill.

  • Lastly, return the other values in the Interest and Remaining columns by applying AutoFill.
  • In this way, you can determine the EMI amount.


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Henceforth, you will be able to Calculate EMI for Bike Loan in Excel following the above-described methods. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


<< Go Back to Calculate EMI in Excel | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo