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:
| Compounding | n | Maturity value (₹) | Effective yearly rate |
|---|---|---|---|
| Yearly | 1 | 1,46,932.81 | 8.00% |
| Quarterly | 4 | 1,48,594.74 | 8.24% |
| Monthly | 12 | 1,48,984.57 | 8.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:
| Year | Balance (₹) | Interest that year (₹) |
|---|---|---|
| 1 | 1,08,000.00 | 8,000.00 |
| 2 | 1,16,640.00 | 8,640.00 |
| 3 | 1,25,971.20 | 9,331.20 |
| 4 | 1,36,048.90 | 10,077.70 |
| 5 | 1,46,932.81 | 10,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
- Set n to 1 and confirm B6 equals B1 multiplied by (1.08) five times.
- Confirm B6 and B7 are identical to the paisa.
- 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.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


