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. ### 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. ## 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%. ## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects. Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  