How to Calculate Compound Interest in Excel

Whether for personal or business application, it’s a good idea to know how to calculate compound interest for loans and investments. There are two methods you can use, the long form and the FV (future value) function.

Download ExcelCompoundInterest.xlsx to see the examples.

Long Form

In this first example, we’ll assume a one and a half percent (1.5%) interest rate over a period of 12 months and a beginning balance of $5,000. If we were doing this with a calculator, we’d take $5,000 and multiply it by the 1.5% rate after dividing it by 12 to account for the monthly interest rate. We’d take the result of that and add it back to the $5,000. So:

$5,000 X (.015/12) = .21To get to the balance with the interest, we simply add the starting balance back to it.

$5,000 + ($5,000X (.015/12)) = $5,000.21Looking 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 row the second date row to the bottom to arrive at the compounded interest.

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 purchase), 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.

Function Method

The second way to calculate compound interest is to use the FV function. This function requires:

• Interest Rate (don’t forget to divide by 12 if it’s an annual rate!)
• Number of periods (in our example 12 for 12 months)
• Additional monthly payments (0 in our case)
• Present value (initial deposit expressed as a negative)

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

Which Form to Use?

What we actually created in the Long Form 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. If you don’t need all the detail, FV is just fine.

If you’re calculating 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.