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.
- After that, use AutoFill to complete the rest.
- As expected, the EMI values are the same.
- 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.
- 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.
- 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.
- Consequently, choose cell F9. Type the formula:
=F8-D8- Press Enter to get the remaining.
- Then, use AutoFill to fill the series.
- The values will be the same at first.
- But it’ll return precise results later on.
- Similarly, apply AutoFill to get the Interests.
- For now, the values are equal for all.
- It’ll get modified in the next step.
- 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.
- 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.
- 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.
