Learning Do-Until Loops

[rickincanada]

Hi there! I'm trying to learn how to use VBA's Do-Until looping function to assist me in getting a payment rollback feature to work. I have a very simple workbook created that calculates a monthly payment based on the standard set of PMT Arguments. What I'm trying to do is get an input box that asks me what I want the monthly payment to be and then have it reduce or increase the Principal amount until it gets there.

Here's what I've written so far, however nothing happens after I enter my desired payment (NewPmnt) and click Yes.

Option Explicit

Sub PaymentRollback()
Dim NewPmnt As Variant
Dim OldPmnt As Double
'Range A4 is my Principal Amount

NewPmnt = InputBox("What do you want the payment to be?")
OldPmnt = Range("A6")

If Not IsNumeric(NewPmnt) Then
MsgBox ("You must enter a number!")
End If

If NewPmnt < 0 Then
MsgBox ("That's too low! Payment must be greater than 0.")

Do Until OldPmnt = NewPmnt
If NewPmnt > OldPmnt Then
Range("A4").Value = Range("A4").Value + 0.01
End If
Loop
Do Until OldPmnt = NewPmnt
If NewPmnt < OldPmnt Then
Range("A4").Value = Range("A4").Value - 0.01
End If
Loop
End If
End Sub

Thanks so much to anyone who can help. I'm really trying to learn this stuff on my own but this looping thing has me stuck!

 

[Norie]

Why do you have Exit Do?

With that you'll get 1 iteration and that's it, just remove it.

By the way how many iterations are you expection?

I changed the 0.01 to 1 and on each iteration the payment only seems to change by +/- 0.02.

I changed it to 10 and it was only +/-0.2.

 

[rickincanada]

No, we should always be able to arrive within a penny. Do you think you can (or are you willing to) help me through this?

 

[rickincanada]

Ooops sorry about the Exit Do, forgot to take that out. Yeah, there is definitely a better way to go than by 0.01. I'm so new to this though I'm not sure exactly how best to approach it.

 

[Norie]

Rick

I can help you with the code but not the calculation, and I think the calclation might be more important than the code.

Actually I was going to suggest you try GoalSeek or something similar.

There are also quite a few financial functions in Excel that you might be able to use.

If you really want code then you might be able to use those via Application.WorksheetFunction.

 

[rickincanada]

Ok, so I think I've got a working formula to get us there in good time (reasonable number of iterations). However where I'm stuck now is that the following just hangs up. It won't land on a number. This is where I need your help.

 

[rickincanada]

Oops, forgot the code

Option Explicit

Function Payment(P As Double, J As Double, N As Double) As Double
Payment = P * (J / (1 - (1 + J) ^ -N))
End Function


Sub PaymentRollback()
Dim P As Double
Dim J As Double
Dim N As Double
Dim AdjP As Double
Dim K As Double
Dim Pmnt As Double
Dim x As Double

P = Range("$A$2") '20000
J = Range("$D$2") '0.002083333
N = Range("$C$2") '60
'Pmnt = Payment(P, J, N) 'When this function is called on the worksheet with the above argument the result is 354.95
K = InputBox("What do you want the payment to be?")

If Not IsNumeric(K) Then
MsgBox ("You must enter a number!")
End If

If K < 0 Then
MsgBox ("That's too low! Payment must be greater than 0.")
End If

Do Until Pmnt = K
Pmnt = Payment(P, J, N)
x = (Pmnt - K) * N
P = P - x

Range("$A$2").Value = P
Loop

End Sub

 

[rickincanada]

It works to near perfection with the exception that when I try to get to 399.00 as my desired payment it hangs.

 

[Norie]

Rick

This is why suggested using less/more than or equal to.

I actually wrote the code, well part of it anyway, but it crashed Excel.

Well it didn't really crash it but it got stuck in some sort of infinite loop.

I'll have another look later.