Solved

# HELP HELP CALCULAYE PAYMENT PER MONTH

Posted on 2000-03-19
211 Views
AzraSound is it possible to email you project to understand??

WE are currently having troubles with this code.

Need Help debugging PMT function.

// Here is code used

Option Explicit
Dim intTval As Integer

Private Sub cmdCalc_Click()

Dim dblPval As Double, dblIval As Double, dblPayment As Double
'call function to determine # of payments

dblPval = txtPrincipal.Text
dblIval = Val(lstInterest().Text) / 10

If optTerm(0).Value = True Then
intTval = 24
End If

If optTerm(1).Value = True Then
intTval = 36
End If

If optTerm(2).Value = True Then
intTval = 48

Else
intTval = 60

End If

'formula for the payment total
' dblPayment = Pmt(dblIval / 12, intTval, dblPval)
'dblPayment = Pmt(dblIval / 12, dblPval, intTval)
lblPayment.Caption = dblPayment

txtPrincipal.SelStart = 0
txtPrincipal.SelLength = Len(txtPrincipal.Text)

End Sub

frmMthlyPay.Left = (Screen.Width - frmMthlyPay.Width) / 2
frmMthlyPay.Top = (Screen.Height - frmMthlyPay.Height) / 2

txtPrincipal.SelStart = 0
txtPrincipal.SelLength = Len(txtPrincipal.Text)

lstInterest.ListIndex = 14      ' assign default values
optTerm(2).Value = True

End Sub

Private Sub mnuFileExit_Click()

End Sub

Private Sub mnuFilePrint_Click()
cmdCalc.Visible = False
PrintForm
cmdCalc.Visible = True

End Sub

Private Sub mnuFormatBackground_Click()

dlgMonth.Flags = cdlCCRGBInit
dlgMonth.Color = txtPrincipal.ForeColor
dlgMonth.ShowColor
frmMthlyPay.BackColor = dlgMonth.Color

End Sub

Private Sub mnuFormatFont_Click()
dlgMonth.Flags = cdlCFScreenFonts

dlgMonth.FontName = lblPayment.FontName
dlgMonth.FontBold = lblPayment.FontBold
dlgMonth.FontItalic = lblPayment.FontItalic
dlgMonth.FontSize = lblPayment.FontSize

dlgMonth.ShowFont

lblPayment.FontName = dlgMonth.FontName
lblPayment.FontBold = dlgMonth.FontBold
lblPayment.FontItalic = dlgMonth.FontItalic
lblPayment.FontSize = dlgMonth.FontSize

End Sub

Private Sub mnuFormatInfo_Click()

dlgMonth.Flags = cdlCCRGBInit
dlgMonth.Color = txtPrincipal.ForeColor
dlgMonth.ShowColor
fraInfo.BackColor = dlgMonth.Color

End Sub

Thanks Slammer 14

0
Question by:slammer14
• 7
• 3
• 2

LVL 28

Expert Comment

ID: 2634905
Maybe you need to use the Val function when doing dblPval = txtPrincipal.Text

0

LVL 28

Expert Comment

ID: 2634908
Also, why no index specified in dblIval = Val(lstInterest().Text) / 10  ??
0

Author Comment

ID: 2634948
Edited text of question.
0

LVL 28

Expert Comment

ID: 2634951
you can email me if youd like
AzraSound@aol.com
0

LVL 14

Expert Comment

ID: 2634956
Here is a function to calculate Total Payment Amount. The syntax to use it is (rounding the result to 2 decimal positions)..

Dim curReturnValue as Currency
curReturnValue = Round(xTotalPaymentAmount(curPrinciple, dblAnnualInterestRate, intMonths), 2)

Eg.

curReturnValue = Round(xTotalPaymentAmount(10000, 7.5, 48), 2)

<----- Code Begin ----->

Private Function xTotalPaymentAmount _
(ByVal curPrinciple As Currency, dblAnnualInterestRate As Double, intMonths As Integer) _
As Currency

xTotalPaymentAmount = curPrinciple _
* ((1 + (dblAnnualInterestRate / 100)) _
^ (CDbl(intMonths) / 12))

End Function

<----- Code End ----->
0

LVL 28

Accepted Solution

AzraSound earned 100 total points
ID: 2635142
If optTerm(0).Value = True Then
intTval = 24
End If

If optTerm(1).Value = True Then
intTval = 36
End If

If optTerm(2).Value = True Then
intTval = 48

Else
intTval = 60

End If

Syntax error:
Probably wouldnt have happened had you used Select Case instead. Notice your last if else structure.  If someone chose optTerm(1) for example then the function still runs through to the next if which would be false of course, but your else statement makes everything you enter 60 payment periods no matter what!!  Careless errors are my downfall too, no worries.  Also, i think for what you are doing you will need to set your dblPval to negative in the Pmt function (youre doing a loan yes?)
0

LVL 14

Expert Comment

ID: 2635200
Azrasound:
Although you have contributed greatly, this puppy is hardly done.. hence a comment may have been a little more appropriate.. <wink>.  Yes.. this is a loan calculator.. but no, dblPval should not be negative as it is customary to show all numbers in there positive form.. <smile>.

------------------------------------

Yup.. he's got his share of bugz..  He's dividing his Interest rate by 10, rather than 100.. he probably should be using comboboxes rather than listboxes, so that the user can enter Interest Rates and / or Payment periods not listed.. his PMT function is compounding Monthly, rather than annually (but I suppose that may be ok).

The raw empiricism for calculating Total Payments based on an APR (Annual Percentage Rate) is in the function I included above. It accepts the annual percentage rate as most people key them ie. 7.50%. If you want the Monthly Payment amount, just divide the Total Payment Amount calculated by the number of Months.. <smile>.
0

LVL 28

Expert Comment

ID: 2635226
actually in the way that the Pmt function is structured, the dblPval does need to be negative in accordance with the optional paramter 'fv' that when left out defaults to 0.  fv tells us what the final amount should be after the last payment, and this being a loan, the values being added need to equal 0, thus requiring an initial value to be negative.
0

LVL 28

Expert Comment

ID: 2635233
wsh2,
I think the confusion here is the fact that you are creating your own function and they are using the built in Pmt function   =)
0

Author Comment

ID: 2635306
Adjusted points from 50 to 100
0

Author Comment

ID: 2635307
Personable and Informative help..!
0

LVL 28

Expert Comment

ID: 2635321
I thought it almost got too personal after that Victoria comment...but thats neither here nor there  =oP   take care
0

## Featured Post

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no oâ€¦
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most pâ€¦
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applicâ€¦
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can launâ€¦