Banks offer interest to their clients for depositing money in their banks. Alternatively, clients need to pay interest to the banks for loans. So, we often need to calculate interest for loans or deposits. This article shows how to create a bank interest calculator Excel sheet easily. The following picture gives an idea of what the article is about.
Terms Related to Bank Interest Calculation
Take a quick look at the following terms. It will help to understand the calculations.
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 formula as follows:
Follow the steps below to apply this formula.
Steps
- Assume we have the following dataset.
- Now, to find Simple Interest, enter the following formula in cell C9:
=C4*C5*C6- You will see the result as follows:
- Now, 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.
Read More: Create a Daily Loan Interest Calculator in Excel
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)
Follow the steps below to apply those formulas.
Steps
- At first, apply the following formula in cell C12:
=C4*(1+C5/C7)^(C6*C7)
- After that, 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.
Read More: How to Create a Daily Compound Interest Calculator
Bank Interest Calculation in Excel for Different Interest Rates
Assume that you need to calculate the interest rates for the following dataset.
Then follow the steps below.
Steps
- First, enter the following formula in cell C10:
=C9*C5*$C$6- Then, use the Fill Handle tool to get other interest amounts.
- After that, apply the following formula in cell C11 to get the compound interest. Use the Fill Handle tool as earlier.
=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 earlier methods.
EMI Calculation Using the PMT Function in Excel
Equated Monthly Installments or EMI is a very popular phenomenon nowadays. It is a fixed monthly payment by the borrower to repay a loan. You must learn how to calculate EMI to optimize your lending or borrowing options.
Assume, you want to buy a car for $35,000 and complete the payment in 12 EMIs at 5% yearly interest rate.
Follow the steps below to calculate the EMI.
Steps
- At first, apply the following formula in cell C8:
=(((C4*C5/12)*(1+C5/12)^C6))/(((1+(C5/12))^C6)-1)
- You can easily understand the above formula by observing and comparing the following formula.
=(((P*R/12)*(1+R/12))^N]/(((1+R/12)^N)-1)Fortunately, 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])The arguments signify the followings:
- 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.
Now, let’s check out this formula.
- First, enter the following formula instead in cell C8:
=PMT(C5/12,C6,C4,,0)
- Your monthly installments will be as follows.
Things to Remember
- Don’t forget to 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.
Download Bank Interest Calculator
You can download the Interest Calculator Workbook from the download button below.
Conclusion
Now you know how to calculate simple and compound interests, interests with conditional interest rates, and EMIs. Please use the comment section below for further queries or suggestions.
Further Readings
- Create Reverse Compound Interest Calculator in Excel
- Make Quarterly Compound Interest Calculator in Excel
- How to Develop CD Interest Calculator in Excel
- How to Create SIP Interest Calculator in Excel
- How to Generate Overdraft Interest Calculator in Excel
<< Go Back to Interest Calculator | Finance Template | Excel Templates
Get FREE Advanced Excel Exercises with Solutions!
