VBA loop help

[thegh0st]

Hello, I am doing a project for class-here is the assignment:
Suppose $800 is deposited into a savings account earning 4% interest compounded annually, and $100 is added to the account at the end of each year. Calculate the amount of money in the account at the end of 10 years. Determine a formula for computing the balance at the end of one year based on the balance at the beginning of the year. Allow the user to input the beginning balance and the amount to be contributed at the end of each year. You must a loop in this program.

here is the code I have so far:

Public Class Savings

Private Sub btnCalc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCalc.Click
Dim intrest As Decimal
Dim bonus As Integer
Dim endofyear As Integer
Dim balance As Integer
Dim years As Integer
Dim contributed As Integer
Dim summary As Integer


balance = CInt(InputBox("Insert your beginning balance"))
bonus = CInt(InputBox("Insert amount contributed each year"))
intrest = 0.04 * balance
years = 10

lstAccount.Items.Add("Beginning/End of year")

For summary = 1 To years
endofyear = balance + bonus + intrest

lstAccount.Items.Add(balance & ControlChars.Tab & endofyear & ControlChars.Tab & summary & ControlChars.Tab)


Next
lstAccount.Items.Add("------------------…
contributed = bonus
lstAccount.Items.Add("Contributed amount:" & contributed)


End Sub

Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
Me.Close()
End Sub
End Class

I cannot figure out how to make the previous years balance become the next years and so forth.

Any suggestions? Thanks

 

[Boller]

Do you really need VBA ?

This could be done with worksheet formulas (and this seems to be implied by the assignment).

 

[thegh0st]

I am in a visual basic class

 

[Boller]

Dim initialDeposit#, annualDeposit#, IntRate#, nbrYears#, yr#, yearEnd#
initialDeposit = CInt(InputBox("Insert your beginning balance"))
annualDeposit = CInt(InputBox("Insert amount contributed each year"))
IntRate = 0.04
nbrYears = 10
For yr = 1 To nbrYears
    If yr = 1 Then
        yearEnd = initialDeposit * (1 + IntRate) + annualDeposit
        MsgBox "The balance at end of year 1 is $" & Format(yearEnd, "#,###.00")
        Cells(yr, "A") = "Balance at end of year 1 : $" & Format(yearEnd, "#,###.00")
    Else
        yearEnd = yearEnd * (1 + IntRate) + annualDeposit
        MsgBox "The balance at end of year " & yr & " is $" & Format(yearEnd, "#,###.00")
        Cells(yr, "A") = "Balance at end of year " & yr & " : $" & Format(yearEnd, "#,###.00")
    End If
Next

I suggest you add code to handle situations for the input boxes where the cancel button is selected/nothing is entered/the wrong data type is entered.

Perhaps you are looking for a UDF instead ?