Learn how to calculate simple interest in Excel with easy formulas and practical examples. Discover how to calculate interest for years, months, and days, find total loan repayment amounts, and create a simple interest calculation table.
How to Calculate Simple Interest in Excel (Formula, Examples & Step-by-Step Guide)
Calculating simple interest is one of the most common financial tasks, whether you are lending money, borrowing funds, calculating savings returns, or preparing a loan repayment record. Doing these calculations manually is easy when you have only one transaction, but it can become time-consuming when you need to calculate interest for multiple amounts and different time periods.
Microsoft Excel makes this process much easier. With a simple formula, you can calculate interest, total repayment amounts, and even compare different loan scenarios within seconds.
In this guide, you will learn how to calculate simple interest in Excel using practical examples, easy formulas, and a ready-to-follow table. You do not need advanced Excel knowledge to get started.
What Is Simple Interest?
Simple interest is the interest calculated only on the original amount of money, also known as the principal. Unlike compound interest, it does not include previously earned or accumulated interest in future calculations.
For example, suppose you lend someone $5,000 at an annual interest rate of 8% for 3 years. You will earn interest on the original $5,000 throughout the entire period.
The interest remains the same each year because the principal amount does not change.
Simple interest is commonly used in:
Personal loans between individuals.
Short-term borrowing and lending.
Certain business transactions.
Basic savings and investment calculations.
Educational finance calculations.
Simple Interest Formula
Before calculating simple interest in Excel, it is important to understand the basic mathematical formula.
The standard formula is:
Where:
Symbol | Meaning |
|---|---|
SI | Simple Interest |
P | Principal Amount |
R | Annual Interest Rate (%) |
T | Time Period in Years |
Example:
Suppose you invest $10,000 at an annual interest rate of 5% for 2 years.
Using the formula:
Your total amount after 2 years will be:
You earn $1,000 in interest, while your original investment remains $10,000.
How to Calculate Simple Interest in Excel
Excel does not require a special function to calculate simple interest. You can use a basic multiplication formula to get the result.
Let's create a simple worksheet.
Step 1: Enter Your Financial Details
Open Microsoft Excel and create the following table:
Cell | Description | Value |
|---|---|---|
A1 | Principal Amount | |
B1 | Amount | 10000 |
A2 | Annual Interest Rate | |
B2 | Rate (%) | 5 |
A3 | Time Period (Years) | |
B3 | Years | 2 |
A4 | Simple Interest | |
B4 | Result |
Step 2: Apply the Simple Interest Formula
Click cell B4 and enter this formula:
=B1*B2*B3/100Press Enter.
Excel will automatically calculate the simple interest.
This means you will earn $1,000 in interest over 2 years.
Step 3: Calculate the Total Amount
If you also want to calculate the final amount, enter this formula in another cell:
=B1+B4The result will be $11,000.
This formula adds the original principal and the interest earned.
Simple Interest Calculation Table in Excel
If you regularly calculate interest for different loans or investments, creating a table is more convenient than calculating each amount separately.
Here is a practical example:
Principal | Rate | Time (Years) | Simple Interest | Total Amount |
|---|---|---|---|---|
$5,000 | 5% | 2 | $500 | $5,500 |
$10,000 | 6% | 3 | $1,800 | $11,800 |
$15,000 | 8% | 2 | $2,400 | $17,400 |
$20,000 | 10% | 4 | $8,000 | $28,000 |
$25,000 | 7% | 3 | $5,250 | $30,250 |
To create the same table in Excel, organize your columns like this:
Column A: Principal Amount
Column B: Interest Rate
Column C: Time Period
Column D: Simple Interest
Column E: Total Amount
Enter the following formula in cell D2:
=A2*B2*C2/100In cell E2, enter:
=A2+D2Now drag both formulas down to apply them to the remaining rows.
Important: The interest rate in this example is entered as a whole number, such as 5 for 5%. If you enter it as 5% using Excel's percentage format, use =A2*B2*C2 instead.
How to Calculate Simple Interest for Months in Excel
Sometimes, you need to calculate interest for a few months instead of a full year.
In that situation, convert the number of months into years by dividing it by 12.
The formula becomes:
Where M represents the number of months.
Example:
Suppose you borrow $12,000 at an annual interest rate of 10% for 6 months.
Enter the following values in Excel:
Description | Value |
|---|---|
Principal Amount | $12,000 |
Annual Interest Rate | 10% |
Time Period | 6 Months |
Use this formula:
=A2*B2*C2/1200Assuming:
A2 = 12000
B2 = 10
C2 = 6
The simple interest for 6 months will be $600.
How to Calculate Simple Interest for Days in Excel
You may also need to calculate interest for a specific number of days, especially when managing short-term loans or business transactions.
For this calculation, you can use 365 days as the standard year.
The formula is:
Where D represents the number of days.
Example:
Suppose you lend $20,000 at an annual interest rate of 12% for 90 days.
Enter the following formula:
=A2*B2*C2/(100*365)Assuming:
A2 = 20000
B2 = 12
C2 = 90
The result will be approximately $591.78.
This means the borrower owes approximately $591.78 in interest for 90 days.
Some financial agreements use a 360-day year instead of 365 days. Always check the applicable loan terms before calculating interest.
How to Calculate Total Amount with Simple Interest in Excel
Calculating interest alone is not always enough. If you are managing a loan or preparing a financial report, you may also need to know the total amount payable.
The formula is:
Where:
A = Total Amount
P = Principal Amount
SI = Simple Interest
For example, if your principal is $15,000 and the calculated interest is $2,400, the total amount will be $17,400.
In Excel, use:
=A2+(A2*B2*C2/100)This formula calculates both the interest and the final amount in a single step.
Difference Between Simple Interest and Compound Interest
Simple interest and compound interest are two different ways of calculating returns or borrowing costs.
The main difference is how interest is calculated over time.
Feature | Simple Interest | Compound Interest |
|---|---|---|
Calculation | Original principal | Principal plus accumulated interest |
Interest Amount | Usually fixed per period | Can increase over time |
Formula | P × R × T / 100 | P × (1 + R/100)^T |
Calculation | Easier | More complex |
Common Use | Certain loans | Savings and investments |
For example, if you invest $10,000 at 10% annually for 3 years:
Simple interest: $3,000
Compound interest: $3,310
The difference is $310 because compound interest also earns returns on previously accumulated interest.
Useful Excel Tips for Accurate Interest Calculations
A small mistake in the interest rate or time period can change your final result. Keep these practical tips in mind when working with financial calculations.
Check the interest rate: Make sure you know whether the rate is entered as 5 or 5%.
Use the correct time unit: Convert months into years or use the monthly formula.
Choose the correct day-count convention: Confirm whether your agreement uses 360 or 365 days.
Use cell references: Avoid typing every value directly into formulas. Cell references make calculations easier to update.
Format results as currency: Use Excel's Currency or Accounting format to make financial reports easier to read.
Check your formula: Compare one result with a manual calculation to make sure everything is correct.
If you manage multiple loans, you can also use Excel filters, conditional formatting, and summary tables to organize your records.
Frequently Asked Questions (FAQs)
1. What is the formula for simple interest in Excel?
Use =A2*B2*C2/100, where A2 is the principal, B2 is the annual interest rate, and C2 is the time in years.
2. Can Excel calculate simple interest automatically?
Yes. Once you enter the formula, Excel automatically updates the result whenever you change the principal, rate, or time period.
3. How do I calculate simple interest for 6 months in Excel?
Use =A2*B2*6/1200 when the annual interest rate is entered as a whole number.
4. How do I calculate total loan repayment in Excel?
Use =A2+(A2*B2*C2/100) to calculate the principal plus simple interest.
5. Is there a built-in simple interest function in Excel?
No. Excel does not have a dedicated simple interest function, but you can calculate it easily using basic mathematical formulas.
Final Thoughts
Calculating simple interest in Excel is straightforward once you understand the basic formula. Whether you are calculating loan interest, checking lending records, or estimating investment returns, a simple Excel formula can save time and reduce manual errors.
For annual calculations, use =A2*B2*C2/100. If you are working with months or days, adjust the formula according to the time period.
The best approach is to create a reusable Excel worksheet with separate columns for principal, interest rate, time, interest, and total amount. This makes it easier to manage multiple financial calculations and update your records whenever needed.

COMMENTS