Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. It is possible in Excel to calculate both compound interest rates and simple interest rates. Moreover, you can also convert them from one to another. In this article, I will show how to convert compound interest to simple interest in Excel.
Introduction to Compound Interest and Simple Interest
Compound Interest Rate: Compound interest (or compounding interest) is the interest amount calculated based on both the initial principal and the accumulated interest from previous periods. The formula to calculate compound interest is
I=P(1+r)^n-P
Where,
P = Initial Principal
r = Compound interest rate
n = Time period (years)
On the other hand, the simple interest rate is calculated based on the initial principal only. The formula is
I = P*n*R
Where,
P = Initial Principal
n = Time Period (years)
R = Simple Interest Rate.
For a certain compound interest rate, we can find out the equivalent simple interest rate.
Let’s assume your initial principal amount is P. The compound interest rate is r% and the equivalent simple interest rate is R%. The common time period is n years. The compound rate and its equivalent simple interest rate will return the same amount of interest. Hence,
P*n*R = P(1+r)^n-P
⇒ R = ((1+r)^n-1)/n
This is the relation between the compound interest rate and the simple interest rate.
We will use this relation to calculate the equivalent simple interest rate.
3 Easy Steps to Convert Compound Interest to Simple Interest in Excel
This is the dataset that I am going to use for this article. I have some compound interest rates and am going to convert them to simple interest rates. The time period is 4 years.
Step 1: Write Down Formula to Calculate Simple Interest Rate
- Go to C7 and write down the following formula
=((1+B7)^$C$4-1)/$C$4
Read More: How to Calculate Simple Interest on Reducing Balance in Excel
Step 2: Determine Simple Interest Rate
- Press ENTER to determine the simple interest rate.
- Convert the output to the percentage format.
Read More: How to Calculate Simple Interest Loan Payments in Excel
Step 3: AutoFill to Complete Compound Interest Rate Conversion
- Now, use the Fill Handle to AutoFill up to C13. Excel will calculate the equivalent simple interest rates.
Things to Remember
- The Absolute Reference ($) is used to lock C4.
Download Practice Workbook
Download this workbook and practice while going through this article.
Conclusion
In this article, I have shown how to convert compound interest to simple interest in Excel. I hope it helps everyone. If you have any kind of suggestions, ideas, or feedback, please feel free to comment down below.
