Anyone here know the equivalent to PMT in Excel for VBSCRIPT .

I am trying to create a mortgage calculator this is what I have so far.
eteranAsked:
Who is Participating?
 
venkateshwarrCommented:

Sub pmt
    Set frmCalc = Document.MortgageMinder
    price = frmCalc.Price.Value
    dnpmt = frmCalc.DownPayment.Value
    prin = price - dnPmt
    intRate = (frmCalc.InterestRate.Value/100) / 12
    term = frmCalc.Term.Value * 12
    pmt = int((prin*intRate)/(1-(1+intRate)^(-1*term))*100)/100
End Sub
0
 
eteranAuthor Commented:
<script language="VBScript">
Sub CMDCAL_Click()
     Dim Property
     Dim payments
     Dim term
     Dim rate
     Dim year
     Dim man

     txtproperty.Focus
     Property = txtproperty.value
     txtpayments.Focus
     payments = txtpayments.value
     txtterm.Focus
     term = txtterm.value
     txtRate.Focus
     rate = txtRate.value
     txtyear.Focus
     year = txtyear.value
     txtman.Focus
     man = txtman.value
     If Property = 0 Then
          MsgBox "Please insert a loan value", vbCritical
          txtproperty.Focus
          Exit Sub
     ElseIf term = 0 Then
          MsgBox "Please insert a term value", vbCritical
          txtterm.Focus
     Exit Sub
     ElseIf rate = 0 Then
          MsgBox "Please insert a rate value", vbCritical
          txtRate.Focus
     Exit Sub

     Else
     property1 = -(Property - payments)
     term1 = term * 12
     monthly = Pmt(0.01 * rate / 12, term1, property1, 0, 0)
     txtmonthly.Focus
     txtmonthly.value = monthly
     Loan12 = (monthly + (year / 12) + man)
     txtloan.Focus
     txtloan.value = Loan12
     txtmtaxes.Focus
     txtmtaxes.value = year / 12
     Text25.Focus
     Text25.value = man
     txtfull.Focus
     txtfull.value = term1 * Loan12
     End If
End Sub
</script>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%">
  <tr>
    <td width="25%">Price of Property:</td>
    <td width="25%"><input type="text" name="txtproperty" size="14"></td>
    <td width="25%">Monthly payment of loan:</td>
    <td width="25%"><input type="text" name="txtmonthly" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Down Payment:</td>
    <td width="25%"><input type="text" name="txtpayments" size="14"></td>
    <td width="25%">Monthly Taxes:</td>
    <td width="25%"><input type="text" name="txtmtaxes" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Term:</td>
    <td width="25%"><input type="text" name="txtterm" size="3">Years</td>
    <td width="25%">Monthly Condo Fees:</td>
    <td width="25%"><input type="text" name="Text25" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Rate:</td>
    <td width="25%"><input type="text" name="txtrate" size="4">%</td>
    <td width="25%">Total Monthly Payments </td>
    <td width="25%"><input type="text" name="txtloan" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Yearly Taxes</td>
    <td width="25%"><input type="text" name="txtyear" size="14"></td>
    <td width="25%">&nbsp;</td>
    <td width="25%"><input type="text" name="txtfull" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Condo Fees</td>
    <td width="25%"><input type="text" name="txtman" size="14"></td>
    <td width="25%">&nbsp;</td>
    <td width="25%">&nbsp;</td>
  </tr>
</table>
<form method="POST" action="--WEBBOT-SELF--">
  <!--webbot bot="SaveResults" U-File="fpweb:///_private/form_results.csv" S-Format="TEXT/CSV" S-Label-Fields="TRUE" --><p>
  <input type="button" value="Submit" onclick="CMDCAL_Click()" name="B1"><input type="reset" value="Reset" name="B2">
  </p>
</form>
0
 
venkateshwarrCommented:
Hi eteran,

You wanted a mortage calculator...
I found one..

http://www.mcfedries.com/books/VBAUnleashed/ch21-12.asp

It also contains calculation for Pmt
check the source code.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
venkateshwarrCommented:
Pmt - Payment of annuity
0
 
eteranAuthor Commented:
Where do I plug that into my code
0
 
venkateshwarrCommented:
replace,
monthly = Pmt(0.01 * rate / 12, term1, property1, 0, 0)

with
monthly = int((property1*rate)/(1-(1+rate)^(-1*term1))*100)/100

0
 
eteranAuthor Commented:
and put the other code on top of the page.
0
 
venkateshwarrCommented:
no, forget about that.
just include

monthly = int((property1*rate)/(1-(1+rate)^(-1*term1))*100)/100

0
 
eteranAuthor Commented:
In the values it is given me some wired errors

<script language="VBScript">
Sub CMDCAL_Click()
     Dim Property
     Dim payments
     Dim term
     Dim rate
     Dim year
     Dim man

     txtproperty.Focus
     Property = txtproperty.value
     txtpayments.Focus
     payments = txtpayments.value
     txtterm.Focus
     term = txtterm.value
     txtRate.Focus
     rate = txtRate.value
     txtyear.Focus
     year = txtyear.value
     txtman.Focus
     man = txtman.value
     If Property = 0 Then
          MsgBox "Please insert a loan value", vbCritical
          txtproperty.Focus
          Exit Sub
     ElseIf term = 0 Then
          MsgBox "Please insert a term value", vbCritical
          txtterm.Focus
     Exit Sub
     ElseIf rate = 0 Then
          MsgBox "Please insert a rate value", vbCritical
          txtRate.Focus
     Exit Sub

     Else
     property1 = -(Property - payments)
     term1 = term * 12
    monthly = int((property1*rate)/(1-(1+rate)^(-1*term1))*100)/100    
     txtmonthly.Focus
     txtmonthly.value = monthly
     Loan12 = (monthly + (year / 12) + man)
     txtloan.Focus
     txtloan.value = Loan12
     txtmtaxes.Focus
     txtmtaxes.value = year / 12
     Text25.Focus
     Text25.value = man
     txtfull.Focus
     txtfull.value = term1 * Loan12
     End If
End Sub
</script>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%">
  <tr>
    <td width="25%">Price of Property:</td>
    <td width="25%"><input type="text" name="txtproperty" size="14"></td>
    <td width="25%">Monthly payment of loan:</td>
    <td width="25%"><input type="text" name="txtmonthly" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Down Payment:</td>
    <td width="25%"><input type="text" name="txtpayments" size="14"></td>
    <td width="25%">Monthly Taxes:</td>
    <td width="25%"><input type="text" name="txtmtaxes" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Term:</td>
    <td width="25%"><input type="text" name="txtterm" size="3">Years</td>
    <td width="25%">Monthly Condo Fees:</td>
    <td width="25%"><input type="text" name="Text25" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Rate:</td>
    <td width="25%"><input type="text" name="txtrate" size="4">%</td>
    <td width="25%">Total Monthly Payments </td>
    <td width="25%"><input type="text" name="txtloan" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Yearly Taxes</td>
    <td width="25%"><input type="text" name="txtyear" size="14"></td>
    <td width="25%">&nbsp;</td>
    <td width="25%"><input type="text" name="txtfull" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Condo Fees</td>
    <td width="25%"><input type="text" name="txtman" size="14"></td>
    <td width="25%">&nbsp;</td>
    <td width="25%">&nbsp;</td>
  </tr>
</table>
<form method="POST" action="--WEBBOT-SELF--">
  <!--webbot bot="SaveResults" u-file="fpweb:///_private/form_results.csv" s-format="TEXT/CSV" s-label-fields="TRUE" --><p>
  <input type="button" value="Submit" onclick="CMDCAL_Click()" name="B1"><input type="reset" value="Reset" name="B2">
  </p>
</form>
0
 
eteranAuthor Commented:
Numbersa
0
 
venkateshwarrCommented:
try this...

<script language="VBScript">
Sub CMDCAL_Click()
     Dim Property
     Dim payments
     Dim term
     Dim rate
     Dim year
     Dim man

     txtproperty.Focus
     Property = txtproperty.value
     txtpayments.Focus
     payments = txtpayments.value
     txtterm.Focus
     term = txtterm.value
     txtRate.Focus
     rate = txtRate.value
     txtyear.Focus
     year = txtyear.value
     txtman.Focus
     man = txtman.value
     If Property = 0 Then
          MsgBox "Please insert a loan value", vbCritical
          txtproperty.Focus
          Exit Sub
     ElseIf term = 0 Then
          MsgBox "Please insert a term value", vbCritical
          txtterm.Focus
     Exit Sub
     ElseIf rate = 0 Then
          MsgBox "Please insert a rate value", vbCritical
          txtRate.Focus
     Exit Sub

     Else
     property1 = (Property - payments)
     term1 = term * 12
    monthly = int((property1*rate)/(1-(1+rate)^(-1*term1))*100)/100    
     txtmonthly.Focus
     txtmonthly.value = monthly
     Loan12 = (monthly + (year / 12) + man)
     txtloan.Focus
     txtloan.value = Loan12
     txtmtaxes.Focus
     txtmtaxes.value = year / 12
     Text25.Focus
     Text25.value = man
     txtfull.Focus
     txtfull.value = term1 * Loan12
     End If
End Sub
</script>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%">
  <tr>
    <td width="25%">Price of Property:</td>
    <td width="25%"><input type="text" name="txtproperty" size="14"></td>
    <td width="25%">Monthly payment of loan:</td>
    <td width="25%"><input type="text" name="txtmonthly" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Down Payment:</td>
    <td width="25%"><input type="text" name="txtpayments" size="14"></td>
    <td width="25%">Monthly Taxes:</td>
    <td width="25%"><input type="text" name="txtmtaxes" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Term:</td>
    <td width="25%"><input type="text" name="txtterm" size="3">Years</td>
    <td width="25%">Monthly Condo Fees:</td>
    <td width="25%"><input type="text" name="Text25" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Rate:</td>
    <td width="25%"><input type="text" name="txtrate" size="4">%</td>
    <td width="25%">Total Monthly Payments </td>
    <td width="25%"><input type="text" name="txtloan" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Yearly Taxes</td>
    <td width="25%"><input type="text" name="txtyear" size="14"></td>
    <td width="25%">&nbsp;</td>
    <td width="25%"><input type="text" name="txtfull" size="14"></td>
  </tr>
  <tr>
    <td width="25%">Condo Fees</td>
    <td width="25%"><input type="text" name="txtman" size="14"></td>
    <td width="25%">&nbsp;</td>
    <td width="25%">&nbsp;</td>
  </tr>
</table>
<form method="POST" action="--WEBBOT-SELF--">
  <!--webbot bot="SaveResults" u-file="fpweb:///_private/form_results.csv" s-format="TEXT/CSV" s-label-fields="TRUE" --><p>
  <input type="button" value="Submit" onclick="CMDCAL_Click()" name="B1"><input type="reset" value="Reset" name="B2">
  </p>
</form>
0
 
eteranAuthor Commented:
The values that it out put are crazy
I am trying to atmortize a loan of 120000 30years 7.5 intrest

900000 loan payment. It is calculating somehtiing wroung
0
 
venkateshwarrCommented:
I am not familiar with american mortgage calculations... I just used the same formula provided at the link
http://www.mcfedries.com/books/VBAUnleashed/ch21-12.asp

The same calculation is working fine on this link..
Check it once.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.