ASP Formula for Excel Function

I need an ASP VB (Not VBA) for this complex Excel formula

=CEILING(((PMT(0.00020845,D5,D3))*(D5)+(D3))*(125)*-1,100)
Bizzuka ITAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Robert SchuttConnect With a Mentor Software EngineerCommented:
Though this EE question I found a VB formula on this page and was able to reproduce the result 11800:
<%

Function Ceiling(ByVal dNumber, ByVal dFactor)
   Ceiling = dFactor * Fix((dNumber + dFactor - 1) / dFactor)
End Function

' info from excel:
'PMT - payment for a loan based on constant payments and interest
'   Rate  - interest rate
'   nPer  - number of payments
'   PV    - present value
'   FV    - future value (or cash balance at end)
'   iType - type: payment at beginning of period = 1, at end = 0
Function PMT(Rate, nPer, PV, FV, iType)
    pmt = (-pv * (1 + rate) ^ nper - fv) * rate / ((1 + rate * itype) * ((1 + rate) ^ nper - 1))
End Function

D3 = 36000
D5 = 24
X = CEILING(((PMT(0.00020845,D5,D3,0,0))*(D5)+(D3))*(125)*-1,100)

Response.Write "X = " & X & "<br>"

%>

Open in new window

0
 
jkaiosIT DirectorCommented:
MsgBox Ceiling(99.1)

Public Function Ceiling(ByVal dNumber, Optional ByVal dFactor = 100)
 
   Dim dTemp
   dTemp = Fix(dNumber * dFactor)
   Ceiling = (dTemp + IIf(dNumber = dTemp, 0, Sgn(dNumber))) / dFactor
 
End Function

Open in new window

0
 
aikimarkCommented:
@jkaios

Are you going to post a PMT() routine?
http://office.microsoft.com/en-us/excel-help/pmt-HP005209215.aspx
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Bizzuka ITAuthor Commented:
The ceiling function was really the least of the issue I have.  The real issue I have is getting an ASP version to match the formula,  I tried using the PV() function, but I am not using that version of VB.  So if you plug 36000 into the amount and 24 for the term, Excel generates the result of 11800.  I tried using this formula but it is no where near the same results

Pmt=LoanAmount*0.00020845/(1-(1+0.00020845)^-LoanTerm)
    Pmt=Pmt * (LoanTerm+LoanAmount)
    Pmt=Pmt * 125*-1
0
 
aikimarkCommented:
The PV() function returns the present value, not the payment amount.

Have you tried adding IPmt() and PPmt() function values?
0
 
Bizzuka ITAuthor Commented:
I am using Classic ASP I believe both of these function are VBA, .NET.
0
 
aikimarkCommented:
They are part of the VB6 language, which should include classic ASP.
0
 
Robert SchuttSoftware EngineerCommented:
If you're always using a cash balance of 0 and a type of 0 then the function can be simplified:
Function PMT(Rate, nPer, PV)
    pmt = (-pv * (1 + rate) ^ nper) * rate / ((1 + rate) ^ nper - 1)
End Function

Open in new window

And you can use your original call:
X = CEILING(((PMT(0.00020845,D5,D3))*(D5)+(D3))*(125)*-1,100)

Open in new window

0
 
Bizzuka ITAuthor Commented:
Perfect!  Thank you
0
All Courses

From novice to tech pro — start learning today.