A:

What is Compound Interest?

Compound interest, also known as compounded 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 $100 represents the principal of a loan, which carries a compounded interest rate of 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. The second year's increase is $11, instead of $10, because the interest is compounding – that is, it's being applied to a larger base ($110 compared to $100, our starting point). Each year, the base increases by 10%: $110 after the first year, then $121 after the second year.

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 single 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 break out the calculations line by line. Conversely, you could calculate the whole equation in one cell to arrive at just the final value figure. We recommend the first approach, but both are detailed below.

In the example below, you can input the data in yellow, and choose the compounding period.  

RELATED FAQS
  1. What formula calculates interest on interest?

    Find out more about compounding interest, what it measures, and how to calculate the amount of compound interest accrued ... 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 difference between continuous compounding and discrete compounding?

    Learn to differentiate between and calculate the continuous and discrete compounding formulas for interest-generating investments ... Read Answer >>
  4. What is the difference between compounding interest and simple interest?

    Simple interest is only based on the principal amount of a loan, while compound interest is based on the principal amount ... Read Answer >>
  5. What is the difference between stated annual return and effective annual return?

    Essentially, the effective annual return accounts for intra-year compounding, and the stated annual return does not. The ... Read Answer >>
Related Articles
  1. Investing

    Continuous Compound Interest

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

    Overcoming Compounding's Dark Side

    Understanding how money is made and lost over time can help you improve your returns.
  3. 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.
  4. 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.
  5. Investing

    The Interest Rates: APR, APY And EAR

    When most people shop for financial products, all they focus on is the listed interest rate. Human eyes instinctively dismiss the fine print, which usually includes the terms APR (annual percentage ...
  6. Financial Advisor

    The Awesome Power of Compounding

    The power of compounding. It may not be sexy, but it is the surest way to accumulate wealth over time.
  7. Investing

    How to Make the Time Value of Money Work for You

    How to make the time value of money and power of compounding work for you. Here's a hint: Start saving now.
  8. Investing

    Investing $100 a Month in Stocks for 30 Years

    Find out how you could potentially earn hundreds of thousands of dollars by just investing $100 a month in stocks during your working years.
  9. Investing

    Why Millennials Have an Investing Edge

    Millennials, here's why you shouldn't wait to start saving for your retirement.
  10. Personal Finance

    5 Financial Math Skills Every Teen Should Learn

    Calculus is good for abstract thinking, but what math skills should every student learn for managing his or her money?
RELATED TERMS
  1. Compound Interest

    Compound Interest is interest calculated on the initial principal ...
  2. Discrete Compounding

    Discrete compounding refers to the method by which interest is ...
  3. Periodic Interest Rate

    The periodic interest rate is the interest rate charged on a ...
  4. Compound Return

    The compound return is the rate of return that represents the ...
  5. Compounding

    The ability of an asset to generate earnings, which are then ...
  6. Biotech Compound

    A chemical entity that forms the starting point in the drug development ...
Hot Definitions
  1. Swap

    A swap is a derivative contract through which two parties exchange financial instruments, such as interest rates, commodities, ...
  2. Yield Curve

    A yield curve is a line that plots the interest rates, at a set point in time, of bonds having equal credit quality, but ...
  3. Gross Profit

    Gross profit is the profit a company makes after deducting the costs of making and selling its products, or the costs of ...
  4. Risk Tolerance

    The degree of variability in investment returns that an individual is willing to withstand. Risk tolerance is an important ...
  5. Donchian Channels

    A moving average indicator developed by Richard Donchian. It plots the highest high and lowest low over the last period time ...
  6. Consumer Price Index - CPI

    A measure that examines the weighted average of prices of a basket of consumer goods and services, such as transportation, ...
Trading Center