Compound interest is interest that's calculated both on the initial principal of a deposit or loan, and on all previously accumulated interest.

For example, let's say you have a deposit of $100 that earns a 10% compounded interest rate. The  $100 grows into $110 after the first year, then $121 after the second year.  Each year the base increases by 10%.  The reason the second year's gain is $11 instead of $10 is as a result of the same rate (10% in this example) being applied to a larger base ($110 compared to $100, our starting point).  

Or let's say, $100 is the principal of a loan, and the compound interest rate is 10%.  After one year you have $100 in principal and $10 in interest, for a total base of $110.  In year two, the interest rate (10%) is applied to the principal ($100, resulting in $10 of interest) and the accumulated interest ($10, resulting in $1 of interest), for a total of $11 in interest gained that year, and $21 for both years. (Want to improve your excel skills? Visit Investopedia Academy to view our excel for finance courses.)   

What Is The Formula for Compound Interest?

It's similar to the Compounded Annual Growth Rate (CAGR). For CAGR, you are computing a rate which links the return over a number of periods.  For compound interest, you most likely know the rate already; you are just calculating what the future value of the return might be.   

For the formula for compound interest, just algebraically rearrange the formula for CAGR. You need the beginning value, interest rate, and number of periods in years.  The interest rate and number of periods need to be expressed in annual terms, since the length is presumed to be in years. From there you can solve for the future value. The equation reads:

Beginning Value * (1 + (interest rate/number of compounding periods per year))^(years * number of compounding periods per year) = Future Value

This formula looks more complex than it really is, because of the requirement to express it in annual terms.  Keep in mind, if it's an annual rate, then the number of compounding periods per year is one, which means you're dividing the interest rate by one and multiplying the years by one. If compounding occurs quarterly, you would divide the rate by four, and multiply the years by four. 

Calculating Compound Interest in Excel

Financial modeling best practices require calculations to be transparent and easily auditable. The trouble with piling all of the calculations into a formula is that you can't easily see what numbers go where, or what numbers are user inputs or hard-coded.  

There are two ways to set this up in Excel. The most easy to audit and understand is to have all the data in one table, then breakout the calculations line by line. Conversely, you could calculate the whole equation in one cell to arrive at just the final value figure. Both are detailed below:

  1. How do I use the rule of 72 to calculate continuous compounding?

    Find out why the rule of 72 does not accurately reflect the growth caused by continuous compounding, and which number can ... Read Answer >>
  2. How can I tell if a loan uses simple or compound interest?

    Learn the differences between simple and compound interest and how you can identify which one a loan uses. Read Answer >>
  3. What is the formula for calculating compound annual growth rate (CAGR) in Excel?

    Relatively straightforward, the concept of CAGR requires only three primary inputs: an investment's beginning value, its ... Read Answer >>
  4. What does it mean when interest 'accrues daily?'

    Find out what it means when an interest-bearing account accrues interest daily and how different compounding periods change ... Read Answer >>
Related Articles
  1. Investing

    Learn Simple and Compound Interest

    Interest is defined as the cost of borrowing money, and depending on how it is calculated, it can be classified as simple interest or compound interest.
  2. Investing

    Continuous Compound Interest

    Different frequency in compound interest results in different returns. Check out how continuous compounding accelerates your return.
  3. Investing

    Overcoming Compounding's Dark Side

    Understanding how money is made and lost over time can help you improve your returns.
  4. Personal Finance

    APR and APY: Why Your Bank Hopes You Can't Tell The Difference

    Do you know the difference between Annual Percentage Rate and Annual Percentage Yield? Check out how they can affect your own account balance.
  5. Investing

    The Effective Annual Interest Rate

    The effective annual interest rate is a way of restating the annual interest rate so that it takes into account the effects of compounding.
  6. Investing

    4 Ways Simple Interest Is Used In Real Life

    Simple interest works in your favor when you're a borrower, but against you when you're an investor.
  7. Retirement

    Compounding Is Important in the Later Years Too

    The power of compounding is even greater in the later years of saving for retirement.
  8. Personal Finance

    Schedule Loan Repayments with Excel Formulas

    Calculate all the particulars of a loan using Excel, and set up a schedule of repayment for a mortgage or any other loan.
  9. Investing

    Investing $100 a Month in Stocks for 30 Years

    Find out how you could potentially earn hundreds of thousands of dollars just by investing $100 a month in stocks during your working years.
  1. Effective Annual Interest Rate

    Effective Annual Interest Rate is an investment's annual rate ...
  2. Compounding

    The ability of an asset to generate earnings, which are then ...
  3. Interest Rate

    Interest rate is the amount charged, expressed as a percentage ...
  4. Stated Annual Interest Rate

    The return on an investment that is expressed as a per-year percentage, ...
  5. Compound Annual Growth Rate - CAGR

    The Compound Annual Growth Rate (CAGR) is the mean annual growth ...
  6. Future Value - FV

    Future value (FV) is the value of a current asset at a specified ...
Hot Definitions
  1. Call Option

    An agreement that gives an investor the right (but not the obligation) to buy a stock, bond, commodity, or other instrument ...
  2. Standard Deviation

    A measure of the dispersion of a set of data from its mean, calculated as the square root of the variance. The more spread ...
  3. Entrepreneur

    An entrepreneur is an individual who founds and runs a small business and assumes all the risk and reward of the venture.
  4. Money Market

    The money market is a segment of the financial market in which financial instruments with high liquidity and very short maturities ...
  5. Perfect Competition

    Pure or perfect competition is a theoretical market structure in which a number of criteria such as perfect information and ...
  6. Compound Interest

    Compound Interest is interest calculated on the initial principal and also on the accumulated interest of previous periods ...
Trading Center