Interest Calculation - VBA

[DJTRIN]

Hello I need some help to create a VBA code to ;


· User enter a End Value
· User enter monthly amount
· User enters rate (Currently this is hard coded but ideally user should enter it)
· Start date


I need to calculate interest every June and Dec interest the add the interest to current balance

When Cur balance is = stop or as soon as it it > than stop any interest calc and store value

Example below

Stop Value 1200

Stop Value 1000

This is my code thus far

Private Sub CommandButton1_Click()

Dim Avg As Long
Dim Count As Long
Dim i As Integer
Dim Total As Long
Dim mth As Long

Dim stp As Long
Dim curbal As Long
Dim monthly As Long


i = 2
Count = 0
monthly = Cells(1, 2).Value

stp = Cells(1, 1).Value
curbal = Cells(i, 1).Value

Count = 0


Do Until Count = 6 Or Running > stp

Count = Count + 1
mth = monthly + mth
MsgBox mth
Total = Total + (monthly * Count)

Loop

Running = mth + curbal
Avg = (((Total / 6) * 0.025) / 12) * 6 + Running
Cells(i, 2).Value = Avg
Cells(i, 3).Value = Count
MsgBox Avg



End Sub

I also tried using If statements but no luck I can also post that code.


EDIT:
Stop 1000
https://1drv.ms/u/s!Ahv8cig51I35hGR1XkcmwCley2qx

Stop 1200
https://1drv.ms/u/s!Ahv8cig51I35hGKQc18a-R4Ml8ZX

 

[Johnny C]

I think your problem is here:

Do Until Count = 6 Or Running > stp

Count = Count + 1
mth = monthly + mth
MsgBox mth
Total = Total + (monthly * Count)

Loop

It will go to 6 every time, because you aren't updating 'Running' so if stp is exceeded at say the 4th iteration it will carry on anyway, the Running>stp condition will never be true.

 

[DJTRIN]

youHello, Thank you so much for reply.

I am still building my code. I am new to this and I have encouter another challenge

Private Sub CommandButton1_Click()

Dim Avg As Long
Dim Count As Long
Dim i As Integer
Dim Total As Long
Dim mth As Long

Dim stp As Long
Dim curbal As Long
Dim monthly As Long


i = 2
Count = 0
monthly = Cells(1, 2).Value ' monthly payments
stp = Cells(1, 1).Value 'stop calculating when Avg is = to > than this value
curbal = Cells(i, 1).Value 'current saving balance

Do Until Avg >= stp
MsgBox Avg
Count = 0

Do Until Count = 6 Or Running = stp 'To increment the monthly values then calculate_
'the interest at month 6 and does not go pass the stop value

Count = Count + 1
mth = monthly + mth 'mth represents the total monthly payments
Total = Total + (monthly * Count) ' Total is used to store the balance
Running = mth + curbal 'checks to ensure Loop does not go pass the stop value
Loop
 
Running = mth + curbal ' Running is
Avg = (((Total / 6) * 0.025) / 12) * 6 + Running
Cells(i, 2).Value = Avg
Cells(i, 3).Value = Count
Running = Avg
Loop

End Sub

Using 1200 as the stp value
100 and the monthly value

In the first loop the avg is 604.

However when the second lop starts i want leave 604 as the running balance but running will be reset to

 Running = mth + curbal 'checks to ensure Loop does not go pass the stop value

 

[DJTRIN]

I got through.. thanks again.