Compound Interest Formula Excel (Complete Guide)

Compound interest is the interest determined on the initial principal, which comprises all of the accumulated interest of previous periods of a deposit or loan. We will explain compound interest formula excel sheet with some of the examples. It is easy to use the compound interest formula by yourself and calculate interest.

Compound interest is computed by multiplying the initial principal value by one plus the annual interest rate raised to the number of compound periods minus one. The total initial amount of the loan is then deducted from the resulting value. (Excel Sheet Attached Below!)

The formula for calculating compound interest is:
Compound Interest = Total amount of Principal and Interest in future (or Future Value) less Principal amount at present (or Present Value)

= [P (1 + i)n] – P

and taking P common,

= P [(1 + i)n – 1]

(Where P = Principal, i = nominal annual interest rate in percentage terms, and n = number of compounding periods.)

Read this too: The Best Business Phone Services for Your Business (Latest)

EXAMPLE 1: Take a three-year loan of $50,000 at an interest rate of 5% that compounds annually. What would be the amount of interest?

In this case, P=50000, i=5% , n=3, hence

it would be: $50,000 [(1 + 0.05)3 – 1] = $50,000 [1.157625 – 1] = $7881.25

How is this done? Well, the above formula of compound interest is implemented.

The Execl sheet codes in this case are,

Balance (After X Years) =B3*(1 + B4 /B5)^(B6 * B5)

Total Interest= =SUM(B8,-B3)

How to calculate compound interest using FV Function in Excel? Let’s see!

Excel’s FV function returns the future value of an investment based on factors similar to what we have just discussed, though its syntax is a bit different:

FV(rate, nper, pmt, [pv], [type])
In the function, the first 3 arguments are obligatory and the last 2 are optional.

• rate – an interest rate per period, exactly as the argument’s name suggests.
• nper – the number of payment periods.
• pmt – an additional payment that is made each period, represented as a negative number. If the pmt argument is omitted, the pv parameter must be included.
• pv (optional) – the present value of the investment (principle investment), which is also a negative number. If the pv argument is omitted, it is assumed to be zero (0), in this case the pmt parameter must be specified.
• type (optional) – specifies when additional payments are due: 0 or omitted – at the end of the period, and 1 – at the beginning of the period.
• Read this : Latest High Paying Career Change Ideas at 40

Compound interest formula Excel:

Using this below formula you can calculate your CI.

Attachment Excel sheet: compound-interest-calculator-excel-formula

Monthly Compound Interest Formula Excel Template:

If the interest on your investment is paid monthly (while being quoted as an annual interest rate), the Excel compound interest formula becomes:

Calculations go this way,

=P*(1+i/12)^(n*12)
where,

P  is the initial amount invested;
i  is the annual interest rate (as a decimal or a percentage);
n  is the number of years over which the investment is made.

 Excel Sheet: Monthly-compound-interest-calculator-excel-formula

In this case, the annual rate of interest is divided by 12 so that we get monthly compound interest.

Excel Template for Quarterly Interest:

If the interest on your investment is paid quarterly (that is i/4)(while being quoted as an annual interest rate), the Excel compound interest formula becomes:

The above example values become!

Calculations:

=P*(1+i/4)^(n*4)
where,

P  is the initial amount invested;
i  is the annual interest rate (as a decimal or a percentage);
n  is the number of periods over which the investment is made.

Excel Sheet: Quarterly-compound-interest-calculator-excel-formula

How Compound Interest Grows?

Using the above example, since compound interest also takes into stats collected interest of previous periods, the interest value/amount is not the same for all 3 (n) years, as it would be with simple interest.

The interest of 1st year:

In this case, P=$50000, i=5% , n=1, hence

it would be: $50,000 [(1 + 0.05)1 – 1] = $50,000 [1.05 – 1] = $2500

The interest of 2nd year:

In this case, P=$50000+$2500, i=5% , n=1, hence

it would be: $52,500 [(1 + 0.05)1 – 1] = $52,500 [1.05 – 1] = $2625

The interest of 3rd year:

In this case, P=$52500+$2625, i=5% , n=1, hence

it would be: $55,125 [(1 + 0.05)1 – 1] = $55,125 [1.05 – 1] = $2756

Adding these 3 years would result in the total interest value as shown below.

Total Interest: i1+i2+i3 = $2500+$2625+$2756 = $7881.25

This value is exactly equivalent to the initial assessed value. It just shows the per year growth of interest value.

Compounding Periods Do Matter!

When computing compound interest, the number of compounding periods makes a vital difference. The basic rule is that the higher the number of compounding periods, the greater the amount of compound interest.

Special Thanks to: Investopedia