How Do I Calculate Compound Interest Using Excel?

What Is Compound Interest

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

Compound interest is a tremendous advantage for savers and investors. For borrowers, not so much. That’s because savers and investors benefit from the powerful growth in the value of their financial accounts that compounding interest provides over time.

For borrowers, that compounding interest and growth in balance benefits the lender and means having to pay more to get out of debt.

Read on to learn more about compound interest and how to calculate it using Excel.

Key Takeaways

• Compound interest is calculated on the amounts of principal (or initial deposit) and interest in your account.
• As interest increases your account value, subsequent compound interest calculations apply to larger amounts.
• Compound interest can be an advantage if you’re saving money or a disadvantage if you’re borrowing it.
• Excel can simplify your compound interest calculations.

Understanding Compound Interest

Compound Interest Works In Your Favor

Let’s say that you have an account with a deposit of $100 that earns a 10% annual compounded interest rate. That $100 grows to $110 after the first year:

$100 x .10 = $10

$100 + $10 = $110 new balance

The account value then grows to $121 when interest is calculated after the second year. 

$110 x .10 = $11

$110 + $11 = $121 new balance

The reason the second year’s gain is $11 instead of $10 is because the 10% rate was applied to a larger account balance.

As you can see, the 10% interest applied to $100 created the new balance of $110. After year two, the 10% was applied to that new balance of $110, for a third new balance $121. At the end of the third year, the 10% will be applied to $121 and a larger new balance will be the result.

Because compound interest is working for you by increasing the value of your investment, you’ll want to keep your money invested for as long as possible.

Compound Interest Works Against You

Let’s say that you borrowed $100 (the principal amount) at a compound interest rate of 10% that’s applied annually. Using the same calculation above, after one year you’ll have $100 in principal and $10 in interest, for a total amount owed by you of $110. 

$100 x .10 = $10

$100 + $10 = $110 new balance

In year two, the 10% interest rate is applied to both the principal of $100, resulting in $10 of interest, and the accumulated interest of $10, resulting in $1 of interest. This results in a total of $11 in interest gained that year, which is $21 for both years ($10 after year one + $11 after year two).

$100 x .10 = $10

$10 x .10 = $1

$10 + $1 = $11 total new interest

$110 + $11 = $121 new balance 

Because compound interest is working against you by increasing the amount you must pay back to the lender, you’ll want to pay off your debt as soon as possible.

Formula for Compound Interest

The compound interest formula is similar to the Compounded Annual Growth Rate (CAGR). For CAGR, you are computing a rate that links the return over a number of periods. For compound interest, you most likely know the rate already and 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
• 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+(NCPPYinterest rate​))(years × NCPPY) = Future Valuewhere:NCPPY=number of compounding periods per year​

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 three 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. All three ways are detailed below:

Does Interest Always Compound Annually?

No, it can compound at other intervals including monthly, quarterly, and semi-annually. Some investment accounts, such as money market accounts, compound interest daily and report it monthly. The more frequent the interest calculation, the greater amount of money that results.

Why Does Compound Interest Matter?

It matters because it can increase financial values for account balances more quickly than simple interest. These increasing balances may be great for savers and investors who want to see their money grow. But for borrowers, they can be a source of worry and financial hardship if they aren’t able to pay them down or completely off as quickly as they can.

Who Sets the Compound Interest?

That can depend. A financial institution will set the rate and interval for different accounts that it offers savers, investors, and borrowers. If you’re lending money to a friend for a business opportunity, you might set a compound interest rate that will be charged annually (or more frequently) until the loan is repaid.

The Bottom Line

Excel can be a helpful and powerful partner when you need to calculate compound interest amounts for different purposes, such as loans and investments. It’s especially convenient when frequent intervals are involved over multiple years and accuracy counts.