MS Excel Examples: Compound Interest

	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:

	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

To add a new form, on the Standard toolbar of Microsoft Visual Basic, click the Insert UserForm button

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

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

Press F5 to test the form


Home	Copyright © 2004-2010 FunctionX, Inc.