Solved

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

Posted on 2004-04-10
13
456 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 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