Is someone able to convert to VBA?

[Shaun]

Hi All


I have spent quite some time trying to convert:

[pre]

min_rate = 0; max_rate = 100; # Set Maximum and minimum rate
while (min_rate < max_rate - 0.0001)
{
mid_rate = (min_rate + max_rate) / 2; # Divide by 2 to find midpoint
J = mid_rate / 1200; # Convert to monthly decimal percentage
# calculate payment based on this interest, term of F and loan_amt
guessed_pmt = loan_amt *   ( J / (1 - (1 + J) ** -N ));
if (guessed_pmt > actual_payment)
{
max_rate = mid_rate; # current rate is new maximum
}
else
{
min_rate = mid_rate; # current rate is new minimum
}
}
print " The Rate is ", mid_rate;

for use in VBA but I am just having no luck. What I have so far is:

Sub Interest_Rate()

Dim min_rate As Single
Dim max_rate As Single
Dim mid_rate As Single

Dim J As Single
Dim N As Single
Dim guessed_pmt As Single
Dim loan_amount As Single
Dim actual_pmt As Single

loan_amount = 32520
N = 48
min_rate = 0
max_rate = 100
actual_pmt = 583.25

Debug.Print loan_amount; N; min_rate; max_rate; actual_pmt

Do Until guessed_pmt = actual_pmt
mid_rate = (min_rate + max_rate) / 2

Debug.Print mid_rate

J = mid_rate / 1200

Debug.Print J

guessed_pmt = loan_amount * (J / (1 - (1 + J) ^ -N))

If guessed_pmt > actual_pmt Then

Debug.Print guessed_pmt; actual_pmt

max_rate = mid_rate
Else
min_rate = mid_rate
End If
Loop
ActiveSheet.Range("$G$9") = mid_rate * 1200
End Sub

[/pre]
But I just work out how to get it to work. If someone is able to take a look that would be great. It is meant to calculate the interest rate


Cheers


Shaun

 

[NARAYANK991]

Hi Shaun ,


Is this what you are looking for ?

[pre]

Public Sub Calculate_EMI()
Dim J As Double

loan_amount = 32520
N = 48
min_rate = 0
max_rate = 100
actual_pmt = 583.25

min_rate = 0
max_rate = 100                                ' Set Maximum and minimum rate

While (min_rate < max_rate - 0.0001)
mid_rate = (min_rate + max_rate) / 2    ' Divide by 2 to find midpoint
J = mid_rate / 1200                     ' Convert to monthly decimal percentage
'                calculate payment based on this interest, term of F and loan_amt
guessed_pmt = loan_amount * (J / (1 - (1 + J) ^ (-N)))
If (guessed_pmt > actual_pmt) Then
max_rate = mid_rate                  ' current rate is new maximum
Else
min_rate = mid_rate                  ' current rate is new minimum
End If
Wend
MsgBox " The Rate is " & mid_rate
Debug.Print guessed_pmt
End Sub

[/pre]
Narayan

 

[Shaun]

Hi Narayank991


Thank you for taking the time to re-code. It is exactly what I was after.


So, in theory (well at least in my head) I would run the code which would solve for the interest rate where the

guessed_pmt = actual_pmt, where the actual payment is $583.25. Once this was achieved I could take [code]mid-rate and enter it into cell G9 where I could then amortise the interest expense over the term of the agreement using the actuarial method of amortisation.


After running the code the [code]guessed_pmt ≠ [code]actual_pmt and I suspect this is because of a $10,000 balloon payment at the end of the agreement but I am not sure. I have also had a bit of a play with the formula in the range [code]C15:C61

to see if that is the cause the problem. i know it is part of the problem, but I have left in it's original state for anyone who would like to have a look.


So what should the answers be:


=sum(C15:C61)[/code] = $5,476.00

D61[/code] = $5,476.00

E61[/code] = $0.00

F61[/code] = $10,000.00


I will continue to have a bit of a play and see what I can come up with.


http://rapidshare.com/files/2713280740/Interest%20Amortiser.xlsm


Cheers


Shaun