My daughter just completed a project of creating a piggy bank at school. This reminded me of the importance of saving. The return on investment is often determined by interest rates and this is why investors or anyone who wants to save chooses the right moment to make an investment.
In this week’s tip we explain how to calculate compound interest using Microsoft® Excel®.
Since there is no standard function to calculate compound interest in Excel, we’ll show you how to create a custom compound interest function.
Note: You are welcome to download the workbook to practice
Applies To: Microsoft® Excel® 2010 and 2013
Compound interest is interest paid on both the original amount of money and on the interest it has already earned. Follow the steps below as we explain how:
- Press ALT & F11 to start the Visual Basic editor.
- Select Module from the Insert menu.
- Copy and Paste the following code.
Function Compound_Rate(PV As Double, R As Double, N As Double) As Double
Compound_Rate = PV*(1+R)^N
End Function
- A function returns a value.
- Double is a data type that has a fractional component.
- PV is the Present value of the Investment.
- R is the interest rate.
- N is the number of investment periods.
- Save the changes.
- Select the File menu, then Close and Return to Microsoft Excel.
- Press F12 to Save As.
- Select Excel Macro Enabled workbook under Save As Type.
- Place the cursor in cell B5.
- Type: =Compound_Rate(B1,B2,B3) and press enter.
- B1 is the Present value of the investment.
- R is the interest rate.
- N is the number of investment periods.
- The compound interest is 900 as displayed below.
We have created a custom function to calculate compound interest in an easy and accurate manner.