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
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