Here’s an overview of the bank interest calculator we will make.

## Terms Related to the Bank Interest Calculation

Principal Amount (P): The balance at the beginning of the calculation.

Interest Rate (R): The rate at which interest is given.

Period (N): Number of periodic durations of the deposit or loan.

Compound Interest: Compound Interest extends to the added interests also. Banks mainly deal with compound interest.

Simple Interest: Simple interest works on the Principal Balance only. Banks may offer simple interest in special circumstances.

Compound Frequency (T): In the case of compound interest, interest is usually compounded daily, monthly, quarterly, semi-annually, and annually. Then the number of Compounding Times will be 365, 12, 4, 2, and 1 respectively.

## Simple Interest Calculation in Excel

We can easily calculate Simple Interest using the following formula:

Simple Interest = Principal Amount Rate of Interest Time Periods

Steps

• We have the following dataset.

• To find Simple Interest, enter the following formula in cell C9:
`=C4*C5*C6`
• You will see the result as follows:

• To calculate the final balance with interest added, apply the following formula in cell C10:
`=C4+C9`
• You will see the accumulated balance as shown below.

• At the end of each year, your account balance will be as follows.

## Compound Interest Calculation in Excel

We can calculate compound interest using the formulas given below.

• Compounded Amount (A) = P ✕ (1 +  I / T)  ✕ N ✕ T
• Compound Interest = (A – P)

Steps

• Apply the following formula in cell C12:
`=C4*(1+C5/C7)^(C6*C7)`

• Enter the following formula in cell C13 to get the compound interest.
`=C12-C4`

• At the end of each quarterly period, your account balance will be as follows.

• You can use this as a template for your bank interest calculator Excel sheet.

## Bank Interest Calculation in Excel for Different Interest Rates

Assume that you need to calculate the interest rates for the following dataset.

Steps

• Enter the following formula in cell C10:
`=C9*C5*\$C\$6`
• Use the Fill Handle tool to get other interest amounts to the right.

• Apply the following formula in cell C11 to get the compound interest. Use the Fill Handle tool to apply for other cells in the row.
`=C9*(((1+C5/\$C\$7)^(\$C\$6*\$C\$7))-1)`

• Next, to understand the above formula, just compare it to the following formula:
`= P*(((1+R/12)^(N*T))-1)`
• If you need to calculate the accumulated amounts, you can follow the methods above.

## EMI Calculation Using the PMT Function in Excel

We want to buy a car for \$35,000 and complete the payment in 12 EMIs at a 5% yearly interest rate. Let’s calculate the EMI.

Steps

• Apply the following formula in cell C8:
`=(((C4*C5/12)*(1+C5/12)^C6))/(((1+(C5/12))^C6)-1)`

• Compare the result with the following formula.
`=(((P*R/12)*(1+R/12))^N]/(((1+R/12)^N)-1)`

Excel also has an inbuilt function called the PMT function to calculate EMI.

How does the formula work?

The PMT function has the following arguments:

`=PMT(rate,nper,pv,[fv],[type])`
• Rate = Rate of interest applied on the borrowing.
• NPER = Number of monthly installments per loan term which is N in this case.
• PV = Present value or Principal amount (P)
• FV = Future value after last payment which is 0 in this case
• Type = 1 or 0. The payment due at the beginning of the month indicates 1 and 0 if it is at the end of the month.

Here’s how to use it:

• Enter the following formula instead in cell C8:
`=PMT(C5/12,C6,C4,,0)`

• Your monthly installments will be as follows.

## Things to Remember

• Convert the yearly interest rate to monthly, quarterly, etc. interest rate by dividing it by the compounding times (T).
• Make sure to use the absolute references properly.

<< Go Back to Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF