Cellwise Start free

Excel guide · 4 min read

How to calculate simple interest in Excel

Quick answerSimple interest is principal x rate x time: with the principal in B2, the annual rate in B3 and the years in B4, use =B2*B3*B4. For the total including the principal, use =B2*(1+B3*B4). Between two dates, replace the years with the days divided by the days in a year: =B2*B3*(B6-B5)/365.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

AB
1InputValue
2Principal12000
3Annual rate (enter your own)0.045
4Years3
5Start dateJan 15, 2026
6End dateSep 30, 2026
7Days in year (365 or 360)365
8Months18

Download this sheet as an Excel practice file (.xlsx)

The basic formula: =P*r*t

Put each input in its own cell, so you can change it without touching the formula. The sheet has a principal of 12,000 in B2, an annual rate in B3 and a term of 3 years in B4. The 4.5% here is only an example; type the rate from your own agreement.

=B2*B3*B4 returns 1,620 of interest. Simple interest is charged only on the original principal, so it is the same 540 every year. The rate must be stored as a percentage (4.5%, which is 0.045). If you type 4.5 into a plain number cell, the formula treats it as 450% and returns 162,000.

=B2*B3*B4

Result: 1620

1,620: 12,000 x 4.5% x 3 years.

Total amount: principal plus interest

The amount repaid or the final balance is the principal plus the interest. =B2+B2*B3*B4 and =B2*(1+B3*B4) both return 13,620. The second is the textbook form, A = P(1 + rt).

For a term in months, divide by 12: with 18 months in B8, =B2*B3*B8/12 returns 810. The interest for one month is =B2*B3/12, which is 45.

=B2*(1+B3*B4)

Result: 13620

13,620: the 12,000 principal plus 1,620 of interest.

Interest between two dates

Short-term loans and late-payment interest are usually counted in days. Subtracting the start date from the end date gives the days: 15 January to 30 September 2026 is 258 days. Divide by the days in a year to turn it into a fraction of a year.

Whether a year has 365 or 360 days depends on the agreement (it is called the day-count basis), so keep it in an input cell too. With 365 in B7 the interest is 381.70; with 360 it is 387.00. Some agreements also count both the first and last day, which adds one day: =ROUND(B2*B3*(B6-B5+1)/B7,2) returns 383.18. Check which rule applies to you.

Excel also has YEARFRAC, which returns the fraction of a year between two dates with a basis argument (1 for actual/actual, 2 for actual/360, 3 for actual/365). The days/basis version below is easier to check by hand.

=ROUND(B2*B3*(B6-B5)/B7,2)

Result: 381.7

381.70 for 258 days on a 365-day basis. Change B7 to 360 and it becomes 387.00.

Simple vs compound interest, year by year

Compound interest also earns interest on interest already added, so it grows faster. On the same inputs, compound interest over 3 years is =B2*(1+B3)^B4-B2, which is 1,693.99, about 74 more than simple interest.

In Excel 365 the formula below builds a comparison table in one go: the year, the simple interest so far and the compound interest so far. Simple interest goes up by 540 a year (540, 1,080, 1,620). Compound goes 540, 1,104.30, 1,693.99. The gap widens every year, which is why the type of interest matters more on long terms.

=HSTACK(SEQUENCE(B4),B2*B3*SEQUENCE(B4),ROUND(B2*(1+B3)^SEQUENCE(B4)-B2,2))

Result: spills 3 rows × 3 columns

1540540
210801104.3
316201693.99

A 3-row table: year 1 is 540 vs 540, year 2 is 1,080 vs 1,104.30, year 3 is 1,620 vs 1,693.99.

Work backwards: find the rate or the time

Rearrange I = P*r*t when you know the interest and need one of the other parts. If 12,000 earned 1,620 over 3 years, the rate is =1620/(B2*B4), which is 0.045, or 4.5% once you format it as a percentage.

If you know the rate and the interest, the time is =1620/(B2*B3), which returns 3 years. In a real sheet, put the interest in its own cell and reference it rather than typing 1620.

=1620/(B2*B4)

Result: 0.045 shows as 4.5% with Percent format

0.045, which shows as 4.5% when formatted as a percentage.

Common mistakes

Typing the rate as 4.5 in a number cell

Excel reads it as 450%. Type 4.5% (or 0.045), or format the cell as Percentage before typing.

Using a monthly term with an annual rate

Divide months by 12 (or days by 365 or 360) so the time is in years, the same unit as the rate.

Hardcoding 365 inside every formula

Put the day basis in a cell. Agreements differ, and one cell is easier to change and to check.

Rounding before adding up

Round the final interest, not the inputs. A rate rounded to 4% or days rounded to months changes the answer.

FAQ

What is the simple interest formula in Excel?

=P*r*t, written with cell references, for example =B2*B3*B4 for principal, annual rate and years. For the total amount, use =B2*(1+B3*B4).

Does Excel have a simple interest function?

No. Excel's financial functions such as FV, PMT and IPMT assume compound interest. Simple interest is a plain multiplication, so write it as =P*r*t.

How do I calculate daily simple interest?

Divide the annual rate by the day basis: =B2*B3/365 is the interest for one day, about 1.48 on 12,000 at 4.5%. Multiply by the number of days for a period.

Should I use 365 or 360 days?

It depends on the agreement or the market convention for that product, so check the contract. Put the number in an input cell; the formula is the same either way.

Functions used

Related guides

Updated