Reverse Compound Interest Calculator in Excel (Download for Free)

The final amount including compound interest is easily calculated using the initial amount, interest rate, and the compounding period. In this article, we’ll consider the reverse situation, when we have the initial amount, compounding years, and the final amount, and want to know the interest rate. We’ll create and use a reverse compound interest calculator to accomplish this.

What is Compound Interest?

Compound Interest is interest calculated on an initial amount plus any previously accrued interest. The amounts including interest grow exponentially over time compared to simple interest, where an interest rate is only applied to the initial amount.

Compound Interest Rate Formula

The generic formula for calculating the value of the compound interest in a Final Amount 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 we have,

IA = Initial Amount

FA = Final Amount

n = Period in Years

Then we 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

Method 1 – Using the POWER Function

Steps:

Create a dataset like the image below.
In cells D4, D5, and D6, enter the Initial Amount, Final Amount and Period in Years.
To return the compound interest rate, insert the following formula in cell D8:

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

Press ENTER .

The compound interest rate is returned.

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

Method 2 – Using the RATE Function

Steps:

Using the same dataset as previously, in cells D4, D5, and D6 enter the Initial Amount, Final Amount and Period in Years.
To return the compound interest rate, insert the following formula in cell D8:

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

Press ENTER to return the result.

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

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, let’s calculate the compound interest rate in reverse.

Initial Amount = $5,000,000

Final Amount = $8,550,000

Period in Years = 5