Calculating compound interest with Excel VBA

Compound Interest Formula:

The formula for compound interest is:

A= P * (1 + r / n) ^ (n * t)

Where:

• A= Final amount (principal + interest)
• P= Principal (initial investment)
• r = Annual interest rate (in decimal form, for example, 5% is 0.05)
• n = Number of times the interest is compounded per year (for monthly compounding, n=12n = 12n=12)
• t = Number of years the money is invested.

VBA Code to Calculate Compound Interest:

1. Open the VBA editor:
   • Open Excel.
   • Press Alt + F11 to open the VBA editor.
   • In the editor, click Insert -> Module to add a new module.
2. VBA Code:

Sub CalculateCompoundInterest()
    ' Declare variables
    Dim P As Double  ' Principal (initial investment)
    Dim r As Double  ' Annual interest rate
    Dim n As Integer ' Number of compounding periods per year
    Dim t As Double  ' Duration of investment in years
    Dim A As Double  ' Final amount (principal + interest)   
    ' Get values from Excel cells
    P = Range("B1").Value  ' Principal (initial investment) from cell B1
    r = Range("B2").Value  ' Annual interest rate from cell B2
    n = Range("B3").Value  ' Number of compounding periods from cell B3
    t = Range("B4").Value  ' Duration in years from cell B4   
    ' Calculate compound interest
    A = P * (1 + r / n) ^ (n * t)   
    ' Display the result in cell B5
    Range("B5").Value = A   
    ' Optional: Display the result in a message box
    MsgBox "The final amount after " & t & " years is " & Format(A, "0.00") & " €.", vbInformation, "Result"  
End Sub

Explanation of the Code:

1. Variable Declaration:
   • P (Principal): The initial investment amount.
   • r (Interest Rate): The annual interest rate in decimal form (e.g., 5% would be entered as 0.05).
   • n (Number of Periods): The number of times the interest is compounded per year. For monthly compounding, use n = 12.
   • t (Time): The number of years the money is invested.
   • A (Final Amount): The total amount after applying compound interest.
2. Getting Values from Excel Cells:
   • The code retrieves the values from cells B1, B2, B3, and B4 where the user inputs the data for the principal, interest rate, number of periods, and duration.
3. Calculating Compound Interest:
   • The compound interest formula is applied here:

A = P * (1 + r / n) ^ (n * t)

This calculates the final amount after applying compound interest.

1. Displaying the Result:
   • The result (final amount) is displayed in cell B5 of the Excel sheet.
   • Additionally, a message box (MsgBox) shows the final amount in a pop-up message with the formatted result.

Example Usage in Excel:

Suppose you have the following values in your cells:

• B1 (Principal): 1000 (the initial investment of 1000 €)
• B2 (Interest Rate): 0.05 (5% annual interest rate)
• B3 (Number of Periods): 12 (interest compounded monthly)
• B4 (Time): 5 (investment duration in years)

When you run the macro, the calculation will be done, and the final amount (principal + interest) will appear in cell B5.

How to Run the Macro:

1. After pasting the code into the VBA editor, return to your Excel sheet.
2. Press Alt + F8 to open the « Macro » dialog.
3. Select CalculateCompoundInterest and click Run.

The final amount will be calculated and displayed in cell B5, and a message box will also show the result.

Customization:

You can adjust the cells (e.g., change B1, B2, B3, B4) to fit where you want the user to input the values in your Excel sheet.