Calculating Simple Interest and Compound Interest is a common occurrence in our day-to-day lives but on the other hand, it can give us a hard time if we don’t know how to calculate this. Microsoft Excel is a powerful software that allows us to quickly and simply calculate Simple Interest as well as Compound Interest.
Table of Contents Expand
What Is Simple Interest?
Simple Interest doesn’t compound. In other words, Simple Interest is the interest calculated on the principal portion of a loan or the original contribution to a savings account. In addition, the account holder will gain interest only against the first deposit and the borrower will pay interest only on the initially borrowed amount.
Arithmetic Formula to Calculate Simple Interest
We will use a very straightforward formula to find Simple Interests. It is given below.
I = p*r*t
Here,
I = Simple Interest
p= Principal Amount
r = Rate of Interest
t = Time elapsed
How to Calculate Simple Interest and Compound Interest in Excel: 2 Ways
In this section, we are going to learn how to calculate simple interest in 2 simple and easy-to-use methods. Consequently, you will be able to calculate the Simple Interest without any problems.
In the following dataset, we have a Principal Amount (p) that is deposited in the bank for 5 years. The bank will provide 3% Simple Interest each year. Our goal is to find these simple interests for each year.
1. Using Arithmetic Formula
We are going to compute the Simple Interest by using the Simple Interest Formula.
Steps:
- First, to calculate Simple Interest for Year 1, we can use the following formula in cell E5.
=C5*B5*D5
Here, cell E5 refers to the cell of Simple Interest, cell C5 denotes the cell of Principal, cell B5 represents the cell of Time in years and cell D5 denotes the Rate of Interest.
Afterward, drag the Fill Handle up to cell E9 and you will get Simple Interest for the remaining years.
2. Utilizing the FV Function to Calculate Simple Interest in Excel
Here, we are going to use the FV function (Future Value function) of Excel. To operate this function must require 3 information and 2 optional information. These are:
- rate = rate of compounding interest
- nper = total number of payment periods
- pmt = amount of payment in each period
- pv = present value
- type = This indicates when payments are due. Use 0 for defining the end of the period and 1 for the beginning of the period.
In the case of Simple Interest, there is no Compounding Interest Rate. Hence, the rate in the FV function will be 0.
Now, let’s calculate the Simple Interest using the FV function.
Steps:
- Firstly, to compute SImple Interest for Year 1, we can use the following formula in cell E5.
=-FV(0,B5,(C5*D5),C5)
Here, cell E5 refers to the cell of Total FV.
- Afterward, subtract the Principal (p) from the Total FV, and we will get the Interest (I). We can use the formula in cell F5 to calculate our Simple Interest.
=E5-C5
Here, cell F5 represents the cell of Interest.
- Next, select cell E5 and cell F5 together. Then drag the Fill Handle down, up to the end of the data.
Afterward, you will get the Simple Interests for all 5 Years.
Read More: How to Calculate Simple Interest Loan Payments in Excel
What Is Compound Interest?
Simply put, Compound Interest is when you earn interest on your interest. When you put money into a savings account that earns Compound Interest, you will get interest on both the money you put in and the interest that builds up over time.
Arithmetic Formula to Calculate Compound Interest
We will use the following formula to calculate Compound Interest.
CI = p(1+r/n)^nt
Here,
CI = Compound Interest
p = Principal Amount
r = Rate of Compound Interest
n = Number of compoundings per unit time
t = Time
2 Methods to Calculate Compound Interest in Excel
Now, we are going to learn about 2 easy and effective methods for computing Compound Interest in Excel.
In the following data set, we have a Principal Amount (p) deposited in a bank with a 5% Compounding Interest Rate. In the data set, we have 5 types of compoundings. They are-
| Compounding Type | Number of Compounding Per Year (n) | Explanation |
|---|---|---|
| Yearly | 1 | Once a year |
| Half – Yearly | 2 | Once every 6 months ( 2 times a year) |
| Quarterly | 4 | Once every 3 months ( 4 times a year) |
| Monthly | 12 | Once every month ( 12 times a year |
| Daily | 365 | Once every day ( 365 times a year) |
Our aim is to calculate the Compound Interests for these different types of compounding.
1. Using Arithmetic Formula
Here, we are going to use the Arithmetic Formula to calculate the Compound Interest in Excel. Let’s proceed step by step.
Steps:
- First, to compute the Final Amount (A), we can use the following formula in cell G5.
=C5*(1+(D5/F5))^(F5*B5)
Here, cell F5 denotes the cell of Number pf Compounding Per Year,and cell G5 represents the cell of Final Amount.
- Next, to get the Compound Interest (CI) we need to subtract the Principal from the Final Amount. Therefore, to obtain Compounding Interest for Yearly Compounding, we can use the below-given formula in cell H5.
=G5-C5
Here, cell H5 refers to the cell of Compound Interest.
- After that, select cell G5 and cell H5 together. Then drag the Fill Handle down or just double-click it.
Eventually, you will have Compounding Interests in all types of compounding.
2. Applying FV Function to Calculate Compound Interest in Excel
Now, we are going to use the FV function of Excel again to calculate our Compound Interest in Excel.
Steps:
- Firstly, to calculate the Final Amount for the yearly compounding, we can use the following formula in cell G5.
=FV(D5/F5,B5*F5,0,-C5)
- Afterward, to find the Compound Interest (CI), we need to subtract the Principal (P) from the Final Amount (FV). We can use this formula in cell H5.
=G5-C5
- Next, choose both cell G5 and cell H5 at the same time. Then either double-click the Fill Handle or drag it down.
Afterward, you will now have the Compounding Interests for all types of compoundings.
Things to Remember
- While using the FV function to Calculate Simple Interest, make sure you put a negative sign before it. Because in this function Excel by default thinks it is a cash outflow.
- For the same reason put a negative sign before the Present Value, while computing Compounding Interest Using theFV function.
Download Practice Workbook
Calculate Simple Interest and Compound Interest.xlsx
Conclusion
Finally, we have come to the end of our article. Thank you for taking the time to read this article. I really hope that this article has provided you with the answers you were looking for when it comes to the calculation of simple and Compound Interest rates. Please feel free to ask any further questions in the comments section below.
Happy learning!
Related Articles
- Convert Compound Interest to Simple Interest in Excel
- How to Calculate Simple Interest on Reducing Balance in Excel
<< Go Back to Simple Interest Formula in Excel |Excel for Finance | Learn Excel
