Spreadsheets

Compound interest calculator in Excel: formula, FV, yearly table

Build a compound interest calculator in Excel with the manual formula, the FV function, a compounding frequency cell and a year-by-year table.

Editorial illustration for Compound interest calculator in Excel: formula, FV, yearly table

In Excel, compound interest is =P*(1+r/n)^(n*t), where P is the amount, r the yearly rate, n the compounding periods per year and t the years. The same result comes from =FV(r/n,n*t,0,-P). Put each input in its own cell, make n a cell you can change (1 for yearly, 4 for quarterly, 12 for monthly) and one sheet answers all three cases.

What cells do I need?

Set up inputs in column B and label them in column A:

  • B1: Amount, say 100000 (a round example, not a suggestion).
  • B2: Yearly rate, typed as 8% (Excel stores 0.08). It is an illustration, not a current rate from any bank.
  • B3: Compounding periods a year: 1, 4 or 12.
  • B4: Years, say 5.

Then the answers:

  • B6, maturity by manual formula: =B1*(1+B2/B3)^(B3*B4)
  • B7, maturity by FV: =FV(B2/B3,B3*B4,0,-B1)
  • B8, interest earned: =B6-B1
  • B9, effective yearly rate: =EFFECT(B2,B3)

The minus sign before B1 inside FV matters. FV treats money you put in as negative, so entering -B1 makes the answer come out positive. Without it you get a negative maturity value. The 0 is the periodic payment, because there are no further deposits.

Does the compounding frequency really change the answer?

Yes, slightly. Using the made-up ₹1,00,000, 8 percent and 5 years:

CompoundingnMaturity value (₹)Effective yearly rate
Yearly11,46,932.818.00%
Quarterly41,48,594.748.24%
Monthly121,48,984.578.30%

Check the quarterly line by hand: 8 percent divided by 4 is 2 percent a quarter, and there are 4 times 5, or 20, quarters. 1.02 raised to the 20th power is about 1.485947, so ₹1,00,000 grows to about ₹1,48,594.74.

How do I build a year-by-year table?

List years 0 to 5 in column D from D2 down. In E2 (year 0) write =$B$1. In E3 write the balance after that many years, directly from the formula:

=$B$1*(1+$B$2/$B$3)^($B$3*D3)

Fill down to year 5. In F3 write =E3-E2 for the interest earned that year. With yearly compounding (B3 = 1), the table reads:

YearBalance (₹)Interest that year (₹)
11,08,000.008,000.00
21,16,640.008,640.00
31,25,971.209,331.20
41,36,048.9010,077.70
51,46,932.8110,883.91

Notice the yearly interest rises every year even though the rate stays at 8 percent, because each year earns interest on the previous interest too. Round only the displayed value with number formats; do not wrap ROUND around the calculation unless you are copying a particular bank's rounding.

Can I use this for an FD maturity estimate?

As an estimate, yes, and it is a handy cross-check. Banks differ in how often they compound, how they count days in a part-year and how they round, so a deposit certificate or the bank's own figure should win when it differs from your sheet. Use the sheet to compare options on the same footing, for example the same ₹1,00,000 for the same years at two quoted rates and two compounding frequencies, and check the exact terms in the paperwork.

What if I add a fixed amount every month?

FV handles that through its payment argument. For a made-up ₹5,000 added at the end of every month for 5 years at 8 percent a year, use =FV(8%/12,60,-5000,0). The result is about ₹3,67,384, against ₹3,00,000 actually put in (5,000 times 60 months), so the rest is interest. Keep the periods and the rate on the same footing: monthly payments need a monthly rate and the number of months.

Quick checks before you share the sheet

  1. Set n to 1 and confirm B6 equals B1 multiplied by (1.08) five times.
  2. Confirm B6 and B7 are identical to the paisa.
  3. Change B2 to 0% and check the maturity equals the amount you put in.

This is a calculator for planning arithmetic, not advice on where to keep money.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports