Compound Interest refers to earning or paying interest on interest. Although it can apply to both savings and loans, it is easiest to understand when thinking about savings. Download this compound interest calculator excel template and get started.
After each compound period, the interest earned over that period is added to the principal so that the next calculation of interest includes the original principal plus the previously earned interest.
With Simple Interest (the opposite of compound interest), interest is only calculated from the principal, and interest is not added to the principal.
Features and Functionality of this Compound Interest Excel Calculator
This spreadsheet and excel template/ calculator was designed as an educational tool – to help show how compound interest works for both savings and loans.
The table is based on the payment frequency and shows the amount of interest added each period. The graph compares the total (cumulative) principal and payments to the balance over time.
One of the worksheets in this file is nearly identical to the online calculator above, and was used to help verify the calculations.
Compound Interest Formula
The basic compound interest formula for calculating a future value is F = P*(1+rate)^nper where
- F = the future accumulated value
- P = the principal (starting) amount
- rate = the interest rate per compounding period
- nper = the total number of compounding periods
Formula for Compounding Yearly, Monthly, Weekly
The formula is often written as F = P*(1+r/n)^(n*t) with the following variables definitions:
- P = the principal amount (the initial savings or the starting loan amount)
- r = the nominal annual interest rate in decimal form. (e.g. 5% means r=0.05)
- n = the number of compound periods per year (e.g. for monthly, n=12)
- t = the time in years
This is the same as the basic formula where rate = r/n and nper = n*t. Although the math can handle a decimal value for nper, it should usually be a whole number. For example, with monthly compounding for a total of 18 months, n=12 and t=1.5 resulting in nper=12*1.5=18.
Formula for Daily Compounding
For daily compounding, the value for n (number of compound periods per year) is typically 365 and you use total number of days in place of n*t like this: F = P*(1+r/365)^days. For day count conventions other than n=365, see the Wikipedia article.
If you want to study more about this template, you can visit tutorial on compound interest calculation as well.
Download and use Compound Interest Calculator Excel template
To use this free compound interest calculator excel template, you should have Microsoft Office/ Microsoft Excel installed in your system.
After installing Excel or Spreadsheet, download the zip file of this template, extract the template using WinRAR or 7Zip decompressing software. Once extracted, you can open the file using Excel and start entering data or customizing the template.
