How to Calculate Compound Interest in Excel: FV Formula and Yearly Table

shape
shape
shape
shape
shape
shape
shape
shape
How to Calculate Compound Interest in Excel: FV Formula and Yearly Table - featured image

Compound interest is interest that is calculated on the original amount and on the interest that has already been added. Because the interest earns interest, the balance grows faster than with simple interest, and the longer the time, the bigger the difference. Excel can calculate it in one cell with a simple formula, or with the FV function, and it can also build a year by year table that shows how the balance grows. This guide explains the formula, shows worked examples that you can verify, covers monthly and daily compounding, adds regular deposits, and lists the common mistakes. The examples use made-up numbers for learning, and this is general information, not financial advice.

Updated November 2026

In this guide

  1. Simple versus compound interest
  2. The compound interest formula
  3. Example: yearly compounding
  4. Use the FV function
  5. Monthly and other compounding
  6. Effective annual rate
  7. Build a year by year table
  8. Add regular deposits
  9. Find the time or the rate
  10. Compare scenarios
  11. Compound interest on debt
  12. The rule of 72
  13. Common mistakes
  14. Practice problems
  15. Quick recap

Simple versus compound interest

With simple interest, you earn interest only on the original amount. If you deposit 1,000 at 5 percent for 3 years, you earn 50 each year, which is 150 in total, and the balance is 1,150. With compound interest, the interest of each year is added to the balance, and the next year earns interest on the larger amount. After year 1 the balance is 1,050. After year 2 it is 1,102.50. After year 3 it is 1,157.63. The compound result is a little higher than the simple one, and the gap grows each year.

The compound interest formula

The formula is A equals P times (1 plus r divided by n) raised to the power of n times t. A is the final amount, P is the principal, r is the annual rate as a decimal, n is the number of times interest is compounded per year, and t is the number of years. For yearly compounding, n is 1, and the formula is A = P(1 + r)^t. To find only the interest, subtract the principal from the final amount.

Example: yearly compounding

Take a principal of 1,000 at 5 percent for 3 years, compounded yearly. The final amount is 1,000 times 1.05 to the power of 3. Since 1.05 to the power of 3 is 1.157625, the result is 1,157.63 when rounded. The interest earned is 157.63. In Excel, put 1000 in B1, 0.05 in B2 and 3 in B3, and use =B1*(1+B2)^B3. The cell shows 1157.625, and you can format it with two decimals. Change any input, and the result updates at once.

Use the FV function

Excel has a built-in function for the future value. The syntax is FV(rate, nper, pmt, pv, type). For our example, =FV(0.05,3,0,-1000) returns 1,157.63. The rate is the rate per period, nper is the number of periods, pmt is the regular payment, which is 0 here, and pv is the present value. Enter pv as a negative number, because Excel treats money you pay in as negative and the result as positive. If you enter a positive pv, the result will be negative, which is a common cause of confusion.

Monthly and other compounding

If the interest is added monthly, divide the annual rate by 12 and multiply the years by 12. For 1,000 at 5 percent for 3 years with monthly compounding, use =FV(0.05/12,3*12,0,-1000). The result is 1,161.47, which is higher than the 1,157.63 from yearly compounding. Daily compounding, with 365 periods, gives about 1,161.83. The more often interest is added, the higher the final amount, but the difference gets smaller each time. Always match the rate per period and the number of periods.

Effective annual rate

The effective annual rate, or EAR, shows the true yearly growth when compounding is more frequent than once a year. For 5 percent compounded monthly, the EAR is (1 + 0.05/12)^12 – 1, which is 5.116 percent. Excel has the function EFFECT: =EFFECT(0.05,12) returns 0.05116. Banks sometimes quote the nominal rate and the EAR, and the EAR is the better number to compare between accounts. A higher compounding frequency raises the EAR above the nominal rate.

Build a year by year table

A table shows the growth in detail. In A2 to A6 type the years 0 to 4. In B2 type the starting balance, 1000. In B3 use =B2*(1+$F$1), where F1 holds the rate of 5 percent, and fill it down. The balances are 1,000, 1,050, 1,102.50, 1,157.63 and 1,215.51. Add a column C with the interest earned each year, =B3-B2, which gives 50, 52.50, 55.13 and 57.88. Notice how the yearly interest increases, which is the effect of compounding.

Add regular deposits

Many people save a fixed amount each month. The FV function handles this with the payment argument. If you start with 1,000 and add 100 at the end of each month for 3 years at 5 percent, use =FV(0.05/12,36,-100,-1000). The result is about 5,036.81. The deposits alone are 3,600, so the total paid in is 4,600, and the interest is about 436.81. To make deposits at the start of each month, set type to 1 as the last argument, which gives slightly more because each deposit earns one more month of interest.

Find the time or the rate

You can solve for other unknowns. To find how many years it takes to double at 5 percent with yearly compounding, use =NPER(0.05,0,-1,2), which returns about 14.21 years. To find the rate that turns 1,000 into 1,500 in 8 years, use =RATE(8,0,-1000,1500), which returns about 5.2 percent. See our CAGR guide for the growth rate between two values, and our loan payment guide for the opposite case of paying back a loan.

Compare scenarios

Build a small table that compares different rates and times. Put the rates, such as 3, 5 and 7 percent, in a row, and the years, such as 5, 10 and 20, in a column. Use a formula with mixed references, such as =1000*(1+B$1)^$A2, and fill it across and down. At 5 percent, 1,000 grows to 1,276.28 in 5 years, 1,628.89 in 10 years and 2,653.30 in 20 years. Tables like this show the power of time and rate. Use a line chart, as in our line graph guide, to show the curves.

Compound interest on debt

Compounding also works against you when you owe money. A credit card balance of 1,000 at 24 percent annual rate, compounded monthly, grows by 2 percent each month if you pay nothing. After 12 months the balance is 1,000 times 1.02 to the power of 12, which is about 1,268.24. That is more than the 1,240 you would get with simple interest. Understanding this explains why paying off high-interest debt early matters. Use the same formulas with the debt as the principal to see how fast a balance can grow.

The rule of 72

A quick mental shortcut is the rule of 72. Divide 72 by the annual rate in percent to estimate the years needed to double the money. At 6 percent, it is 72 divided by 6, which is 12 years. The exact answer from the formula is 11.9 years, so the rule is very close. At 9 percent, the rule gives 8 years, and the exact answer is 8.04. The rule works best for rates between about 6 and 10 percent. Use it for a fast estimate, and use the Excel formulas for the exact figure when the decision matters.

Common mistakes

The most common mistake is to use the annual rate without dividing it by the number of periods per year, when compounding monthly. Another is to enter pv with the wrong sign in FV, which makes the result negative. A third is to type the rate as 5 instead of 5 percent or 0.05. A fourth is to mix months and years, such as 3 years with a monthly rate. A fifth is to round the intermediate steps, which changes the final result slightly. A sixth is to forget taxes and fees, which reduce the real return.

Practice problems

Try these. Problem 1: 2,000 at 4 percent for 5 years compounded yearly gives 2,000 times 1.04 to the power of 5, which is 2,433.31. Problem 2: use FV for problem 1. Problem 3: find the EAR of 6 percent compounded monthly, which is 6.168 percent. Problem 4: 500 at 8 percent for 2 years compounded quarterly gives 500 times 1.02 to the power of 8, which is 585.83. Problem 5: build a table for 1,000 at 5 percent for 10 years and check that the balance at year 10 is 1,628.89.

Quick recap

Compound interest is calculated on the principal plus the interest already earned. In Excel, use =P*(1+r/n)^(n*t) or the FV function, and watch the signs. Match the rate and the periods when you compound monthly, build a year by year table to see the growth, and add regular deposits with the payment argument. Use EFFECT to compare nominal rates. The same formulas show how fast debt grows, so understanding compounding helps both with saving and with borrowing. Always check the inputs before you trust the result.

Frequently asked questions

What is the compound interest formula in Excel?

=P*(1+r/n)^(n*t), or use the FV function.

How do I use FV for compound interest?

Enter the rate per period, the number of periods, 0 for payment and the present value as a negative number.

How do I compound monthly?

Divide the annual rate by 12 and multiply the years by 12.

What is the effective annual rate?

The true yearly rate after compounding, which you can find with the EFFECT function.

Why is my FV result negative?

The present value or the payment has the wrong sign.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *