ASP Formula for Excel Function

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

Question by:Bizzuka IT
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Open in new window

LVL 46

Expert Comment

ID: 40403919

Are you going to post a PMT() routine?

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=Pmt * (LoanTerm+LoanAmount)
    Pmt=Pmt * 125*-1
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

LVL 46

Expert Comment

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

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

Author Comment

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

Expert Comment

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

Accepted Solution

Robert Schutt earned 2000 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

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


Author Closing Comment

by:Bizzuka IT
ID: 40406457
Perfect!  Thank you

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
Suggested Courses

777 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