Simple Interest Formula in Excel With Examples
By ExcelReady TeamUpdated 7 min read
The short answer
Excel has no SIMPLE INTEREST function, so you multiply: interest = principal × rate × time, written as =B1B2B3. The rate must be a yearly percentage and the time must be in years, so divide months by 12 and days by 365. For the total to repay, use =B1*(1+B2*B3). Lock a shared rate cell with $ before filling down.
Key takeaways
- Simple interest is principal × rate × time. In Excel that's a plain multiplication such as =B1B2B3.
- The total amount owed is principal × (1 + rate × time), or principal plus the interest.
- Keep rate and time in the same unit: a yearly rate needs time in years, so use months/12 or days/365.
- Lock a single rate cell with $ signs, like $B$1, so the formula still works after you fill it down.
- Simple interest only grows on the original amount. If interest earns interest, use compound interest instead.
On this page+
The simple interest formula in Excel is a plain multiplication: principal × rate × time. Excel has no special function for it, so you type something like =B1*B2*B3 and press Enter. This guide shows how to set it up, how to get the total amount, how to handle months and days, and how to fill the formula down a list of loans with one locked rate cell. It's for students, small-business owners and anyone facing a finance question in an Excel test.
What is simple interest?#
Simple interest is interest worked out only on the original amount you borrow or save. The original amount is called the principal. The interest does not earn more interest, so it grows by the same amount every year.
The formula has three parts:
Interest = Principal × Rate × Time| Part | Meaning | Example |
|---|---|---|
| Principal (P) | The starting amount | 10,000 |
| Rate (r) | The yearly interest rate | 5% |
| Time (t) | How long, in years | 3 |
So 10,000 at 5% for 3 years earns 10,000 × 0.05 × 3 = 1,500 in interest.
How to calculate simple interest in Excel#
To calculate simple interest, put each part in its own cell and multiply the cells. Keeping the inputs separate lets you change one number and see the result update.
| A | B | |
|---|---|---|
| 1 | Principal | 10000 |
| 2 | Rate | 5% |
| 3 | Years | 3 |
| 4 | Interest | =B1*B2*B3 |
- Type the labels in A1:A4 and the numbers in B1:B3.
- In B2, type
5%with the percent sign. Excel stores it as 0.05. - In B4, type
=B1*B2*B3and press Enter.
The result is 1500. If you're new to typing formulas, the rule is simple: start with =, use * to multiply, and click cells instead of typing numbers. The first formulas lesson practises exactly this.
A common mistake is typing 5 in the rate cell instead of 5%. Then Excel multiplies by 5, not 0.05, and the interest is 100 times too big. The guide to the percentage formula in Excel explains how percent values are stored.
How to find the total amount (principal plus interest)#
The total amount is what you repay, or what your savings grow to: the principal plus the interest. You can add the two, or use the full formula in one step:
=B1*(1+B2*B3)For the same numbers, this gives 10,000 × (1 + 0.05 × 3) = 11,500. The brackets matter. Excel multiplies before it adds, so B2*B3 is worked out first, then 1 is added, then the result is multiplied by the principal.
You can also turn the formula around to find a missing part:
| To find | Formula | With 10,000, 1,500 interest, 5%, 3 years |
|---|---|---|
| Rate | =Interest/(Principal*Time) | 1500/(10000*3) = 5% |
| Time | =Interest/(Principal*Rate) | 1500/(10000*0.05) = 3 years |
| Principal | =Interest/(Rate*Time) | 1500/(0.05*3) = 10,000 |
Simple interest for months or days#
The rate is almost always a yearly rate, so time must be in years too. Convert months and days before you multiply.
Months: divide by 12. For 10,000 at 5% for 18 months:
=10000*5%*18/12The result is 750.
Days: divide by 365. For 10,000 at 5% for 90 days:
=10000*5%*90/365The result is about 123.29. Some banks and bonds use a 360-day year instead, which gives 125.00 for the same loan. Check which rule your lender or textbook uses.
Between two dates: subtract the dates to get the days, or use YEARFRAC. If B4 holds 1 January 2026 and B5 holds 1 July 2026, that's 181 days:
=10000*5%*(B5-B4)/365The result is about 247.95. =YEARFRAC(B4,B5,3) gives the same fraction of a year (181/365). Without the third argument, YEARFRAC uses a 30/360 rule and returns exactly 0.5, so the interest becomes 250.
How to fill the formula down a list of loans#
When several loans share one rate, put the rate in one cell and lock it with dollar signs. This is called an absolute reference.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rate | 5% | ||
| 2 | ||||
| 3 | Loan | Principal | Years | Interest |
| 4 | Car | 10000 | 3 | =B4*$B$1*C4 |
| 5 | Laptop | 1500 | 2 | 150 |
| 6 | Course | 4000 | 1.5 | 300 |
Type the formula in D4, then select D4:D6 and fill down (Windows: Ctrl+D; Mac: ⌘D). B4 and C4 change to B5 and C5 on the next row, but $B$1 stays fixed. Without the dollar signs, the copy in D5 would point at B2, which is empty, and return 0.
Press F4 (Mac: ⌘T) while the cursor is on B1 in the formula to add the $ signs. The guide to the dollar sign in Excel formulas explains the mixed forms too.
Common simple interest mistakes#
Most wrong answers come from units and brackets, not from the formula itself.
- Rate typed as a whole number.
5means 500%. Type5%, or0.05. - Rate and time in different units. A yearly rate needs years. A monthly rate needs months. Never mix them.
- Missing brackets in the total.
=B1*1+B2*B3multiplies first and gives a nonsense total. Write=B1*(1+B2*B3). - Missing $ signs. Filling down
=B4*B1*C4moves the rate reference to B2, B3 and so on. Lock it as$B$1. - The wrong day count. Dividing by 360 when the lender uses 365 (or the other way round) changes the answer slightly. Check the rule first.
Simple vs compound interest#
Simple interest is calculated on the principal only. Compound interest adds each period's interest to the balance, so the next period earns interest on a bigger amount.
| Simple interest | Compound interest (yearly) | |
|---|---|---|
| Formula in Excel | =P*r*t | =P*(1+r)^t-P |
| 10,000 at 5% for 3 years | 1,500.00 | 1,576.25 |
| Grows by | The same amount each year | A bit more each year |
Most savings accounts and loans with regular payments compound. Use the compound interest formula in Excel or the FV function for savings, and the PMT function for loan repayments. Simple interest is common in textbook questions, short-term loans and some notes and bonds.
Practise it#
The formula is short, so the real skill is setting up the cells and the $ signs correctly. Practise writing formulas in a live spreadsheet, then lock a rate cell with absolute references and fill it down a list. Each step checks your answer instantly. For a timed set of money questions, try the finance Excel test.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
The yearly rate is in B1. In D4, calculate the simple interest on the car loan: principal (B4) × rate (B1) × years (C4). Lock the rate cell with $ signs so the formula could be filled down.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | |||||
| 3 | Interest | ||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 |
Tip: Multiply the three parts with *. Write the rate cell as $B$1 so it stays fixed when you fill down.
Practise this in a lesson
Frequently asked questions
What is the formula for simple interest in Excel?
Multiply principal, rate and time: =B1B2B3, where B1 is the amount, B2 the yearly rate as a percentage and B3 the time in years. There is no built-in SIMPLE INTEREST function.
How do I calculate simple interest for months in Excel?
Divide the number of months by 12 so time is in years: =B1B2B3/12. For example, 10,000 at 5% for 18 months is 100000.0518/12, which is 750.
How do I calculate simple interest between two dates?
Turn the dates into years first. Use (end-start)/365 for an actual/365 count, or YEARFRAC(start,end) for other day-count rules, then multiply by principal and rate.
How do I find the total amount with simple interest?
Use =P*(1+rt). For 10,000 at 5% for 3 years, =10000(1+0.05*3) gives 11,500: the 10,000 you started with plus 1,500 interest.
What is the difference between simple and compound interest?
Simple interest is always worked out on the original amount. Compound interest adds each period's interest to the balance, so later interest is bigger. Over 3 years at 5%, 10,000 earns 1,500 simple interest and about 1,576 compound.