Solved

ASP Formula for Excel Function

Posted on 2014-10-24
9
234 Views
Last Modified: 2014-10-27
I need an ASP VB (Not VBA) for this complex Excel formula

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

Expert Comment

by:jkaios
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

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
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

by:Bizzuka IT
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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 45

Expert Comment

by:aikimark
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

by:Bizzuka IT
ID: 40406237
I am using Classic ASP I believe both of these function are VBA, .NET.
0
 
LVL 45

Expert Comment

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

Accepted Solution

by:
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>"

%>

Open in new window

0
 
LVL 35

Expert Comment

by:Robert Schutt
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

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
 

Author Closing Comment

by:Bizzuka IT
ID: 40406457
Perfect!  Thank you
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Window…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

813 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

14 Experts available now in Live!

Get 1:1 Help Now