Solved

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

Posted on 2004-04-10
13
440 Views
Last Modified: 2008-02-01
I am trying to create a mortgage calculator this is what I have so far.
0
Comment
Question by:eteran
  • 7
  • 6
13 Comments
 

Author Comment

by:eteran
ID: 10797605
<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
 
LVL 12

Expert Comment

by:venkateshwarr
ID: 10797896
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
 
LVL 12

Accepted Solution

by:
venkateshwarr earned 500 total points
ID: 10797900

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
 
LVL 12

Expert Comment

by:venkateshwarr
ID: 10797933
Pmt - Payment of annuity
0
 

Author Comment

by:eteran
ID: 10798034
Where do I plug that into my code
0
 
LVL 12

Expert Comment

by:venkateshwarr
ID: 10798137
replace,
monthly = Pmt(0.01 * rate / 12, term1, property1, 0, 0)

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

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:eteran
ID: 10798184
and put the other code on top of the page.
0
 
LVL 12

Expert Comment

by:venkateshwarr
ID: 10798216
no, forget about that.
just include

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

0
 

Author Comment

by:eteran
ID: 10798259
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
 

Author Comment

by:eteran
ID: 10798344
Numbersa
0
 
LVL 12

Expert Comment

by:venkateshwarr
ID: 10798442
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
 

Author Comment

by:eteran
ID: 10798624
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
 
LVL 12

Expert Comment

by:venkateshwarr
ID: 10798687
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now