There are two ways you can calculate compound interest in Excel. You can use the compound interest formula, or you can use the built-in Excel financial functions that let you calculate compound interest with ease.
In this article, we’ll walk you through both these methods, and by the end, you’ll be calculating compound interests like a pro.
How Does Compound Interest Work?
Suppose you invested $1000 with a 5% interest rate that will compound every year. In this case, you will earn $50 (5% of 1000) after one year, making your gross amount $1050. In the following year, the interest will apply to the gross amount, i.e., 5% of 1050. This will make your gross amount $1102.5.
Similarly, the interest will apply to the preceding year’s gross amount in the next year, i.e., 5% of 1102.5. As this continues, your initial investment (principal) increases exponentially yearly.
Compound interest differs from simple interest because in the former, the interest applies to the gross amount, while in the latter, it only applies to the principal. Compound interest has exponential increments solely because of this difference.
Calculate Compound Interest Using the Formula in Excel
Now that we’ve understood how compound interest works let’s learn how to calculate compound interest in Excel using the compound interest formula.
The compound interest formula is:
Here:
P is the principal or the initial investment. P’ is the gross amount (after the interest is applied). R is the interest rate. N is the number of times compounding occurs per year. T is the total time (in years) in which compound interest is applied.
Now that we’ve got the compound interest formula, let’s use it in Excel to calculate our compound interest for five years (from the previous example):
Create a table in Excel containing the principal value, interest rate, and other metrics required in the formula. Write the compound interest formula in cell B5 (starting with an equal sign. ) Here the principal is given in cell B1, the interest rate in cell B2, the compounding period (per year) in cell B3, and the total number of years in cell B4. So, your compound interest formula should look something like this: Press enter, and you’ll get the gross amount, more commonly referred to as the future value, you’ll get after five years.
You’ve essentially created a compound interest calculator. Now, it’s simply a matter of altering the values to calculate your compound interest.
Calculate Compound Interest Using the FV Function
Another method to calculate compound interest in Excel is using the FV function. The syntax of the FV function is as follows:
Here,
Rate is your compound interest rate Nper or number of periods is the number of periods (years) for which you want to create the future value (gross amount) Pmt or payment is the additional payment made each period. This value needs to be constant throughout the period. If no other amount is paid, this value will be 0. pv or principal is your initial investment [type] or end_or_beginning is used to specify whether you want to calculate the gross amount at the end of the period or the beginning. (Enter 0 if you want the gross amount at the end of the period and 1 if you want it at the start of the period. )
Now that we understand the FV function let’s use it to calculate the compound interest of our initial example.
In an Excel cell, type =FV and input the required values between the parentheses. For rate, we enter 5%, which was the interest rate in our initial example. (Make sure to type the interest rate as a percentage, i. e. , 5% or in decimal form, i. e. , 0. 05. ) We’re calculating compound interest for five years, so the value of nper is 5. There is no additional payment in our example, so we’ll enter 0. Type 1000 in pv because that is our initial investment or principal. Type 0 in [TYPE] because we want to calculate our gross amount (future value) at the end of the period. Close the parentheses and press enter, and you’ll get your compound interest for five years.
Note: Don’t forget to place commas between the values.
Calculate Compound Interest for an Intra-Year Period
Notice how we’ve only calculated annual compound interest until now. But often, we want to calculate quarterly, monthly, or even daily compound interests. It’s time to understand how to calculate compound interest for an intra-year period.
We can calculate monthly, weekly, or daily compound interest like annual compound interest using the compound interest formula and an Excel function. Let’s try the compound interest formula first:
Calculate Intra-Year Compound Interest Using the Formula
We already know that the formula for compound interest is given as follows:
Here N denotes the number of times compounding occurs in a period (year.) In our initial example, we put N=1 because compounding happens only once per year. If we want to calculate the monthly compound interest for our initial example, we’ll put N=12 (since there are 12 months in a year.) Similarly, for the daily compound interest, we’ll put N=365.
The monthly compound interest of our initial example is calculated as follows:
Calculating Intra-Year Compound Interest Using the EFFECT Function
Excel has an EFFECT function which is one of the top financial functions in Excel. You can use it to calculate the compound interest for an intra-year period. The syntax of the effect function is as follows:
Here,
P is the principal R is the interest rate N is the number of times compounding occurs per year T is the total time (in years) in which compound interest is applied
If we take our initial example, we can calculate the monthly compound interest using the EFFECT function as follows:
Our principal is $1000, so we enter P=1000 The interest rate is 5%, so R=5% Since we’re calculating monthly compound interest, N=12 (for semi-annual compound interest, N will be 2, for daily compound interest, N will be 365, etc. ) We need to calculate the monthly compound interest for five years; that’s why T=5. Putting in the values, we get:
Learning to Find Compound Interest With Ease
By now, you have a decent understanding of compound interest. All you need now is a little practice, and you’ll be able to calculate annual or intra-year compound interests using the compound interest formula or the Excel functions.
But why stop there? There are a ton of Excel functions that you can learn to perform tricky calculations and analyze complex data quickly.
