How to Create Reverse EMI Calculator in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, with some simple techniques, we’ll learn how to create a reverse EMI calculator in Excel. The EMI refers to the Equated Monthly Installment. Excel is an efficient way to calculate reverse EMI for determining total loans, mortgages, or any other expenses. Here is an overview of the methods and processes we have used in this article.

Overview of Reverse EMI Calculator in Excel


Download Practice Workbook

Download the following workbook that we used to write this article so that you can practice along with it while reading the article.


What Is Reverse EMI?

Reverse EMI is the meaningful opposite of EMI. Here we have a property that is fully owned. For older people, sometimes need the money against it but as a monthly payment. So here comes the reverse EMI concept. The more money they get from the bank or the payer, the more they lose their portion of the property.

In a normal EMI, a loan is taken out all at once and paid monthly to overcome it. In reverse EMI, the loan is given on a monthly basis, making it seem like the bank or the payer is paying the loan here. That’s why it’s called reverse EMI.

Read More: How to Calculate EMI in Excel (2 Effective Ways)


2 Easy Methods to Create Reverse EMI Calculator in Excel

In this article, we will show 2 different methods to create a reverse EMI calculator in Excel with appropriate examples. Both methods are explained with proper steps below.

1. Use of Goal Seek Feature to Create Reverse EMI Calculator in Excel

Goal Seek feature in Excel is one of the most efficient ways to find out something based on a defined relationship. If we can establish a relationship between the time period, total loan, monthly installment, and interest rate, we can therefore create a reverse EMI calculator in Excel.

🔶 Step 1: Establishing the Relationship

In this method, the most crucial part is to establish a relationship between the EMI and Total Loan based on the Interest Rate and Time. Generally, we use the PMT function to do that. The syntax is –

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

PMT function syntax

For the reverse EMI calculation, we will have the total time (nper) and interest rate (rate). We have the output EMI and we will find out Present value or total loan amount (pv). Now, follow these procedures:

  • First, we have all these data like the image below.

Parameters or data that we need to find out EMI

  • Now, we will write the following formula in the desired cell:
=PMT(D7,D6,C5)

Using the PMT function in Excel to find out EMI

  • Then pressing Enter will give us the following result.

Output using the PMT function

  • Finally, the cells are connected based on the PMT function as in the image below.

Relation between parameters and EMI

🔶 Step 2: Setting up the Parameters

We will now set up the parameters, like the npr, rate according to our needs. Then we will use the goal-seek feature. For example, we will use a time period of 6 years and 10% as a rate.

Making Data modification for using goal seek feature to create reverse EMI calculator in Excel

🔶 Step 3: Using the Goal Seek Feature for Reverse EMI Calculation

At last, we are ready to use the Goal Seek feature. To do so, follow these procedures:

  • First, go to the Data tab in the Ribbon and select What If Analysis under the Forecast Select Goal Seek in the drop-down menu. A small dialog box will appear.

Finding Goal Seek feature from the Ribbon

  • Second, in that box, we will set the EMI to see how much value or net worth we will be getting. So, in the Set cell field, we will select the EMI amount and give the value in the box below. And for the By changing cell option, select the cell containing the total Loan Amount and press Enter.

Applying Goal seek feature to create reverse EMI calculator in Excel

Here, we have set the EMI to 3000$ to see the total amount or present value we will be getting from the bank.

Read More: How to Calculate Interest Rate from EMI in Excel (with Easy Steps)


2. Using PV Function to Create Reverse EMI Calculator in Excel

We can also use the simple PV function or the present value function to create a reverse EMI calculator in Excel. The steps are below.

🔶 Steps:

  • First, we will have all these data like the image below. Here we need to find the Loan Amount or the amount we will get.

parameters or data for using PV function

  • Then we will select the cell for output and write the following formula in the formula bar.
=PV(C6/12,C5*12,C4)

using PV function to create reverse EMI calculator in Excel

  • Next pressing Enter will give us the expected result.

Loan amount using PV function to create Reverse EMI calculator in Excel

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


Things to Remember

  • We must remember that the Goal Seek feature requires an existing relationship.
  • The PV function is a simple statistical function for finding out the present value of any loan.
  • Using PV function is more effective while the calculation is static. When we can change multiple parameters and need reverse calculation of any of the parameters, Goal Seek is a better option.

Conclusion

This is how we can create a reverse EMI calculator in Excel. Go through the article and practice it carefully. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions. For any Excel-related problems, you can visit our website, Exceldemy for solutions.


Related Articles

Nasir Muhammad Munim
Nasir Muhammad Munim

I am Nasir Muhammad Munim from Dhaka, Bangladesh. I work as an Excel and VBA Content Developer for Exceldemy right now. Electrical and electronic engineering was my major at Islamic University of Technology, where I got my degree. I love creative works and learning new things. And I'm really into online games, especially first-person shooter games.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo