How to Calculate Compound Interest in Excel (2 Easy Ways)

Today we’re trying to build your interest in compound interest. Interest can be an income on investment or an expense or borrowing. But compound interest functions in an intriguing way that may be beneficial for the investor and quite expensive for the borrower. As you may have gathered from the terminology, compound interest differs from simple interest in concept and calculation.

We will show how to apply a straightforward, surefire mathematical formula to compute the future value of a principal sum. Using that formula, will expand on yearly, quarterly, monthly, weekly, and daily compounding periods. It might sound like a tall order but trust us, there’s only a change of one value in the formula and it magically applies to all compounding periods.

Other than that, you can also use the FV function to calculate the very same future value. And that is it! Just two simple, no-nonsense, super compounding methods. But first, are you familiar with compound interest? If not, read the section ahead before diving into the formulas.

Compound Interest Vs Simple Interest

Simple Interest

Simple interest is the interest we all know. A flat rate is applied to the principal balance and that fixed amount of interest applies every year. This interest amount can be pro-rated according to the period in the calculation. Have a look at this super bare-bones example below:

If the principal amount invested is $5,000, at 10% as the interest rate, the yearly interest earned would be $500. In 5 years, that amount would be (500 x 5) $2,500, and the final amount with the interest earned would amount to $7,500. That is simply-put simple interest as we know it and is only earned on the initial investment.

Compound Interest

Now for compound interest, the interest rate is reapplied to the balance at the end of the period. For easiness and comparison's sake, let's consider this period to be a year and look at the example above again.

In the second year, instead of paying out the interest amount of the first year, it is added to the principal sum. Therefore, in the second year, the interest rate will be applied to the compounded amount; the principal amount, and the interest earned from last year ($5,000 + $500 = $5,500). In the third year, the rate will be applied to the amount from the second year and added to the interest for the third year ($5,500 + $550 = $6,050).

In this way, the interest compounds so that you are not just earning interest on the initial investment, but also earning interest on the interest amount itself.

From an investment point of view, compound interest can yield sizeable returns, especially on frequent compounding periods. As for borrowing, if it is the only option, compound interest will come down to a greater payout as the resulting interest keeps escalating.

Method #1 – Using Mathematical Compound Interest Formula

What we have demonstrated above, is the most layman way of calculating compound interest and future values. While it may be easier to understand that way, once you grab the concept, you can move on to applying a simple formula instead of a tabular layout. Now we will go into calculating future values with compound interest according to a simple formula.

Syntax

The syntax is as follows:

Arguments

'F' – Final amount including the initial amount and compound interest
'P' – Initial principal sum
'r' – Rate of interest
'n' – Number of times interest is compounded in a year
't' – Number of years

This is the basic formula for calculating the final sum with compound interest. Using this formula, we will show you how to perform the calculations for:

• yearly compounding,
• quarterly compounding,
• monthly and weekly compounding,
• daily compounding.

 Let’s get compounding! 

Yearly Compounding

For yearly compounding, the number of times the interest is compounded in a year is 1. We will carry forward the example used earlier to show you how the compound interest formula works. Let’s see the formula below:

Following the syntax, the interest rate is added to the number 1. Since this is a yearly calculation, the number of times the interest is compounded in a year is 1. Divided by 1, the interest rate, is added to 1. 1+10% equals 110%.

This is raised to the power 5 as the number of years. Since the number of compounding periods is 1, 5 is multiplied by 1. 110% will be raised to the power 5 resulting in 161%.

Finally, 161% multiplied by 5000 gives us the final amount of $8,053. This is the final value after 5 years, using compound interest on an initial sum of $5,000 at an interest rate of 10%. This is also in line with our layman's method of calculating compound interest.

Note: Do not be confused that you cannot see a couple of the 1’s in the formula we were talking about just now. For simplifying the calculation, we have removed them. If you are a syntax junkie though, you might feel more comfortable seeing the non-simplified formula:

This will give you a better sense of familiarity throughout with the rest of the compounding periods. Keep reading.

Quarterly Compounding

Now let’s move on to quarterly compounding. In quarterly compounding, the interest is compounded every 3 months, which makes the number of times the interest is compounded 4 times in a year. There will only be a slight change in the formula since only the compounding times are changing. With the formula ahead, you can calculate the final value with the interest compounded quarterly:

Let’s talk about the interest rate first. Sticking to the syntax, the interest rate of 10% is divided by 4 since the interest is compounded 4 times a year for quarterly compounding. This splits the interest for the year into 4 periods. 10% divided by 4 gives us 0.025. With 1 added, we’re at 1.025.

1.025 will be raised to the power 5*4. This is because 5 is the number of years and multiplying it by 4 accounts for the times the interest will be compounded in one year. 1.025 raised to the power 20 lands at 1.64. Multiplying 1.64 by the principal balance gives us the final balance of $8,193.

Note how the final amount calculated for quarterly compounding is slightly greater than yearly compounding. The more frequent the compounding period, the more the final amount will be.

Monthly Compounding

Next, we have interest compounded on a monthly basis. This makes 12 compounding periods in a year. You will see ahead that the final value will be even more than quarterly compounded interest. Without much fiddle in the calculation, we can proceed to the formula since it only requires the number of times of interest compounded to change.

Dividing the interest rate by 12 incorporates the number of times the interest will be compounded in a year by spreading the interest out for the 12 months. Then we add 1 to arrive at 1.01. This figure is to be powered by (5*12) because 12 into 5 is the number of times the interest will be compounded in the duration of 5 years. So far, the calculation brings 1.64. 1.64 into the initial balance makes the final balance of $8,227.

At $8,227, the amount increases with the decreasing compounding periods and increasing number of compounding periods.

Note: For weekly compounding, the number of times the interest is compounded in a year becomes 52. To incorporate 52 in the formula, we’d have to use:

Daily Compounding

It doesn’t get any more frequent than daily compounding so for the final touch, we’ll tap into the daily aspect too. For interest compounded daily, the frequency of compounding in a year is 365. Now let’s see how it sits in the formula:

Daily compounding requires the interest to be compounded 365 times in a year and so the interest rate is divided into 365 days. As per the formula, the adjusted interest rate has 1 added to it which reaches 1.00. Now the number of years needs to be adjusted with the same 365 and then 1.00 will be calculated at the power 5*365 which equals 1.65. 1.65 multiplied by $5,000 makes the final sum $8,243.

Now with all types of compounding periods covered, it’s clear that daily compounded interest has the highest yield on the principal balance.

Method #2 – Using FV Function

Until now, what you've seen was a formula that was in arithmetic agreement and that helped us calculate the future value with compound interest. The more Excel-ish way to do this would be to use a function for the job and we have the FV function at our service.

The FV function computes the future value of an initial amount taking into account constant payments and interest rate. Sounds like something we can work with. Let’s see how compound interest calculations go with the FV function.

If we take yearly compounding, the formula would end up rather simple as we’ve seen earlier. Just to give you an understanding of how the number of times of compounding would fit here, we’ll take monthly compounding as an example. Here is the formula:

Looks familiar, doesn’t it? The first parameter for the computation of the future value is the interest rate. Our interest rate is divided by 12 for yearly compounding.

The second parameter is the total number of compounding periods. Over 5 years, the total number would be 5*12.

The third parameter is the payment made in each period. We have left out this value and therefore have to enter the next parameter which would otherwise have been optional.

The fourth parameter is the present value of the sum that the future value is being calculated for. We have put 5000 as a negative value in the formula.

Any value that is an outflow should be put as negative in the formula and all inflows should be entered as positive. E.g. for lending or deposits, the initial sum would be an outflow and entered as negative. The result will be returned as positive as the sum earned.

The future value of the investment of $5,000 in 5 years at the rate of 10% which will be compounded monthly is $8,227.

The future value calculated by the FV function is totally on point with the mathematical formula as both have produced the same outcome of $8,227.

Interesting, right? With these easy-to-grip formulas, you won’t even need an external compound interest calculator. By now you must have compound interest, present and future values buzzing in your head. Before all that buzzing cools down, we’ll be ready with more buzzing bees for you to catch up with! Ready, set, bzzzzz!