Compound Interest Formula in Excel: Calculate and Check Future Value

For a single starting balance with no deposits or withdrawals, calculate compound interest in Excel with =A1*(1+A2)^A3, where A1 is the starting balance, A2 is the rate per period, and A3 is the number of periods. The rate and period count must use the same time unit.

This guide covers the basic lump-sum calculation in Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. It also explains how to check the result period by period.

What you need

• A starting balance.
• An interest rate for each period, or the information needed to convert a quoted annual rate to a periodic rate.
• The number of periods, expressed in the same unit as the rate.

The basic formula does not include recurring deposits or withdrawals.

1. Enter the starting balance, periodic rate, and number of periods

Set up one cell for each input so the rate and period count are easy to compare.

1. Enter the starting balance in A1. For example, enter 1000.
2. Enter the rate per period in A2. For an annual calculation at 5%, enter 5%.
3. Enter the number of periods in A3. For two annual periods, enter 2.

The rate period and the period count must match. Use an annual rate with years, or a monthly rate with months. If A2 contains an annual rate while A3 counts months, convert one of them before calculating.

For monthly periods, if the quoted rate is a nominal annual rate convertible monthly, enter the annual rate divided by 12 as the periodic rate, and enter years multiplied by 12 as the number of periods. For example, a 5% nominal annual rate over two years becomes 5%/12 and 24 periods.

If the quoted rate is an effective annual rate, do not assume that dividing by 12 gives the equivalent monthly rate. Convert it using (1 + effective annual rate)^(1/12) - 1, then use that monthly rate with the number of months. The rate convention determines which conversion is appropriate.

This setup is for a single starting balance. Do not treat a recurring payment as part of A1 or assume that the lump-sum formula includes deposits or withdrawals.

2. Calculate the future value with the compound-interest formula

In a result cell, enter =A1*(1+A2)^A3. The formula multiplies the starting balance by one plus the rate per period, raised to the number of periods. The result is the ending balance under those assumptions.

Check an annual calculation

With 1000 in A1, 5% in A2, and 2 in A3, the formula returns $1,102.50. The balance grows to $1,050.00 after the first year, then earns 5% on that updated balance during the second year.

Use monthly periods only with a matching monthly rate

For the same $1,000 starting balance and a 5% nominal annual rate convertible monthly over two years, use 5%/12 as A2 and 24 as A3. The formula returns approximately $1,104.94. This is a calculation from those assumptions, not a quoted account or investment return. Monthly capitalization produces a different amount because interest is added to the balance more often.

When Excel’s FV function fits

Excel’s FV function can calculate future value for a lump sum, fixed periodic payments, or both. Its syntax is FV(rate,nper,pmt,[pv],[type]): rate is the rate per period, nper is the total number of periods, pmt is the same payment each period, and pv is the present value, or starting lump sum. The optional type is 0 for payments at the end of each period or 1 for payments at the beginning. Cash paid out, such as a savings deposit, is entered as a negative value; cash received is positive.

For the $1,000 annual example, a complete FV formula is =FV(5%,2,0,-1000). The negative present value represents the starting deposit, and the result is positive. For $100 deposited at the end of each month for 10 years at a 5% nominal annual rate, =FV(5%/12,120,-100,0,0) gives about $15,528. That illustration assumes equal month-end deposits and produces a calculated balance, not a guaranteed return.

The pmt argument must stay the same throughout the periods. If deposits or withdrawals vary, or occur on irregular dates, use a schedule that records each actual cash flow and its timing instead of treating it as a fixed payment. The simple power formula also excludes fees and taxes.

3. Check the result with a period-by-period balance table

A schedule makes it possible to see whether interest is applied to the updated balance each period. For a calculation without deposits or withdrawals, create columns for the period, opening balance, interest for that period, and closing balance.

Build the period-by-period schedule

For the $1,000 example at 5% annually, enter the first period as follows:

• Period: 1
• Opening balance: 1000
• Interest: opening balance multiplied by 5%, or =B2*5% if the opening balance is in B2
• Closing balance: opening balance plus interest, or =B2+C2 if interest is in C2

For the next row, use the prior row’s closing balance as the new opening balance. Apply the same rate once, then add that period’s interest to get the closing balance. Continue for the number of periods in the calculation.

Compare the last balance with the formula

The first two closing balances should be $1,050.00 and $1,102.50. The last closing balance should equal the result from =A1*(1+A2)^A3 when both calculations use the same rate, period count, and no deposits or withdrawals.

If the totals differ, check that the schedule applies the rate once per period and that the rate and period count match the formula inputs. The check is not like-for-like if one calculation includes a cash flow or uses different payment timing and the other does not.

Common mistakes to avoid

• Entering 5 when you mean 5%. Use 5% or its decimal equivalent, 0.05.
• Pairing an annual rate with a monthly period count, or a monthly rate with an annual count.
• Dividing an effective annual rate by 12 without converting it to an equivalent monthly effective rate.
• Expecting the lump-sum formula to include recurring deposits, withdrawals, fees, or taxes. Excel’s FV function can handle equal periodic payments, but unequal or irregular cash flows need a schedule.

Quick reference

An Excel result models the values and timing entered. It does not establish a bank’s contractual rate, a loan’s repayment terms, or an investment’s actual return.