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

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.

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.

How to Create Reverse EMI Calculator in Excel: 2 Easy Methods

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)

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.

Now, we will write the following formula in the desired cell:

=PMT(D7,D6,C5)

Then pressing Enter will give us the following result.

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

🔶 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.

🔶 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.

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.

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

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

We can also use the 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.

Then we will select the cell for output and write the following formula in the formula bar.

=PV(C6/12,C5*12,C4)

Next pressing Enter will give us the expected result.

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.

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.

Reverse EMI Calculator.xlsx

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.

<< Go Back to EMI Calculator | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!