Calculation of Minimum Payment on Form

[2tMonte]

I have created a form for credit card companies that holds the credit limit, available balance, balance, etc... One of the fields is the minimum payment due which is calculated by a formula devised by the credit card company. In addition to that, if the answer to the formula is below a certain number then the amount due is a default amount.

I have worked out the formula on the control source of the field so the minimum amount due is calculated automatically. In the case of one particular company, they take your balance, apply any interest, and then demand at least 3.5% of that amount so it looks like:

=(([AccountBalance]*0.02799)+[AccountBalance])*0.035

The balance on this particular account is $630.70 so $22.69 (rounded) populates.

My problem is figuring out how to enforce the $35 minimum required. The above formula works as long as the balance on the account is large enough to create $35 or more. But in the case of this balance ($22.69) the calculation is not sufficient to equal the minimum payment.

I tried a macro of:

If [Forms]![Billers]![MinPaymentDue] <35 Then
SetValue
Item=[Forms]![Billers]![MinPaymentDue]
Expression=35
End If

That did not work with an error of "You can't assign a value to this object"

In addition, I would want this macro to run automatically when the form is opened.



Any ideas?

[2tMonte]

To clarify, the minimum payment due is the formula or $35 whichever is the greatest. (Sorry)

[ssanfu]

To clarify, the minimum payment due is the formula or $35 whichever is the greatest. (Sorry)

So if my new balance is $25.33, I still have to pay $35???? WOW, I'm going to move to a different bank!!

OK,
If the new balance is <=$35 then
MinPayment = new balance
Else
min payment = $35

Another way of writing ([AccountBalance]*0.02799)+[AccountBalance]) is ([AccountBalance]*1.02799)


I created a table, "Account", with one field "Accountbalance". I entered a few amounts, both GT and LT $35.

Then I creates a query:

SELECT Account.AccountBalance, IIf(([AccountBalance]*1.02799)>=35,35,CCur(([AccountBalance]*1.02799))) AS CalcMinPay
FROM Account;

This is the result:

AccountBalance
CalcMinPay

$995.00
$35.00

$630.70
$35.00

$35.50
$35.00

$20.00
$20.56

$10.00
$10.28

$0.00
$0.00

In design view of the query, it looks like this:

CalcMinPay: IIf(([accountbalance]*1.02799)>=35,35,CCur(([accountbalance]*1.02799)))

I don't use macros, so I'm no help there......


Is this close to what you want??

[2tMonte]

Steve,

Yes, that is the right idea except the minimum payment may go well beyond $35 if the balance was high.

If the balance was $10,000 then the minimum payment would be $359.80 [.035($10k * 1.02799)]. But like in my example above, the balance is only $630.70 and the formula equates to $22.69 in which case the minimum has to default to $35 because the company will not accept any less.

I believe you can absolutely solve this because of your previous examples, I think you just have the minimum payment confused.

Thank you much for your help.

[ssanfu]

You have really tweaked my brain

How does this look:

Accountbalance
CalcMinPay
CalcUncorrected

$10.00
$10.28
0.3597965

$20.00
$20.56
0.719593

$35.50
$35.00
1.277277575

$630.70
$35.00
22.692365255

$995.00
$35.80
35.79975175

$10,000.00
$359.80
359.7965

And the SQL of the query is (wait for it, wait for it):

SELECT Account.AccountBalance, IIf(([accountbalance]*1.02799)<=35,CCur(round(([accountbalance]*1.02799),2)),IIf(([accountbalance]*1.02799)*0.035<=35,35,CCur(round((([accountbalance]*1.02799)*0.035),2)))) AS CalcMinPay, ([accountbalance]*1.02799)*0.035 AS CalcUncorrected
FROM Account
ORDER BY Account.AccountBalance;

And in query design view:

CalcMinPay: IIf(([accountbalance]*1.02799)<=35,CCur(round(([accountbalance]*1.02799),2)),IIf(([accountbalance]*1.02799)*0.035<=35,35,CCur(round((([accountbalance]*1.02799)*0.035),2))))

Ha! What do yo think now???

[2tMonte]

Steve,

I think that is awesome and exactly what I wanted. Now that I have tweaked your brain and beat my head against the desk, I can't use it. When I was devising my database design I did not think about each record having it own payment terms. So while your query works beautifully for Wells Fargo that calculates the 27.99% and demands a minimum of 3.5% paid, Citibank or Sears may have entirely different parameters. So, for each records in my forms, the formula would be different and specific to that individual record. I have tried queries, macros, properties within the record, etc... Apparently there is no way to do this so it is based on the categories of each record.

Thanks anyway, sorry to burden you, and glad I tweaked your brain; it's good for you.

[ssanfu]

You could still use most of it.
I don't know your table structure, but could you tie in the table for the different bank rate into the query above?
Another method is to write a UDF - pass the PK and the balance and have the code get the different rates and do the calcs.