help creating a compound interest form with loops

cc.caroline15

Registered User.
Local time
Today, 05:03
Joined
Oct 23, 2014
Messages
23
Hello everyone
I really need help im new to VBA and having a hard time understanding loops. I'm trying to create a compound interest form using loops and the following formula:
TotalVariable = TotalVariable + (TotalVariable * (RateVariable / 100 / 12))
I'm not sure whats wrong with my code and it's not working!! Can anyone take a look at my code and help me? Thanks
 

Attachments

Here's a starting place . . .
Code:
Private Sub cmdCalculate_Click()
    Dim i As Integer
    Dim amt As Currency
    
    amt = Me.txtAmount
    
    For i = 0 To Me.txtYears * 12
        amt = amt + (amt * (Me.txtInterest / 100 / 12))
    Next

    Me.lblResult1.Caption = "At the end of " & Me.txtYears & " years" & vbCrLf _
        & "the total savings will be " & amt
End Sub
 
Hi Markk thanks for the help base on your advice I cleaned my code and now it works!! but I don't get the right answer, could you please help me?? Also how can I change the color of the letters in the result.??.Thank you!

Here's a starting place . . .
Code:
Private Sub cmdCalculate_Click()
    Dim i As Integer
    Dim amt As Currency
    
    amt = Me.txtAmount
    
    For i = 0 To Me.txtYears * 12
        amt = amt + (amt * (Me.txtInterest / 100 / 12))
    Next

    Me.lblResult1.Caption = "At the end of " & Me.txtYears & " years" & vbCrLf _
        & "the total savings will be " & amt
End Sub
 

Attachments

  • form1.jpg
    form1.jpg
    38 KB · Views: 123
  • 240CCA03.accdb
    240CCA03.accdb
    360 KB · Views: 97
Compound interest calculations do not require a loop.

Code:
result = (amount * (1 + annualrate/12)) ^ months
 
Hi Galaxium unfortunately I'm required to use loops, this is part of an assignment and my mean instructor is requiring us to use loops for this, if not I will get a 0 for this even if the answers are right. could you please help me?? :( we are also require to use this formula:
TotalVariable = TotalVariable + (TotalVariable * (RateVariable / 100 / 12))
Thanks
Compound interest calculations do not require a loop.

Code:
result = (amount * (1 + annualrate/12)) ^ months
 
In the example given, how many times is the FOR loop being cycled (looped) and how many times should it be cycled?
 
Hi, this is what I was told, I thought it was supposed to loop 3 times but this confused me:
  • To determine the value of compound interest, the following formula must repeat (loop) number of years * 12 (e.g. if it’s a 5 year mortgage, then 60 times; if it’s 10 years then 120 times etc.) TotalVariable = TotalVariable + (TotalVariable * (RateVariable / 100 / 12)) Since the years (and therefore the number of loops) is provided by the user, the For …Next loop

    In the example given, how many times is the FOR loop being cycled (looped) and how many times should it be cycled?
 
Hi, this is what I was told, I thought it was supposed to loop 3 times but this confused me:
  • To determine the value of compound interest, the following formula must repeat (loop) number of years * 12 (e.g. if it’s a 5 year mortgage, then 60 times; if it’s 10 years then 120 times etc.) TotalVariable = TotalVariable + (TotalVariable * (RateVariable / 100 / 12)) Since the years (and therefore the number of loops) is provided by the user, the For …Next loop


You only addressed half my question/suggestion. In the example provided, how many times is the FOR loop cycling (looping)?
 
The instructor didn't give us that information, he only said our form should look like the one in the example.
You only addressed half my question/suggestion. In the example provided, how many times is the FOR loop cycling (looping)?
 
here is a snip of my code with the change I tried and the database with the form as well essaytee,
hope you can help me, don't really know what to do
 

Attachments

here is a snip of my code with the change I tried and the database with the form as well essaytee,
hope you can help me, don't really know what to do

Why haven't you used the code that MarkR provided back in post #2? It was given to you on a plate albeit with one minor error which is what I was alluding to previously with non too subtle hints. (pls note, I'm not criticising MarkR, this type of error catches me out all the time.)

You claimed his code was giving the wrong answer but I can't see using MarkR's code, as is, could have given you the answer you claim. Something else must have been happening.

Anyway, on to your snippet of code you provided, the relevant part as follows:

Code:
   'set up loop
   Dim intI As Integer
   For intI = 1 To (txtYears * 12)
   sngTotal = sngTotal + (intTotal * (sngInterest / 100 / 12))
   Next

Ask yourself, what is the value of intTotal after every iteration? Re-check MarkR's code. Within the loop throw in a

Code:
 Debug.Print "intTotal = " & str(intTotal)

or for more information

Code:
debug.print "intI = " & str(i) & "  sngTotal = " & str(sngTotal) & "   intTotal = " & str(intTotal)


From the VBA Editor, turn on the immediate window, it's a great tool for debugging.

I noticed the snippet of code you provided also includes two or three other amounts to be compounded. For testing purposes I wouldn't worry about that aspect until you have a working version for one amount. Once that's achieved the rest is just a copy and paste.
 

Users who are viewing this thread

Back
Top Bottom