Reviewed by Ebony Howard
Fact checked by Michael Logan
Key Takeaways
-
Compound interest grows by earning interest on both principal and accumulated interest.
-
The compound interest formula calculates the future value based on the initial principal, interest rate, and compounding periods.
-
In Excel, use a table to input data and break out calculations for clarity and auditing.
-
Compounding frequency affects the formula, with more frequent compounding leading to greater interest accrual.
-
Best practices for financial modeling include transparency and minimizing errors in calculations.
Compound interest is interest that is calculated on the initial principal of a deposit or loan , and on all previously accumulated interest . When compound interest is added to an investment's or loan's initial principal, it increases the balance and, in turn, the amount of interest earned or paid in the next period.
Calculating compound interest is complex. You can use Microsoft Excel to simplify calculations like the compounding interest formula.
This article will provide a step-by-step method for using Excel to calculate compound interest on deposits or loans. We will cover key considerations like selecting the appropriate compounding period and using precise formulas.
Note
Compound interest is also known as compounded interest.
Example of Compound Interest
For example, let's take a $100 loan which carries a 10% compounded interest. After one year, you have $100 in principal and $10 in interest, for a total base of $110.
In year two, the 10% interest rate is applied to the $100 principal, resulting in $10 of interest. It's also applied to the accumulated interest of $10, resulting in $1 of additional 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 . It's being applied to a base that has grown to $110 compared to our starting point of $100. Each year, the base increases by 10%: $110 after the first year, then $121 after the second year.
Understanding the Compound Interest Formula
The formula for compound interest is similar to the one for the compound annual growth rate (CAGR) . For the CAGR, you compute a rate that links the return over a number of periods. For compound interest, you know the rate already. You are simply calculating what the future value of the return might be.
To reach the formula for compound interest, you algebraically rearrange the formula for the 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.
Important
Many of us just need a calculator to compute simple interest. You merely multiply the daily interest rate by the principal by the number of days that elapse between payments. But calculations are trickier when it comes to interest that compounds—i.e., interest that accrues on principal and the accumulated interest as well. An Excel spreadsheet can take care of this work for you, provided you set up the formula accurately.
How to Calculate 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.
Here is a four-step process to set this up in Excel so as to minimize that problem:
-
Gather your initial data, such as principal amount, annual interest rate, and the number of years for the loan.
-
Open Excel and input this data into individual cells, making the principal, interest rate, and years easy to reference.
-
In another cell, use the compound interest formula: =Principal (1 + (Interest Rate / Compounding Periods)) ^ (Years * Compounding Periods) , where you replace Principal, Interest Rate, Compounding Periods, and Years with the respective cell references.
-
Adjust the formula based on your chosen compounding period (e.g., quarterly, monthly) by modifying the compounding periods accordingly in your formula.
In the example below, you can input the data in yellow and choose the compounding period.
The Bottom Line
Compound interest is interest calculated on the initial principal and on all accumulated interest from previous periods on a deposit or loan.
Here's an example: A $100 loan with a 10% compounded interest will yield more interest in the second year than in the first because the interest is applied to the increased base of principal plus accumulated interest.
The formula for compound interest requires the starting principal, interest rate, and compounding frequency to determine the future value.
To calculate compound interest in Excel, it is recommended to break down the calculations line by line. This makes the process more transparent and easier to audit, as opposed to condensing everything into a single formula.
Read the original article on Investopedia
