Credit Card Function in Excel

I'm a hack begging for some help here. Any entrepenuers out there know if you have any abilities they will be abused when starting a company, such is my plight.

Currently, we waste a lot of time calculating accrued interest and total months paid on clients revolving accounts (Credit Cards) using a java script found on the web. Then imputing the values by hand.

Script follows:
//By Daniel C. Peterson
//Web Winder Website Services, 1997-2005 All Rights Reserved.
//Distribution, editing or reselling of this script is strictyly prohibited
//without expressed written permission from Daniel C. Peterson.
//For commercial grade (professional) versions of this and many other
//calculators, visit http://www.webwinder.com.

function computeForm(form) {

var i = form.interest.value;

if (i > 1.0) {

i = i / 100.0;

form.interest.value = i;

}

i /= 12;

var j = form.minpayperc.value;

if (j > 1.0) {

j = j / 100.0;

j = j * 1;

}





var prin = eval(form.principal.value);

var pmt = 0;

var prinPort = 0;

var intPort = 0;

var count = 0;

accruedInt = 0;

while(prin > 0) {
if(eval(prin * j) < eval(form.minpaydol.value)) {pmt = eval(form.minpaydol.value); } else { pmt = eval(j * prin); }
intPort = eval(i * prin);
prinPort = eval(pmt - intPort);
prin = eval(prin - prinPort);
accruedInt = eval(accruedInt + intPort);
count = count + 1
if(count > 600) { break; } else { continue;}
}

form.ccInt.value = accruedInt;
form.nPer.value = count;
form.years.value = count / 12;
}



function clearForm(form)

{

form.principal.value = "";

form.interest.value = "";

form.minpaydol.value = "";

form.minpayperc.value = "";

form.ccInt.value = "";

form.nPer.value = "";

form.years.value = "";

The reason I have not used Excel's built in functions: not dynamic (can't use minimum payment or minimum percent paid). Of course if any know how this can be accomplished using built-in functions that would be great.

I'd like to write a function or series of functions (after playing for a day at this it would seem the latter is more appropriate) that will dynamically calculate Accrued Interest and Total Months as the above script does sourcing Balance (Principle), Interest Rate, MinPay (Dollars) and MinPay (Percent) from cells in the work sheet. Thus far (only a couple hours work, I'm trying to learn VB simultaneously) my function script returns zeros .

What is submitted below was actually writen as a Sub, but I now realize that it will likely need to be written as two funtions a Dynamic Interest and Dynamic Periods (or Payoff) function.

Option Explicit
Public Function CCCalc1()

'Define All variables
Dim Prin As Double
Dim IntR As Double
Dim MinPay As Double
Dim MinPerc As Double
Dim Pmt As Double
Dim IntPort As Double
Dim PrinPort As Double
Dim AccruedInt As Double
Dim Count As Integer

'Input Known Variables Prin, Int, MinPay and MinPerc
Prin = Sheet1.Cells(2, 2)
IntR = Sheet1.Cells(2, 3)
MinPay = Sheet1.Cells(2, 4)
MinPerc = Sheet1.Cells(2, 5)

'Prepare Rates
If IntR > 1 Then IntR = IntR / 100
If MinPerc > 1 Then MinPerc = MinPerc / 100

'Create Do Loop for Payoff
Do While Prin < 0
If Prin * MinPerc < MinPay Then
Pmt = Prin * MinPerc
Else
Pmt = Prin * MinPerc
IntPort = (Prin * IntR)
PrinPort = (Pmt - IntPort)
AccruedInt = AccruedInt + IntPort
Count = Count + 1
End If
If Count > 600 Then Count = 600
Loop

'Create outputs
Sheet1.Cells(2, 5) = AccruedInt
Sheet1.Cells(2, 6) = Count
Sheet1.Cells(2, 7) = Count / 12

End Function


Any help will be greatly appreciated.

dbg

I've updated the problem with my second attempt
here .

Well, the advice I got here helped. So if you're interested check it out.

I didn't realize (but now it seems obvious) that the function itself was a variable needing definiting.

best of luck to all

Just in case you didn't notice, this is a forum for Visual Basic

I thought this VBA script would qualify. If there's no interest in this then I'm sure my thread will die of old age.

Your script doesn't resemble any VBA I've worked with. It looks like javascript.

Well, that was kinda the point, scrolling down gets what was my best effort to translate. I did mostly solve the problem and have written the following two VBA functions that do what I needed but not quite what I wanted. So additional help would be greatly appreciated.

Here is the function returns the number of periods (months) it will take to pay off a credit card paying the minimum payment:


Public Function CMPOP(ByVal Bal As Currency, ByVal Rate As Variant, ByVal MinDol As Currency) As Double
'CMPO count minimum pay-off periods
'Establish Variables
AccInt = 0
IRate = CDec(Rate * 1) / 12

Do
IntP = Bal * IRate
PrinP = MinDol - IntP
Bal = Bal - PrinP
AccInt = AccInt + IntP
Counter = Counter + 1
Loop While Bal > 0

CMPOP = Counter

End Function


This returns the accrued interest:

Public Function AIMPO(ByVal Bal As Currency, ByVal Rate As Variant, ByVal MinDol As Currency) As Double
'Accrued Interest of Minimum Pay-off
'Establish Variables
AccInt = 0
IRate = CDec(Rate * 1) / 12

Do
IntP = Bal * IRate
PrinP = MinDol - IntP
Bal = Bal - PrinP
AccInt = AccInt + IntP
Counter = Counter + 1
Loop While Bal > 0

AIMPO = AccInt

End Function


I still can't figure out how to get the IF THEN ELSE to work so that in the loop either the Minimum Monthly Payment, from MinDol or the Minimum Monthly Percentage, added as variable MinPerc. Bothe MinDol and MinPerc would be sourced from the cells.

I tried several variations of:


Public Function SelectMyPay(ByVal Bal As Currency, ByVal MinDol As Currency, _
ByVal MinPerc As Variant) As Double

'Establish Variables
Dim MinP As Variant
Dim MinD As Currency
MinP = CDec(MinPerc * 1) * Bal
MinD = MinDol

'Select Payment as either Percent or Dollar based
If MinP > MinD Then
Pay = MinP
Else
Pay = MinD
End If

SelectMyPay = Pay

End Function


In and out of the Loop but no success.

Thanks for any help,

DBG