Solved

# ASP Formula for Excel Function

Posted on 2014-10-24
219 Views
I need an ASP VB (Not VBA) for this complex Excel formula

=CEILING(((PMT(0.00020845,D5,D3))*(D5)+(D3))*(125)*-1,100)
0
Question by:Bizzuka IT
• 3
• 3
• 2
• +1

LVL 12

Expert Comment

ID: 40403635
``````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
``````
0

LVL 45

Expert Comment

ID: 40403919
@jkaios

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

Author Comment

ID: 40406194
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

LVL 45

Expert Comment

ID: 40406225
The PV() function returns the present value, not the payment amount.

Have you tried adding IPmt() and PPmt() function values?
0

Author Comment

ID: 40406237
I am using Classic ASP I believe both of these function are VBA, .NET.
0

LVL 45

Expert Comment

ID: 40406251
They are part of the VB6 language, which should include classic ASP.
0

LVL 35

Accepted Solution

Robert Schutt earned 500 total points
ID: 40406401
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>"

%>
``````
0

LVL 35

Expert Comment

ID: 40406416
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
``````
And you can use your original call:
``````X = CEILING(((PMT(0.00020845,D5,D3))*(D5)+(D3))*(125)*-1,100)
``````
0

Author Closing Comment

ID: 40406457
Perfect!  Thank you
0

## Featured Post

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This video teaches viewers about errors in exception handling.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.