Interest Calculation - VBA

DJTRIN

New Member
Joined
May 28, 2013
Messages
6
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

vNO3nP0xC6YAAAAASUVORK5CYII=

Stop Value 1000

2Q==


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
 
Last edited by a moderator:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
I think your problem is here:
Code:
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.
 
Upvote 0
youHello, Thank you so much for reply.

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

Code:
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
Code:
 Running = mth + curbal 'checks to ensure Loop does not go pass the stop value
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,150
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top