Home

MS Excel Examples: Compound Interest

 

Introduction

The compound interest is the amount of money earned on a deposit during a period of time. It can be calculated using the following formula:

Compound Interest Formula P = Principal
r = Annual (Interest) Rate
m = Number of Compounding Periods per Year
n = Total Number of Compounding Periods
A = Amount Earned After n periods
 

Practical Learning: Calculating the Compound Interest

  1. To add a new form, on the Standard toolbar of Microsoft Visual Basic, click the Insert UserForm button
  2. Design it as follows:
     
    Control Name Caption/Text Other Properties
    Frame   Preparation  
    Label Principal:  
    TextBox txtPrincipal 0.00 TextAlign: 3 - frmTextAlignRight
    Label   Interest Rate:  
    TextBox txtInterestRate 0.00 TextAlign: 3 - frmTextAlignRight
    Label   %  
    Label   Number of Periods:  
    TextBox txtPeriods 0.00 TextAlign: 3 - frmTextAlignRight
    Frame   Compound Frequency  
    OptionButton optMonthly Monthly Alignment: 0 - fmAlignmentLeft
    OptionButton optQuarterly Quarterly Alignment: 0 - fmAlignmentLeft
    OptionButton optSemiannually Semiannually Alignment: 0 - fmAlignmentLeft
    OptionButton optAnnually Annually Alignment: 0 - fmAlignmentLeft
    Frame   Results  
    CommandButton cmdCalculate Calculate  
    Label   Interest Earned:  
    TextBox txtInterestEarned 0.00 TextAlign: 3 - frmTextAlignRight
    Label   Amount Earned:  
    TextBox txtAmountEarned 0.00 TextAlign: 3 - frmTextAlignRight
  3. Double-click the Calculate button and implement its Click event as follows:
     
    Private Sub cmdCalculate_Click()
        Dim Principal As Currency
        Dim InterestRate As Double
        Dim InterestEarned As Currency
        Dim FutureValue As Currency
        Dim RatePerPeriod As Double
        Dim Periods As Integer
        Dim CompoundType As Integer
        Dim i As Double
        Dim n As Integer
        
        Principal = CCur(txtPrincipal.Text)
        InterestRate = CDbl(txtInterestRate.Text) / 100
        
        If optMonthly.Value = True Then
            CompoundType = 12
        ElseIf optQuarterly.Value = True Then
            CompoundType = 4
        ElseIf optSemiannually.Value = True Then
            CompoundType = 2
        Else
            CompoundType = 1
        End If
        
        Periods = CInt(txtPeriods.Text)
        i = InterestRate / CompoundType
        n = CompoundType * Periods
        RatePerPeriod = InterestRate / Periods
        FutureValue = Principal * ((1 + i) ^ n)
        InterestEarned = FutureValue - Principal
        
        txtInterestEarned.Text = FormatCurrency(InterestEarned)
        txtAmountEarned.Text = FormatCurrency(FutureValue)
    End Sub
  4. Press F5 to test the form

 

 

Home Copyright © 2004-2010 FunctionX, Inc.