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

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.


Download Practice Workbook

Download the following workbook to practice by yourself.


2 Easy Methods to Calculate EMI for Bike Loan in Excel

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: SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option


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.

Read More: Reducing Balance EMI Calculator in Excel Sheet (Create with Easy Steps)


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. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo