Methods to Apply Continuous Compound Interest Formula in Excel

In some cases especially in financial analysis, you may need to calculate continuous compound interest. In this article, I’ll show you 6 methods as well as provide a calculator to calculate continuous compound interest using the formula in Excel.


Download Practice Workbook


Basics of Continuous Compound Interest


i. What is Continuous Compound Interest

Compound interest calculates only the interest of principal after a certain time. But the continuous compound interest means increasing the interest based on principal or initial amount as well as the multiple time periods. That is to say, you can measure the final amount or future value (FV) if the interest is compounding continuously.


ii. The Formula of Continuous Compound Interest

The formula of continuous compound interest is as follows-

A(FV) = Pert

Here,

A is the final amount or continuous compounding amount (FV).

P is the initial amount or principal.

r means the rate of interest expressed in percentage.

t refers to the number of time units.

Read More: Compound Interest Formula in Excel: Calculator with All Criteria


Methods of Continuous Compound Interest Formula Excel 

Assuming that you want to buy a bond or invest your money anywhere which has some properties. For example, the Principal Amount (P) is $1000. Also, the Rate of Interest (r) is 10% and the Number of Time Units (n) is 25 years. That means it will be ended after that period. More importantly, the interest is compounding continuously. Now, you need to compute the Continuous Compounding Amount or Future Value (FV).

Dataset


1. Future Value with Annual Continuous Compound Interest 

If the investment will end after 25 years. And you need to measure the continuous compounding amount after that period. So, use the following formula in Excel.

=C5*EXP(C6*C7)

Here, C5 is the initial amount (P), C6 is the rate of interest (r), and C7 is the number of time units/years (n).

Besides, the EXP function returns the value of the constant e raised to the power of a given number.

Inserting Formula for Annual Interest

Next, after inserting the formula, press Enter and the output will look like the following.

Future Value with Annual Continuous Compound Interest

Read More: How to Calculate Future Value When CAGR is Known in Excel (2 Methods)


2. Future Value with Semi-Annual Continuous Compounding

If the interest is compounding incessantly with the semi-annual interest rate. That means, if the investment yields 10% semi-annually, the annual compounding interest will be 20%. For your convenience, you may add an individual term namely Number of Compounding Units Per Year. In the case of semi-annual compounding, the value of the term will be 2.

So the adjusted formula will be-

=C5*EXP(C6*C7/C8)

Here, C8 is the number of compounding units per year.

Inserting Formula for Semi-Annual Interest

If you press Enter, you’ll get the following output.

Future Value with Semi-Annual Continuous Compounding


3. Future Value with Quarterly Continuous Compound Interest

Again, the investment yields 10% quarterly, that is to say, the annual compound interest will be 40%. In such a situation, you need to insert the following formula.

=C5*EXP(C6*C7/C8)

Here, C8 is the cell representing the number of compounding units per year.

Future Value with Quarterly Continuous Compound Interest


4. Future Value with Monthly Continuous Compound Interest

Furthermore, if you want to measure the future value (FV) after 8 months with the monthly compounding interest of 10%, use the formula below.

=C5*EXP(C6*C7/C8)

Here the number of compounding units (C8 cell) is 12.

Future Value with Monthly Continuous Compound Interest

Read More: Formula for Monthly Compound Interest in Excel (With 3 Examples)


5. Calculate Present Value with Continuous Compound Interest

Let’s say you know the future value which will be 8 months later. Now, you need to find out the present value that you have to invest. So, you’ll get such an amount after 8 months. Luckily, you can easily measure that quickly in Excel.

For doing this, you must put a minus sign (-) after the Exp function and before the rate of interest.

=C5*EXP(-C6*C7/C8)

Calculate Present Value with Continuous Compound Interest

As you see in the above screenshot, you’ll get the principal or present value if you know the future value.

Read More: A Daily Compound Interest Calculator in Excel (Template Attached)


6. Continuous Compound Interest with the FV Function

Last but not the least, you might use the FV function to calculate the continuous compounding amount. The function mainly calculates the future value for an investment based on compounded interest.

So, use the formula below.

=FV(C6/C8,C8*C7,0,-C5)

Here,

Rate = C6/C8. Here, I divide the rate of interest by the number of compounding units per year to get a monthly interest rate.

Nper = C8*C7. I multiply the number of years and number of compounding units to get the total number of payment periods.

Pmt = 0. I don’t have any additional money for each period.

Pv = –C5. It is the present value i.e the initial amount.

Continuous Compound Interest Formula Excel Using the FV Function

Read More: Compound interest excel formula with regular deposits


Things to Remember

  • Keep in mind the number of compounding units per year. Look at the following table.
Intra-year Compounding Interest Rate Number of Compounding Units Per Year
Semi-Annual 2
Quarterly 4
Monthly 12
Weekly 52
Daily 365 (actual)
  • Theoretically, the number of time units in the case of continuous compound interest will be infinitive. However, this will not be possible in the real world. So, you need to assume the number of periods for calculation purposes.

Calculator for Measuring Continuous Compound Interest

If you download the practice workbook, you’ll get a sheet namely Calculator. If you wish, you can insert your investment properties. Therefore, you’ll get the future value.

Calculator

Note: Also, you may save the sheet as an Excel template for using the calculator frequently.


Conclusion

This is how you can use the above methods to calculate the continuous compound interest using Excel formula. I strongly believe this article will be highly beneficial for you. Anyway, if you have any queries or suggestions, please share them.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

2 Comments
  1. Good day
    Thank you for the great insights and calculation.
    I wonder if you can provide further insights guidance on the following requirement / scenario

    Investment / principal amount is $210
    Compounded growth is weekly at 3% (yes,3% every week) for 5 years (60 months)
    I used the formula as follows
    “FV = (0,03/52; 52*5; 0; -210)” and the result I get is $237,95
    the manual process comes to the value of $236,36 on the 4th line as follows
    210
    216.30
    222.79
    229.47
    236.36
    243.45…. etc

    Appreciate your guidance and assistance
    Many thanks
    A

  2. Hello AHMED
    I think you made an unintentional mistake during the calculation.
    After 5 years, the future value would be $243.97. The result would come the same whether you calculate manually or use any function.
    Calculating manually, you’ll get the result below.
    Manual Calculation
    And, using the FV function the result would come like in the one below.
    Using FV Function

Leave a reply

ExcelDemy
Logo