Compound Interest in Excel and Google Sheets: Formulas That Work

The short answer: in Excel or Google Sheets, =FV(rate/n, n*years, -payment, -start) returns the final balance of an investment with compound interest. For EGP 100,000 at 18% compounded monthly for 3 years, =FV(0.18/12, 36, 0, -100000) returns 170,913.95. The same formulas work in both programs. This guide gives you copy-ready versions for every common question: final balance, effective rate, rate needed, time needed, and the deposit required to hit a goal.

First, the plain formula (no special function)

You can type the textbook formula directly. If cell B1 holds the start amount, B2 the annual rate (as 0.18 or as 18%), B3 the compounding periods per year, and B4 the years:

If you want to go deeper on this, see Calculating Compound Interest.

=B1*(1+B2/B3)^(B3*B4)

With 100000, 0.18, 12 and 3, the result is 170,913.95. If your rate cell shows 18% (a percentage format), Excel stores it as 0.18, so you do not divide by 100. If you typed the plain number 18 without the percent format, it will be treated as 1,800%. That is the single most common error. For a deeper explanation of the formula itself, see calculating compound interest.

The FV function

Syntax: FV(rate, nper, pmt, [pv], [type])

ArgumentMeaningExample
rateRate per period0.18/12 for monthly
nperTotal number of periods36 for 3 years monthly
pmtRegular payment per period (negative if you pay it in)−2000, or 0 for none
pvPresent value, the starting amount (negative if you deposit it)−100000
type0 = payments at period end, 1 = at period start0 (default)

It also helps to read Key of Greens New Zayed by TBK before you decide.

The type argument is 0 (default) when payments happen at the end of each period and 1 when they happen at the start. The key rule about signs: money you pay in is negative and money you get back is positive, so a positive result means "what you will have". If you enter the start amount as a positive number, the function returns a negative result; the magnitude is the same.

Example 1: lump sum, no deposits

=FV(0.18/12, 36, 0, -100000) returns 170,913.95. For annual compounding use =FV(0.18, 3, 0, -100000), which returns 164,303.20.

Example 2: start amount plus monthly deposits

=FV(0.15/12, 60, -2000, -50000) returns 282,508.08 for EGP 50,000 plus EGP 2,000 deposited at the end of each month for 5 years at 15%. If you deposit at the start of each month, add the type argument: =FV(0.15/12, 60, -2000, -50000, 1) returns 284,722.45. Depositing at the start of the month earns one extra month of interest on each deposit, a difference of EGP 2,214.37 over five years.

EFFECT and NOMINAL: converting between rates

=EFFECT(nominal_rate, npery) returns the effective annual rate. =EFFECT(0.18, 12) returns 19.56%, matching the monthly row of the effective-rate table in our effective annual rate guide. =NOMINAL(effective_rate, npery) goes the other way: =NOMINAL(0.1956, 12) returns approximately 18.00%. Use EFFECT whenever you compare two products with different compounding schedules.

RATE: what rate did I earn, or what do I need?

=RATE(nper, pmt, pv, fv) returns the rate per period, so multiply by the periods per year to get the nominal annual rate. If EGP 100,000 grew to EGP 170,913.95 in 36 months with no deposits, =RATE(36, 0, -100000, 170913.95)*12 returns 18.00%. For a yearly figure, =RATE(3, 0, -100000, 164303.2) returns 18.00%. Another way to get the compound annual growth rate without functions: =(end/start)^(1/years)-1. For EGP 100,000 growing to EGP 269,597.01 in 5 years, that gives 21.94%.

For a clearer comparison of the options, take a look at Compound Interest After Inflation in Egypt.

NPER: how long will it take?

=NPER(rate, pmt, pv, fv) returns the number of periods. To double EGP 100,000 at 1.5% a month: =NPER(0.015, 0, -100000, 200000) returns 46.56 months, which is about 3.88 years. To grow it five-fold: =NPER(0.015, 0, -100000, 500000) returns 108.10 months, which is 9.0 years. The Rule of 72 gives a quick check: 72 ÷ 19.56 (the effective rate) = 3.68 years, close to the exact 3.88.

PMT: how much do I need to save to reach a goal?

=PMT(rate, nper, pv, fv) returns the regular payment. To reach EGP 500,000 in 10 years at 15%, compounded monthly, starting from zero: =PMT(0.15/12, 120, 0, -500000). The result is 1,816.75, with a sign that depends on how you enter the future value, so check by feeding it back: =FV(0.15/12, 120, -1816.75, 0) returns about 500,000. This is the cleanest way to turn a savings target into a monthly number. If you already have a starting amount, put it in the pv argument as a negative number and the required payment falls.

Build a year-by-year table

A table shows the compounding effect better than a single number. Put this in a sheet:

CellFormulaWhat it does
A20Year 0
B2100000Start balance
A3=A2+1Next year
B3=FV(0.18/12, 12, 0, -B2)Balance after one more year of monthly compounding
C3=B3-B2Interest earned that year

This point is covered in more detail in How Compound Interest Works Against You on Loans and Credit Cards.

Copy row 3 down. For EGP 100,000 at 18% monthly, the interest in year 1 is 19,561.82 and in year 2 it is 23,388.46: the second year's interest is higher because it is calculated on a larger balance. That is compounding, visible in a column.

Add tax and inflation columns

If a tax percentage applies to interest, add a column for the after-tax balance: =B3-(B3-B2)*tax_rate, applied year by year, or at the end =start+(end-start)*(1-tax_rate). To express a future balance in today's money, divide by (1 + inflation) to the power of the years: =end/(1+inflation)^years. For EGP 269,597.01 after 5 years with 12.7% inflation, =269597.01/(1+0.127)^5 returns 148,284.41. The reasoning is in compound interest after inflation in Egypt. If you do not want to build the sheet, the compound interest tool on Royal Home Egypt does these steps with the numbers you enter (Arabic interface, EGP).

Google Sheets differences

  • The functions FV, PMT, RATE, NPER, EFFECT and NOMINAL are available and use the same argument order.
  • Google Sheets may use a comma or a semicolon as the argument separator, depending on your locale settings. If a formula returns a parse error, try the other one.
  • Percent formatting behaves the same way: 18% is stored as 0.18.
  • For a quick check without functions, the plain formula =B1*(1+B2/B3)^(B3*B4) works identically.

The mistakes that produce wrong answers

  • Using the annual rate with monthly periods. If nper is in months, rate must be the monthly rate: annual divided by 12.
  • Wrong sign. If pmt and pv have the same sign, the result is nonsense or an error. Money paid in is negative.
  • Typing 18 instead of 18%. Always check that the cell is formatted as a percentage.
  • Forgetting type. Start-of-period deposits need the type argument set to 1.
  • Treating the result of RATE as annual. RATE returns the per-period rate, so multiply by periods per year.
  • Using a nominal rate where an effective rate is expected. Convert with EFFECT first.

To keep going on this topic, continue with New Zayed Rent and Yield.

A quick reference table

QuestionFormulaCheck result
Final balance, monthly=FV(0.18/12,36,0,-100000)170,913.95
Final balance, annual=FV(0.18,3,0,-100000)164,303.20
With deposits (end)=FV(0.15/12,60,-2000,-50000)282,508.08
With deposits (start)=FV(0.15/12,60,-2000,-50000,1)284,722.45
Effective annual rate=EFFECT(0.18,12)19.56%
Rate earned=RATE(36,0,-100000,170913.95)*1218.00%
Months to double=NPER(0.015,0,-100000,200000)46.56
Monthly saving for goal=PMT(0.15/12,120,0,-500000)1,816.75 (sign depends on entry)

Use this table to test your own sheet: if your numbers match the check column, your setup is right.

Frequently asked questions

What is the compound interest formula in Excel?

=P*(1+r/n)^(n*t), or =FV(r/n, n*t, 0, -P).

Does the FV function include interest on deposits?

Yes, each deposit compounds from the period it is made until the end.

Why does my FV result come out negative?

Because you entered pv or pmt as positive. Excel treats money you pay in as negative and returns the opposite sign. Use a leading minus on the result or on the inputs.

For a fuller explanation of this point, read Compound Interest After Inflation in Egypt.

How do I calculate compound interest daily?

Use n = 365: =FV(0.18/365, 365*3, 0, -100000), which returns about 171,577.85. The rate per day is the annual rate divided by 365.

Can I use these formulas for a loan?

Yes, the same functions work for a balance that grows against you. See compound interest on loans and cards.

Build a reusable calculator sheet

Rather than typing numbers into formulas, keep the inputs in labelled cells so you can change them. A layout that works in both Excel and Google Sheets:

CellLabelExample value
B1Start amount100000
B2Annual nominal rate18%
B3Periods per year12
B4Monthly deposit0
B5Tax on interest0%
B6Inflation12.7%

If you want to go deeper on this, see Effective Annual Rate (APY) Explained.

Then use =FV(B2/B3, B3*years, -B4, -B1) for the balance after any number of years. Put the number of years in column A of a table and the formula in column B, so each row shows the balance at that year. Here is what a table for EGP 100,000 at 18% compounded monthly looks like when you fill it with =FV(0.18/12, 12*A3, 0, -100000):

YearsBalance
1119,561.82
3170,913.95
5244,321.98
10596,932.29

Notice how the ten-year balance is almost six times the deposit, while three years gives 1.7 times. The table is the quickest way to see that compounding accelerates.

Three tests that catch most errors

  1. The zero-rate test. Set the rate to 0. =FV(0, 36, -2000, -50000) must return 122,000, which is the start amount plus 36 deposits of 2,000. If it does not, a sign or period is wrong.
  2. The one-period test. For one year of annual compounding, =FV(0.18, 1, 0, -100000) must return 118,000.
  3. The consistency test. The balance after one year from FV should equal the start amount times (1 + EFFECT(rate, n)). For 18% monthly, 100,000 × 1.1956 = 119,561.82, matching the first row of the table.

If all three pass, your setup is almost certainly right. If one fails, check the sign of each argument, whether the rate is per period, and whether nper is in the same unit as the rate.

It also helps to read New Zayed Payment Plans Compared before you decide.

Using Goal Seek in Excel

Excel's Goal Seek (Data, What-If Analysis, Goal Seek) finds an input that produces a target result. Suppose a cell shows the balance after 10 years and you want it to reach EGP 500,000. Tell Goal Seek to set that cell to 500000 by changing the monthly deposit cell, and it will search for the deposit. For this problem the PMT function gives the answer directly (EGP 1,816.75 at 15% compounded monthly from zero), and Goal Seek is the better choice when your model has several steps, for example tax or an annual increase in the deposit, that PMT cannot express in one formula.

Adding an annual increase to the deposit

A single FV call assumes a constant deposit. If you plan to raise your deposit each year, build a year-by-year table: in each row, the opening balance, the deposit for that year, the interest for the year, and the closing balance. For monthly deposits within a year, you can use =FV(rate/12, 12, -deposit, -opening_balance) to get the closing balance directly, and then carry it to the next row as the opening balance. This keeps the logic visible, and you can change the deposit in each row to see the effect of a raise.

A worked mini-case: comparing two offers in a sheet

Suppose you are comparing two hypothetical products for EGP 100,000 over 3 years. Product X pays a nominal 18% compounded quarterly; Product Y pays a nominal 17.8% compounded monthly. In one row each, enter =FV(0.18/4, 12, 0, -100000) and =FV(0.178/12, 36, 0, -100000). The first returns 169,588.14 and the second returns about 169,906.52. Product Y ends higher by roughly EGP 318, even though its headline rate is lower, because it compounds more often. Add a column with =EFFECT(0.18, 4) and =EFFECT(0.178, 12) (19.25% and 19.33%) and you have a clean comparison. Repeat the same layout for any pair of products, and add your tax and fee assumptions as extra columns.

Formatting and sharing tips

  • Format result cells as numbers with two decimals, and label the currency in the header rather than inside every cell, so formulas stay clean.
  • Format rate cells as percentages, and type the value with the percent sign (18%) so the stored value is 0.18.
  • Round only for display. Avoid ROUND inside the formulas that feed other cells, because rounding early can shift later results by small amounts.
  • Colour the input cells differently from the formula cells, and lock the formula cells if you share the sheet.
  • Write the date and the source of any rate or inflation figure next to it, so the sheet does not quietly become out of date.

For a clearer comparison of the options, take a look at Do Egyptian Bank Certificates Compound.

Property examples to model in your sheet

The functions in this guide work for property cash flows too. Try them on the instalment schedules described in Village West Payment Plans: How to Read an Offer, or model a rental yield from الإيجار في ستون ريزيدنس: كيف تحسب العائد الحقيقي بأرقامك أنت (Arabic). If you prefer a ready-made version, the buyer and seller toolkit has similar logic built in (Arabic interface).

Home Dimension

حوّل بيتك لتحفة معمارية

تشطيبات فاخرة بإشراف هندسي كامل

  • خبرة
  • التزام
  • جودة
Home Dimension – Luxury Finishing & Interior Design

طلب معاينة مجانية

املأ البيانات وسنتواصل معك خلال 24 ساعة