# How to Calculate Simple Interest and Compound Interest in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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 Interest. It is given below.

`I = p*r*t`

Here,

I = Simple Interest

p= Principal Amount

r = Rate of Interest

t = Time elapsed

## 2 Methods to Calculate Simple Interest in Excel

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 reaming 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 Total FV, and we will get the Interest (I). We can use the below-given 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. ## 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 the 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. ## Related Articles Zahid Hasan

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

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