Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate compound interest in Excel and Google Sheets
Topic:Finance basicsExcel & Google Sheets
=ROUND(A2*(1+B2/C2)^(C2*D2),2)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Principal | Nominal annual rate | Periods per year | Years |
| 1000 | 0.12 | 12 | 1 |
=ROUND(A2*(1+B2/C2)^(C2*D2),2)→1126.83
Both expressions are checked against an independently calculated expected result in HyperFormula. This is a sample execution check, not native Excel or Google Sheets certification.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter principal, nominal annual rate, compounding periods per year and years in A2:D2.
- 2Enter 0.12 or 12% for a 12% rate, not the number 12. Use a positive whole-number compounding frequency.
- 3Place the formula outside the input row. It applies 1% per month for 12 months and rounds the final balance to two decimal places.
- 4Check independently: 1000 × 1.01^12 is approximately 1126.82503. Interest earned is the closing balance minus principal, or 126.83 after rounding.
Tips
- This example assumes no deposits, withdrawals, fees or taxes and a constant nominal rate; it is a worked calculation, not a promised return.
- Do not divide an already effective annual rate by 12. Match the rate definition and period convention to the product, and avoid rounding after each period unless its rules require that.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Is the displayed result interest or total balance?
It is the total closing balance. Subtract the original principal to obtain interest earned.
What causes a division error?
The periods-per-year value must be positive. A blank or zero frequency is invalid input, not a zero return.
More on Finance basics
See all →How-to
How to calculate the effective annual interest rate
Convert a positive nominal annual rate and a whole-number compounding frequency into an effective annual rate with EFFECT.
How-toHow to calculate the break-even point in units
Divide fixed costs by unit contribution margin and round up to whole units, with an explicit message when the margin is nonpositive.
How-toHow to calculate tiered commission
Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.
How-toHow to calculate net pay
Calculate take-home (net) pay by subtracting every deduction from gross salary, with worked examples for fixed amounts, percentage tax, and slab-based tax. Excel and Google Sheets.
How-toHow to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.
How-toHow to calculate cost per unit
Divide total cost by total units to find cost per unit using a simple formula in Excel or Google Sheets.
Sources and compatibility
Reviewed 2026-09-16