Solved

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

Posted on 2004-04-10
13
435 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
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 …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

13 Experts available now in Live!

Get 1:1 Help Now