EMI Calculator with Prepayment Option in Excel Sheet (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

When you’re dealing with your personal finance, you have to have clear knowledge about it. Not knowing general financial concepts can lead you to financial disasters. There are two types of financial terms to pay the loan. The first one is Flat Rate Interest and another is Reducing the Balance Rate. In both cases, you need to calculate the EMI. We can easily calculate the EMI with the prepayment option in Excel. This will save you a lot of time and energy. Today, in this article, we’ll learn five quick and suitable steps how to EMI calculator Excel sheet with prepayment option effectively with appropriate illustrations.


How to Calculate EMI with Prepayment Option in Excel Sheet: Easy Steps

Let’s assume we have an Excel large worksheet that contains the information about the EMI calculation with the prepayment option. We will calculate the EMI with the prepayment option from our dataset using Excel’s PMT, and IPMT financial formulas. PMT stands for Payment, and IPMT is used to get the interest of payment. We will apply these financial functions to calculate the EMI calculator Excel sheet with prepayment option. We will do those five easy and quick steps, which are also time-saving. Here’s an overview of the dataset for today’s task.

emi calculator excel sheet with prepayment option

Let’s follow the instructions below to learn!


Step 1: Use PMT Function to Calculate EMI

First of all, we will calculate the EMI by using the PMT financial function. One can pay one’s payment every week, month, or year by using this function. The syntax of the function is,

=PMT(rate, nper, pv, [fv],[type])

Where the rate is the interest rate of the loan, the nper is the total number of payments per loan, the pv is the present value i.e. the total value of all the loan payments at present, [fv] is future value i.e. the cash balance one wants to have after the last payment is done, and [type] specifies when the payment is due.

Use PMT Function to Calculate EMI

Let’s follow the instructions below to calculate the EMI using the PMT function!

  • First, select cell C11 and write down the PMT function in that cell. The PMT function is,
=PMT(E$4/E$6,E$5*E$6,E$7)
  • Where E$4 is the Annual Interest Rate, E$6 is the number of payments per year, E$5 is the number of years, and E$7 is the original price of the car. We use the dollar ($) sign for the absolute reference of a cell.

  • Hence, simply press ENTER on your keyboard. As a result, you will get the payment ($12,178.38) as the output of the PMT function.

Use PMT Function to Calculate EMI

  • Now, AutoFill the PMT function to the rest of the cells in column C.
  • After completing the above process, you will be able to calculate the EMI of the loan per month which has been given in the below screenshot.

Use PMT Function to Calculate EMI


Step 2: Apply IPMT Function to Calculate Interest

After calculating the EMI, now, we will calculate the interest of payment by applying the IPMT function. The syntax of the function is,

=IPMT(rate, per, nper, pv, [fv],[type])

Where the rate is the interest rate per period, per is a specific period; must be between 1 and nper, nper is the total number of payment periods in a year, the pv is the current value of a loan or investment, [fv] is the nper payments future worth, [type] is the payments behavior.

emi calculator excel sheet with prepayment option

Let’s follow the instructions below to calculate the interest of payment by using the IPMT function!

  • First of all, select cell D11 and type the IPMT function in the Formula Bar. The IPMT function in the Formula Bar is,
=IPMT(E$4/E$6,B11,E$5*E$6,E$7)
  • Where E$4 is the Annual Interest Rate, E$6 is the number of payments per year, B11 is the number of months, E$5 is the number of years, and E$7 is the original price of the car. We use the dollar ($) sign for the absolute reference of a cell.

  • Further, simply press ENTER on your keyboard. As a result, you will get the interest of payment ($933.33) as the output of the IPMT function.

emi calculator excel sheet with prepayment option

  • Hence, AutoFill the IPMT function to the rest of the cells in column D.
  • After completing the above process, you will be able to calculate the interest of payment of the EMI calculation with prepayment per month which has been given in the below screenshot.

Read More: Create Home Loan EMI Calculator in Excel Sheet with Prepayment Option


Step 3: Use Subtraction Formula to Calculate Principal

In this portion, we will use the Mathematical subtraction formula to calculate the principal of the loan. This is an easy and time-saving task also. From our dataset, we can easily calculate the principal of the loan by subtracting the Interest from EMI. Follow the instructions below to calculate the balance using the mathematical function!

  • First of all, select cell E11 to apply the mathematical subtraction formula, and write down the following formula in the formula bar. The formula is,
=C11-D11
  • Where C11 is the EMI of the loan, and D11 is the interest of the first month.

emi calculator excel sheet with prepayment option

  • After that, press ENTER on your keyboard, and you will get the principal of the first month. The principal for the first month is ($11,245.05).

  • While performing the above process, you will be able to calculate the principal per month which has been given in the below screenshot.

emi calculator excel sheet with prepayment option


Step 4: Apply Mathematical Formula to Calculate Due Balance

In this step, we will apply the Mathematical formula to calculate the due balance of the loan. This is an easy and time-saving task also. From our dataset, we can easily calculate the due balance using the mathematical summation formula. Follow the instructions below to calculate the balance using the mathematical function!

  • First of all, select cell F11 to apply the mathematical summation formula. Hence, write down the following formula in the Formula Bar. The formula is,
=F10+E11
  • Where F10 is the price of the car after giving the prepayment, and E11 is the total payment after the first month.

  • After that, press ENTER on your keyboard, and you will get the balance after the first month. The balance becomes $128,754.95 after the first month.

emi calculator excel sheet with prepayment option

  • While performing the above process, you will be able to calculate the EMI with a prepayment option per month. After the 12th month, you will be able to pay the total loan which has been given in the below screenshot.

emi calculator excel sheet with prepayment option


Step 5: Calculation of Paid Loan in Percentage

This is the final step to calculating the paid loan for each month in percentage in Excel. After calculating the EMI per month, the interest of payment per month, the principal of payment per month, and the paid balance per month. Now, we will calculate the paid loan in percentage by using those values. Follow the instructions below to calculate the paid loan in percentage using the mathematical function!

  • First of all, select cell G11 to apply the mathematical summation formula. After that, write down the following formula in the Formula Bar. The formula is,
=(F$10-F11)/F$10

  • Hence, press ENTER on your keyboard. As a result, you will get the percentage of your paid balance after the first month. The percentage of your paid balance after the first month is 8.03%.

emi calculator excel sheet with prepayment option

  • While performing the above process, you will be able to calculate the percentage of your paid balance for each month. After the 12th month, you will be able to pay the 100% loan which has been given in the below screenshot.

emi calculator excel sheet with prepayment option

Read More: SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option


Things to Remember

👉 Each month, the loan debt is decreased as a result of your payments. The accumulated interest must decrease as the loan balance decreases.

👉 #DIV/0 error occurs when the denominator is 0 or the reference of the cell is not valid.

👉 The #NUM! error occurs when the per argument is less than 0 or is greater than the nper argument value.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope all of the suitable steps mentioned above to calculate the EMI calculator Excel sheet with prepayment option will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles


<< Go Back to EMI CalculatorFinance TemplateExcel Templates

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.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo