Compound Interest Formula in Excel

Excel compound interest formula

The easiest way to calculate compound interest in Excel is usually the FV function:

=FV(rate, nper, pmt, pv, type)

What the FV arguments mean

• rate = interest rate per period.
• nper = total number of periods.
• pmt = regular payment each period. Use 0 if there are no deposits.
• pv = present value, usually entered as a negative number.
• type = 0 for end-of-period payments, 1 for beginning-of-period payments.

Monthly compounding, no deposits

For £10,000 at 5% for 10 years compounded monthly:

=FV(5%/12, 10*12, 0, -10000, 0)

This returns approximately £16,470.09.

Monthly compounding with monthly deposits

For £10,000 starting amount plus £250 deposited at the end of each month:

=FV(5%/12, 10*12, -250, -10000, 0)

For deposits at the beginning of each month, change the final argument to 1:

=FV(5%/12, 10*12, -250, -10000, 1)

Worked spreadsheet: monthly deposits using cell references

Build this example in a blank worksheet. Enter labels in column A and values in column B. It assumes a fixed nominal annual rate compounded monthly, equal monthly payments and no withdrawals, fees or tax.

With end-of-month deposits, B8 is £55,290.66, B9 is £40,000.00 and B10 is £15,290.66. The final balance is not all interest: it includes your initial £10,000 and 120 deposits of £250. Changing B6 to 1 gives £55,452.42, because each payment earns one extra month's interest.

Format B3 as a percentage and the amounts as currency with two decimal places. Use formatting to round the display, not ROUND inside the calculations, so the schedule and FV result use the same unrounded model.

Build the month-by-month schedule

In row 14, enter the headings Month, Opening balance, Deposit, Interest and Closing balance in columns A–E. Then enter these formulas:

Select A16:E16 and fill down through row 134 for this ten-year example. Row 134 is month 120: E134 should match B8, and =SUM(D15:D134) should match B10. If you change the number of years, extend or shorten the schedule to match B4*12 months; changing B4 alone does not resize these rows. The dollar signs keep the input references fixed while the row references move.

For the default end-of-month timing, the first three closing balances are £10,291.67, £10,584.55 and £10,878.65. The interest formula adds the current deposit to the interest-bearing balance only when B6 is 1.

Common spreadsheet mistakes to avoid

• Entering 5 instead of 5%. Excel stores 5% as 0.05. A plain 5 means 500% in these formulas.
• Mixing annual rates with monthly periods. For this nominal-rate example, use B3/12 and B4*12 together. If your input is an effective annual rate (AER/APY), the equivalent monthly rate is (1+B3)^(1/12)-1, not B3/12; use it consistently in both FV and the schedule.
• Counting deposits as interest. Subtract both the starting balance and all regular deposits from the final balance.
• Mixing payment timing. Use B6 = 0 for payments after interest or B6 = 1 for payments before interest, in both the FV calculation and schedule.
• Using inconsistent cash-flow signs. Here the inputs are positive, so the FV formula applies minus signs to B2 and B5 to return a positive final balance.
• Rounding each month accidentally. Currency display formatting leaves the underlying precision intact. Real accounts may round posted interest, so their statements can differ from this model.

Some regional Excel settings use semicolons instead of commas between arguments. If a copied formula is rejected, use your spreadsheet's local argument separator.

For an editable teaching example, download the regular-savings classroom workbook. Its worked sheets compare beginning/end payments over 24 periods using a rate per period and AUD display amounts; it is not the ten-year layout above. For monthly practice at a 5% nominal annual rate, enter =5%/12 in its rate-per-period input. See the regular-savings lesson for the accompanying activity.

Daily compounding in Excel

For daily compounding, divide the annual rate by 365 and multiply the years by 365:

=FV(5%/365, 10*365, 0, -10000, 0)

Continuous compounding in Excel

For continuous compounding without deposits, use EXP:

=10000*EXP(5%*10)

Copy a formula from the calculator

The main calculator can generate a matching Excel FV formula for a lump sum or constant monthly payments without tax, fees or an exhausted balance. Weekly payments use the calculator's monthly average. Inflation adjustment is shown separately. Enter your numbers, select Calculate, then use Copy Excel formula.

The calculator's Excel download contains calculated values in two sheets, with no calculation formulas. It records the full monthly schedule, including annual or increasing payments, but changing its inputs will not recalculate the results. See the Excel download instructions and preview, or use the cell-reference model above for calculations inside Excel.

Open the £10,000 Excel example