Solved

ASP Formula for Excel Function

Posted on 2014-10-24
9
226 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
groupSumClump challenge 9 98
regex to extract ip:john 17 68
Passing a Text Box name to a Sub 6 71
Help with retrieving partial value from a column using VB.NET 4 39
If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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 clear a vector as well as how to detect empty vectors in C++.

932 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

11 Experts available now in Live!

Get 1:1 Help Now