Loan Calculator 1

[ralphtrent]

Hi
I need the formula that will calculate month payments for a mortgage in vbscript.

Thanks
Ralph

 

[PHV]

Doesn't have Excel some builtin formulas ?
You may instantiate an invisible Excel.Application that will do the job for you.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ralphtrent]

I didn't see anything that did this.

 

[PHV]

No one in the Financials WorkSheet's function ?

 

[ralphtrent]

nothing that I see that is what i am looking for. I am using excel 2002. Unless it is labeled as something that I am not thinking of. What i want to see is the monthly payment on a loan given teh terms of the loan.

 

[PHV]

Take a look at

http://www.efunda.com/formulae/finance/loan_calculator.cfm

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

And the Excel function is PMT I guess:
ActiveCell.FormulaR1C1 = "=PMT(7.5%/12,60,20000)"
? ActiveCell.Value
-400,759

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

ralphtrent, thanks for sharing.
Have you solved your issue ?
If so can you please explain the members how ?

 

[ralphtrent]

I just used the PMT function in excel. Not the way I want to go about doing it, but it works for now.

 

[markdmac]

I found a javaScript example here:

http://www.firstokmulgee.com/loancalc.htm

Found the same script at a dozen other locations. Does not seem to be an easy way to do it with VBSCRIPT alone apparently.

I hope you find this post helpful. Please let me know if it was.

Regards,

Mark

 

[mapman04]

Here a script that will calculate the monthly payment. It produces the same value as the PMT function in Excel.

mapman04

<------------------->
Dim M 'Monthly Payment
Dim P 'Principal
Dim N 'Term in months
Dim I 'Interest Rate
Dim strT 'Title
Dim intA 'Counter
Dim A 'Amortization Rate
Dim U
Dim C
Dim D
Dim B

strT = "Mortgage Calculator"

P = InputBox("Enter Principal",strT)
L = InputBox("Enter Term in Years",strT)
I = InputBox("Enter Rate (1 to 100)",strT)

J = (I/100)
N = L * 12
A =(1+((J)/12))
intA = Cint(1)

Do While intA <= Cint(N + 1)
If intA = 1 Then
B = A / A
Else
B = B / A
End If
intA = intA + 1
Loop

U = 1 - B

C = A - 1

D = C/U

M = P * D

MsgBox "Monthly Payment: " & Round(M,2),64,strT
WScript.Quit

 

[markdmac]

mapman, thanks for posting that. Out of curiosity, what are U, C, D and B?

I hope you find this post helpful. Please let me know if it was.

Regards,

Mark

 

[mapman04]

I just used some variables when I was debugging. The formula I used is:
-N
M = P * (A - 1) / (1 - A )

A = amortization factor: (1 + (J / 12))

Sorry if it's a little confusing.

mapman04

 

[ralphtrent]

Mapman04 exactly what I was looking for.

Thanks