Compound interest works by calculating the amount of interest each period and adding it on to the principal value before the next calculation. Basically, it is a way to add up the interest faster because after each compounding the original interest starts to earn interest too. It can be useful to create a formula for this type of calculation in Microsoft Excel so that users can compare interest rates and periods for compounding in a visual way.
Create a new blank Excel document by clicking on the "File" menu and selecting "New."
Enter a column heading corresponding to the following values in the first row of your document starting in column A.
Column - Heading A - Amount Invested B - Annual Percentage Rate C - # Times Compounded Annually D - # of Years F - Future Value
Enter the desired values for calculation in row 2 starting in column A. Example values are listed below for each column. This represents £650 compounded quarterly at a rate of 2.25% for 10 years.
A - 1000 B - .0225 C - 4 D - 10
Enter the following function in row 2 of column F.
The result of this formula should be approximately 1251.53.
Apply this formula to more rows by clicking on it and dragging your mouse down the desired number of rows. When you have highlighted enough rows in one column hit the Control (Ctrl) and D keys at the same time to "Fill Down" the formula.
Make sure that when you enter your annual percentage rate you either divide it by 100 or format the cell as a percentage.
Tips and warnings
- Make sure that when you enter your annual percentage rate you either divide it by 100 or format the cell as a percentage.