How To Calculate Compound Interest Using Excel

There are two ways to calculate compound interest using Excel. You can either use a formula or create a macro function. Using a formula entails changing it whenever the quantities change over time. Although doing this may seem easier, it can become a hassle over time because the quantities will definitely change and you will have to redo the encoding over and over again. Creating a macro function can be tricky, but once you get it done, you can use this spreadsheet whenever you wish without having to worry about the changes in quantities.

To use a formula to calculate compound interest in Excel, follow these steps:

  • Open a new document. Open Excel and click on File. Open a new workbook where you would like to work on calculating your compound interest.
  • Label the table. Go to row 1 of your spreadsheet and start labeling each column depending on the values that you will need. Encode the following:
    • Column A: Present value/amount invested
    • Column B: Rate
    • Column C: Number of investment periods
    • Column D: Future value/compound interest
  • Encode the values. Now that you have labeled your columns, start encoding the values of the present value, the rate, and the number of investment periods. Put these values in row 2 under its respective columns.
  • Encode the formula. Once you are done encoding the values that you need, go to column d, row 2 and type in: “=A2*(1+B2)^C2 and press enter. Upon pressing enter, you will get the future value or compound interest of your values.
  • Copy and paste the formula. If you want to add to the table later on, just copy and paste the formula on column D, row 2, and change all the “2”s to the row number of your new values.

To create a macro function to calculate compound interest in Excel, follow these steps:

  • Open a new document. Open Excel and click on File. Open a new workbook where you would like to work on calculating your compound interest.
  • Encode the script of your macro function. Go to Tools, click on Macro, and open Visual Basic Editor. When the Visual Basic Editor has opened, go to the Insert drop down menu and click on Module. Encode the following script:
    Function Yearly_Rate(PV As Double, R as Double, N As Double) As Double
    Yearly_Rate = PV*(1+R)^N
    End Function
  • Encode your values. Now go to the File drop down menu and click on Close and Return to Excel. This will bring you back to your worksheet. You can start encoding your values as follows:
    • Column A, Row 1: Present value/amount invested
    • Column A, Row 2: Rate
    • Column A, Row 3: Number of investment periods
  • Use your macro function. Once you have all the values encoded, you can start using your macro function. Go to Column A, Row 4 and type in “Yearly_Rate(A1,A2,A3)”. This will give you the future value or compound interest of the values that you encoded.
  • Save your worksheet. If you wish to save your worksheet, open the File drop down menu, go to Save As, and select the Excel macro-enabled format. This will allow you to use the worksheet with the macro function that you created.


Computing your compound interest is made a lot easier with the use of Excel. Whether you use the first or second step is completely up to you. Good luck!

 

Share this article!

Follow us!

Find more helpful articles: