Reverse Compound Interest Calculator in Excel (Download for Free)

Get FREE Advanced Excel Exercises with Solutions!

Compound interest can be easily calculated using the initial amount, interest rate, and the compounding period. Suppose, you have the initial amount, compounding years, and the final amount. Now how can you calculate the compound interest rate? This is where you need to reverse back the whole procedure. With that said, in this article, you will learn to create and use a reverse compound interest calculator in Excel with easy steps.


What is Compound Interest?

Compound Interest is a type of interest that is calculated from the initial amount of money and the collective interest from the past compounding periods.

The compound interest is often treated as interest on interest. This compound interest thrives faster than simple interest. Because the simple interest is calculated only from the initial amount of money.


Compound Interest Rate Formula

The generic formula for calculating the compound interest is,

Compound Interest = Final Amount - Initial Amount

If,

P = Initial Amount (Principal)

i = Annual Interest Rate in Percentage

n = Period in Years

Then the compound interest formula becomes,

Compound Interest = P [(1 + i) ^ n – 1]

Reverse Compound Interest Rate Formula

When you have,

IA = Initial Amount

FA = Final Amount

n = Period in Years

Then you can use the following formula to calculate the compound interest rate in reverse,

Compound Interest Rate = [(FA/IA) ^ 1/n] -1

Create a Reverse Compound Interest Rate Calculator

1. Use the POWER Function to Create a Reverse Compound Interest Rate Calculator

Now we know the formula to calculate the compound interest rate in reverse is,

Compound Interest Rate = [(FA/IA) ^ 1/n] -1

Where,

IA = Initial Amount

FA = Final Amount

n = Period in Years

To create a calculator that calculates the compound interest rate in reverse using the POWER function,

❶ Allocate cells to input the Initial Amount, Final Amount, Period in Years, etc.

I have chosen cells D4, D5, and D6 respectively.

❷ Now select a cell where you want to return the compound interest rate value and insert the following formula:

=(POWER((D5/D4),1/D6))-1

❸ After that press the ENTER button.

That’s it.

Use the POWER Function to Create a Reverse Compound Interest Rate Calculator

Read More: How to Create Quarterly Compound Interest Calculator in Excel


2. Use the RATE Function to Create a Reverse Compound Interest Rate Calculator

To create a calculator to get the compound interest rate in reverse using the RATE function, go through the following steps:

❶ Choose cells to insert the value of Initial Amount, Final Amount, and Period in Years.

For this instance, I have picked cells D4, D5, and D6.

❷ Then insert the following formula in cell D8,

=RATE(D6,0,-D4,D5)

In the formula above,

D4 contains the Initial Amount.

D5 contains the Final Amount.

D6 contains the Period in Years.

❸ Finally press the ENTER button to insert the above formula.

Use the RATE Function to Create a Reverse Compound Interest Rate Calculator

Read More: A Daily Compound Interest Calculator in Excel (Template Attached)


Application of the Reverse Compound Interest Rate Calculator

Suppose, you’ve taken a loan of $5,000,000 from XYZ bank. After 5 years, you need to pay $8,550,000 in return. From this information, if you want to calculate the compound interest rate in reserve, then follow the steps below:

In the problem above,

Initial Amount = $5,000,000

Final Amount = $8,550,000

The period in Years = 5

❶ Now insert the Initial Amount, $5,000,000 in cell D4.

❷ Then in cell D5 enter the Final Amount which is $8,550,000.

❸ Finally, insert the Period in Years in cell D6 which is 5.

After entering the above data, you will see the compound interest rate calculator has instantly calculated the compound interest rate in reverse. In the D8, you will see that the calculated compound interest rate is 11%.

Read More: Create a Daily Loan Interest Calculator in Excel


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


Conclusion

To sum up, we have discussed the procedure to create and use a reverse compound interest calculator in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.


Related Articles


<< Go Back to Interest Calculator | Finance Template | Excel 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.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo