Solved

ASP Formula for Excel Function

Posted on 2014-10-24
9
269 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
[X]
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
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
Industry Leaders: 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!

 
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

Industry Leaders: 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

Suggested Solutions

Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

738 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