How to Calculate Simple Interest in Excel (Formula & Examples)

Learn how to calculate simple interest in Excel using easy formulas. Explore step-by-step examples for yearly, monthly, and daily interest calculation

 

How to Calculate Simple Interest in Excel (Formula & Examples)

   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:

\[ SI = \frac{P \times R \times T}{100} \]

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:

\[ SI = \frac{10,000 \times 5 \times 2}{100} \]
\[ SI = \$1,000 \]

Your total amount after 2 years will be:

\[ Total = 10,000 + 1,000 = \$11,000 \]

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/100

Press Enter.

Excel will automatically calculate the simple interest.

Result: $1,000

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+B4

The 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/100

In cell E2, enter:

=A2+D2

Now 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:

\[ SI = \frac{P \times R \times M}{100 \times 12} \]

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/1200

Assuming:

  • A2 = 12000

  • B2 = 10

  • C2 = 6

Result: $600

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:

\[ SI = \frac{P \times R \times D}{100 \times 365} \]

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:

\[ A = P + SI \]

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

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content