Solved

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

Posted on 2004-04-10
13
449 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
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 Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

776 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