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 AmountIf,
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] -1Create 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] -1Where,
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.
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.
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.
