How to Calculate Compound Interest in Excel

Key Takeaways

• The standard compound interest formula in Excel is =P*(1+r/n)^(n*t), where P is the principal, r is the annual interest rate, n is the compounding frequency and t is the number of years.
• Excel's built-in FV function provides a faster way to calculate compound interest without building the formula manually.
• Compounding frequency (annual, quarterly, monthly, daily) significantly affects total interest earned or owed.
• You can build a full compound interest schedule in Excel to track balance growth period by period.

The compound interest formula in Excel is =P*(1+r/n)^(n*t). Whether for personal or business application, knowing how to calculate compound interest for loans and investments is an essential skill. Compound interest is interest earned on both the original principal amount and any previously accumulated interest, which means your balance grows faster over time compared to simple interest.

Each variable in the formula means the following:

• P = Principal (your starting balance or initial deposit)
• r = Annual interest rate (expressed as a decimal, so 1.5% becomes 0.015)
• n = Number of compounding periods per year (12 for monthly, four for quarterly, 365 for daily)
• t = Number of years

There are two methods you can use to calculate compound interest in Excel: the manual formula and the FV (future value) function. We'll walk through both approaches below using the same example so you can compare results and choose the method that fits your needs.

Download ExcelCompoundInterest.xlsx to follow along with the examples.

The Compound Interest Formula in Excel

Using the Manual Formula

The manual compound interest formula you can type directly into any Excel cell is:

=P*(1+r/n)^(n*t)

This gives you the future balance including both principal and accumulated interest. To isolate just the interest earned, subtract the principal from the result.

Let's walk through a step-by-step example using a 1.5% annual interest rate compounded monthly on a beginning balance of $5,000.

1. Identify your variables. P = 5000, r = 0.015, n = 12 (monthly compounding), t = one (one year).
2. Enter the formula. In an Excel cell, type: =5000*(1+0.015/12)^(12*1)
3. Review the result. Excel returns $5,075.28, which is your balance after one year of monthly compounding.

If we break this down month by month as shown in the sample workbook, we calculate the first month like this: $5,000 X (.015/12) = $6.25 in interest. To get to the balance with the interest, we simply add the starting balance back to it: $5,000 + ($5,000 X (.015/12)) = $5,006.25.

Looking at the sample worksheet tab named LF-Compounded Monthly, we see that our opening deposit is on 12/31/2015. With interest compounding monthly, each successive row with its end of month date in each succeeding month needs this formula copied down from the second date row to the bottom to arrive at the compounded interest.

Adjusting for different compounding frequencies is straightforward. Simply change the value of n:

• Annual compounding (n=1): =5000*(1+0.015/1)^(1*1) = $5,075.00
• Quarterly compounding (n=4): =5000*(1+0.015/4)^(4*1) = $5,075.21
• Monthly compounding (n=12): =5000*(1+0.015/12)^(12*1) = $5,075.28
• Daily compounding (n=365): =5000*(1+0.015/365)^(365*1) = $5,075.56

If interest were being calculated on the exact number of days each month and then added to the balance at the end of the month (similar to credit card interest calculations on regular purchases), then we'd need to divide the rate by 365 and then multiply it by the number of days in that period. That would look more like this.

Examine LF-Compound Daily 360 and LF-Compound 365 for true daily compounding interest calculations. Rather than accumulating daily interest and adding it to the balance at the end of the month, each day interest is calculated and added to the balance on which the next day's interest is calculated upon. The 360-day convention is common in banking, while the 365-day method reflects actual calendar days. 

Using the FV Function

The second way to calculate compound interest is to use the FV function. This is often faster when you need a single future balance without building a period-by-period schedule.

The syntax is: =FV(rate, nper, pmt, pv, type)

This function requires:

• rate = Interest rate per period (don't forget to divide by 12 if it's an annual rate!)
• nper = Number of periods (in our example, 12 for 12 months)
• pmt = Additional periodic payments (0 in our case since we're not making deposits)
• pv = Present value (initial deposit expressed as a negative number)
• type = Optional; 0 for end of period, 1 for beginning (defaults to 0 if omitted)

Here's how to apply it step by step using our $5,000 example at 1.5% compounded monthly:

1. Click on an empty cell where you want the result.
2. Type: =FV(0.015/12, 12, 0, -5000)
3. Press Enter. Excel returns $5,075.28, matching our manual formula result.

Important tip: The present value (pv) must be entered as a negative number. Excel treats cash you pay out (your initial deposit) as negative and cash you receive (your future balance) as positive. If you enter 5000 instead of -5000, the result will be negative, which can cause confusion.

In the sample workbook the example is on the FV Function worksheet.

Compound vs. Simple Interest

Understanding the difference between simple and compound interest helps you appreciate why compounding frequency matters. You calculate simple interest only on the original principal using the formula =P*r*t. You calculate compound interest on both the principal and previously accumulated interest, which causes the balance to grow at an accelerating rate.

Here's how the two methods compare using a $5,000 principal at a 1.5% annual rate:

The gap widens significantly over longer time periods, which is why compound interest has such a powerful effect on long-term savings and investments.

Putting It All Together: Practical Applications

What we actually created in the manual formula example is an amortization schedule.

This would allow us to see the balance as it increases over time, rather than only at the very end, as with the FV example.

So which method should you use? It depends on what you need:

• Use the manual formula when you need period-by-period visibility, want to build a detailed compound interest schedule or need to model irregular deposits and withdrawals.
• Use the FV function when you need a quick single answer, such as projecting a savings account balance or estimating total loan cost.

Both methods work well for common real-life applications:

• Savings growth projections: Estimate how much a savings account or CD will be worth after a set number of years.
• Loan cost analysis: Calculate the total interest you'll pay on a loan over its full term.
• Investment returns: Project the future value of an investment portfolio assuming a consistent annual return.

If you're calculating a mortgage, you don't have to set up a thing! Just look for the Loan Amortization Schedule template available for any version of Excel. Just search in templates or choose Sample Templates in Excel 2010 to find it. This built-in amortization schedule template handles principal, interest and payment breakdowns automatically.

Tips for Accurate Compound Interest Calculations

Even experienced Excel users can make small errors that throw off compound interest calculations. Keep these best practices in mind:

1. Always divide the annual rate by the compounding periods. If your annual interest rate is 6% and you're compounding monthly, use 0.06/12 as the rate, not 0.06. This is the most common mistake.
2. Match the rate period to the nper period in the FV function. If you divide the rate by 12, your nper should also reflect the total number of months, not years.
3. Enter the present value as a negative in FV. This tells Excel that the initial deposit is cash flowing out. Forgetting this step produces a negative result that can be confusing.
4. Use cell references instead of hardcoded numbers. Placing your rate, principal and term in separate cells makes it easy to adjust assumptions and run different scenarios without rewriting Excel formulas.
5. Double-check your compounding frequency assumption. Verify whether the loan or investment compounds annually, quarterly, monthly or daily. The terms of the financial product dictate which value of n to use, and using the wrong one will produce inaccurate results.