Pmt Function - heed assistance

[MissouriTiger]

I would be grateful if someone would help me with at least one of the following dilemmas. I'm listing them in order of importance.
1. I've spent hours trying to get this right. What am I doing wrong, why am I not getting the right answer? I'm lost. This is my first time using the Pmt Function. Here's my code for a loan paymment calculator:

Dim dblIntRate As Double, dblPrincipal As Double, intTerm As Integer


dblIntRate = 7 + (lstInterest.ListIndex * 0.25) 'Saves value of interest rate selected by user
dblPrincipal = Val(txtPrincipal.Text) 'Saves value of loan
Select Case True
'User selects optionbutton for loan period in months
Case optTerm(0).Value
intTerm = 24
Case optTerm(1)
intTerm = 36
Case optTerm(2)
intTerm = 48
Case optTerm(3)
intTerm = 60
End Select

lblPayment.Caption = Pmt(Rate:=dblIntRate / 12, NPer:=intTerm, PV:=dblPrincipal)



2. Isn't there some way to determine which Option button is selected, incorporated right into a variable initialization statement, rather than with a selection structure (as above). I tried the following syntax to no avail:

intTerm = 24 + (optTerm(Index) * 12)

OptTerm is the name of my Option button Array. It seems like a sensible statement to me, but I keep getting an error and it's telling me that "Index" is an undeclared variable (due to Option Explicit). What the?!?!?!?!
Any suggestions?



[sig][/sig]

 

[Collin]

For your first question;
Dim dblIntRate As Double, dblPrincipal As Double, intTerm As Integer


dblIntRate = 7 + (lstInterest.ListIndex * 0.25) 'Saves value of interest rate selected by user
The listindex property represents the index of the array of strings in your ListBox control. The List property is the actual array. so you may want to change this to
dblIntRate = (lstInterest.List(lstInterest.ListIndex) + 7)/12
What you have works, I just wanted to make sure you understood this.

dblPrincipal = Val(txtPrincipal.Text) 'Saves value of loan
No problem here

Select Case True
'User selects optionbutton for loan period in months
Case optTerm(0).Value
intTerm = 24
Case optTerm(1)
intTerm = 36
Case optTerm(2)
intTerm = 48
Case optTerm(3)
intTerm = 60
End Select
You need to loop through the array of Option buttons useing a For Next loop to determine which Option button is selected. Assign the months that each Option button represents to the Tag property of that Option button.
Dim itr as Integer
For itr = 0 to optTerm.Ubound
If opTerm(itr).Value = True
intTerm = Val(optTerm(itr).Tag)
End If
Next itr
Now this is just one way to do it, You could make the intTerm a Global variable and use the optTerm_Click Event to set it.
Private Sub OptTerm_Click(Index As Integer)
intTerm = optTerm(Index).Tag
End Sub
OR look at the answer to your second question

lblPayment.Caption = Pmt(Rate:=dblIntRate / 12, NPer:=intTerm, PV:=dblPrincipal)
Your useing an optional way to pass Arguments to a function and also making claculations within the arugement list, not the best thing to do in my opion I would change thid to;
lblPayments.Caption = Pmt(dblIntRate,intTerm,dblPrincipal)

To answer you second question
intTerm = 24 + (optTerm(Index) * 12)
Doesnt work for a couple of reasons, "Index" outside of an Event of a Control that is arrayed is just another Variable,
The default property of the Option Button control is its Value property, so
24 + (optTerm(0) * 12 is actualy saying
24 + (optTerm(0).Value * 12
the Value property is a boolean, True or False or -1 for true and 0 for False so the only two results you could get are
24 + (-1* 12)
or 24 + (0 * 12)
This would work though
Dim itr as Integer
For itr = 0 to optTerm.Ubound
If opTerm(itr).Value = True
intTerm = 24 + (itr * 12)
End If
Next itr
[sig][/sig]

 

[dpaulson]

Try this
lblPayment.Caption = Pmt((dblIntRate/100) / 12,intTerm,-dblPrincipal)

I am assuming that you Interest rate is not in a decimal form. eg 8.9% and not 0.089.(this is why I divided by 100)
and that this is a loan (principle has been negated).

There is also other options such as future value(0 is assummed if not included) and if payment at first on month or end of month( first of month assummed if not included in th formula)
[sig]<p>David Paulson<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[Bachenot]

I took a different tack, using the MSDN example, which uses input boxes.

I also converted the length of the loan to years, which might be easier for the user than months.

Also note that the payment in the Pmt function is negative because it is an accounting debit. I multiplied it by -1 to convert it to a positive number.

Here's the code:

Option Explicit

Dim Fmt As String
Dim Years As Single
Dim dblPrincipal As Double
Dim dblIntrate As Single
Dim intTerm As Single
Dim PayType As String
Dim Payment As Double
Const ENDPERIOD = 0, BEGINPERIOD = 1 ' When payments are made.

Private Sub Form_Load()
Fmt = &quot;###,###,##0.00&quot; ' Define money format.
dblPrincipal = InputBox(&quot;How much do you want to borrow?&quot
dblIntrate = InputBox(&quot;What is the annual percentage rate of your loan?&quot
If dblIntrate > 1 Then dblIntrate = dblIntrate / 100 ' Ensure proper form.
Years = InputBox(&quot;Select Years of Loan between 2 years and 5 years&quot
intTerm = Years * 12
PayType = MsgBox(&quot;Do you make payments at the end of month?&quot;, vbYesNo)
If PayType = vbNo Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
Payment = (Pmt(dblIntrate / 12, intTerm, dblPrincipal, PayType)) * -1
MsgBox &quot;Your monthly payment is &quot; & Format(Payment, Fmt) & &quot;. &quot;
End Sub
[sig][/sig]

 

[MissouriTiger]

Thanks to you all! You've been a big help!