How To Calculate Monthly Compound Interest In Excel

We can find the monthly compound interest of an investment using the mathematical formula for compound interest or through Excel’s FV function.

Whenever you apply for a loan or decide to take an investment, it is important to understand how the interest is computed. There are two main types of interest: simple interest and compound interest.

Simple interest is calculated using the initial principal amount only and includes an interest rate that never changes. For example, if you take a simple interest loan of $100 that follows an annual rate of 5%, you will have to pay $105 at the end of the year.

However, many banks and credit cards use compound interest over simple interest. Compound interest is the interest calculated on the principal amount and any previously accumulated interest.

If the interest is accumulated monthly, your investment has a monthly compound interest. 

This guide will explain how to calculate monthly compound interest in Excel. We will explain how to use the FV function and a custom Excel formula to find the future value of an investment.

Let’s dive right in!

A Real Example of Calculating Monthly Compound Interest in Excel

To compute the monthly compound interest, we can use the following formula:

Principal * (1 + Annual Rate/12) ^ (Years * 12)) - Principal 

The formula computes the total amount with compound interest and subtracts the initial principal to find the total accumulated interest.

Users can also find the future value of their loan or investment with the built-in FV function. 

The function can compute the value of an investment given the initial amount, the interest rate per compounding period, and the total number of compounding periods.

Let’s take a look at a quick example where we may need to calculate monthly compound interest.

Suppose you have applied for a loan of $1000 from your bank. If the bank charges a monthly interest rate of 6%, how much must be paid in total after a single year?

monthly compound interest in Excel

 

To get the value in B6, we just need to use the following formula:

=B1*(1+(B3/B2))^(B2*B4)

We can also arrive at the same result using the FV function:

=FV(B3/B2,B2*B4,0,-B1)

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to calculate the monthly compound interest of an investment, head over to the next section to read our step-by-step breakdown on how to do it!

How To Calculate Monthly Compound Interest In Excel

This section will help you calculate the monthly compound interest of an investment or loan. You’ll learn how to use the FV function to find the future value of an investment that accumulates monthly compound interest.

Follow these steps to start calculating monthly compound interest in Excel:

  1. First, we’ll explain how to follow the general compound interest formula to find the monthly compound interest.
    The user must first indicate the principal amount. The principal refers to the initial amount borrowed or deposited as an investment.
    enter principal amount
  2. The compounding period refers to the span of time between when interest was last compounded and when it must be compounded again. When computing monthly compound interest, we should specify that there are 12 compounding periods in a single year.
    enter number of compounding periods each year
  3. The user must take note of the investment’s annual interest rate.enter annual rate
    In our example, the monthly compounding follows an annual interest rate of 6%.
  4. The user must also specify how many years their investment will keep accumulating interest.

    enter number of years of investment or loan
    In this example, we want to know the value of our investment after two years of monthly compound interest.
  5. We’ll use the formula =B1*(1+(B3/B2))^(B2*B4) to find the monthly compound interest.

    monthly compound interest in Excel
    In this example, we’ve determined that the value of our investment after two years will grow to $1127.16.
  6. Next, we will explain how to use the FV function to find the future value of your investment. The “FV” in the function’s name stands for “future value” and the function is often used to calculate different types of compound interest.
    monthly compound interest in Excel with FV function
  7. The first argument of the FV function is the rate per compounding period. Since we will be calculating monthly compound interest, we must divide the annual interest rate by 12.
    monthly compound interest in Excel
  8. The second argument will be the total number of compounding periods. We can calculate this by multiplying the number of years by 12.
    monthly compound interest in Excel
  9. The third argument of the FV function determines the amount to pay for each compounding period. In this example, we will specify a value of 0 since we do not require any payments for each period.
    monthly compound interest in Excel
  10. The fourth argument refers to the total present amount. If we are calculating an investment, we set this value to a negative number to indicate that we are depositing a certain amount and expecting a return in the future.
    monthly compound interest in Excel
  11. Hit the Enter key to evaluate the FV function.
    monthly compound interest in Excel

 

 

These are all the steps you need to find the monthly compound interest of your investment in Excel. 

This step-by-step tutorial should provide you with all the information you need to start calculating monthly compound interest in Excel.

We’ve explained how to find the monthly compound interest of an investment using both the compound interest formula and the built-in FV function.

The FV function is just one example of the many built-in financial functions in Excel. For example, you can read our step-by-step guide on compound interest to learn more about the FV function and how to use it to compute different types of compound interest.

You may also check our guide on finding daily compound interest if your loan or investment compounds interest daily instead.

That’s all for this guide on monthly compound interest! If you’re still looking to learn more about Excel, be sure to check out our library of Excel resources, tips, and tricks! 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like