Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

[David Boomer]

Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

Thanks Robin... but the XL example calculates the Payment and amount for interest (financeCharge)...
... what I need is to calculate the percent, given the correct parameters...
The key to my confusion here is the definition of 'correct parameters'

I think that my problem is that the calculated payment amount, Payment(), includes the payments for interest as well as principle. The payment amount, Payment(), is a function of FinancedAmount, FinanceRate, and the Term of the loan.
So... when the salesperson agrees to values of FinancedAmount, FinanceCost, Term, and MinimumMonthlyPayment an unsolvable problem arises because the FinaceCost will be determined by the Term and MinimumMonthlyPayment i.e. it cannot be arbitrarily agreed to independent of Term, and MinimumMonthlyPayment.

So... correct me if I'm wrong, the 'solution' must involve strict business rules that involve a renegotiation of the contract if the salesperson enters incorrect data on the sales agreement.
As Ray Lyons said in an earlier post in this chain...
"Finally, I would love to be wrong, i.e., that someone out there has a simple mathematical formula that will produce a 100% right APR answer."

[David Boomer]

Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

Below is the function that I now believe (hope) will return a value for the % of the loan given the FinancedAmt (Principle), The Term (months) and the Required MinimumMonthly Payment:

FUNCTION FinancePercent AS N (FinancedAmt As N, Months As N, MinMonPay As N )
dim CalcPayment As N
dim NextGuess As N
dim PaymentAt2Percent As N
dim PaymentAt50Percent As N
dim CostOfBorrowing As C

ui_msg_box("REMEMBER... There is only a narrow range of valid MinMonPay","REMEMBER... There is only a narrow range of valid MinMonPay that will satisfy values for Financed Amount & Term",UI_INFORMATION_SYMBOL+UI_OK)

PaymentAt2Percent = payment(FinancedAmt,.02/12,Months)
if PaymentAt2Percent > MinMonPay then
Response = ui_msg_box("Payment at 2% is > MinMonPay Entered","Payment at 2% is > MinMonPay Entered... DO NOT CONTINUE",UI_INFORMATION_SYMBOL+UI_OK_CANCEL)
if Response = UI_CANCEL_SELECTED then
end
end if
end if

PaymentAt50Percent = payment(FinancedAmt,.5/12,Months)
if PaymentAt50Percent < MinMonPay then
Response = ui_msg_box("Payment at 50% is still < MinMonPay Entered","Payment at 50% is still < MinMonPay Entered... DO NOT CONTINUE",UI_INFORMATION_SYMBOL+UI_OK_CANCEL)
if Response = UI_CANCEL_SELECTED then
end
end if
end if

Response = ui_msg_box("MinMonPay At 2% = " + alltrim(str(PaymentAt2Percent,18,2)) + " MinMonPay At 50 % = " + alltrim(str(PaymentAt50Percent,18,2)),"MinMonPay At 2% = " + alltrim(str(PaymentAt2Percent,18,2))+ " MinMonPay At 50 % = " + alltrim(str(PaymentAt50Percent,18,2)),UI_INFORMATION_SYMBOL+UI_OK_CANCEL)
if Response = UI_CANCEL_SELECTED then
end
end if

CalcPayment = 1
NextGuess = .01/12 '(ie 1%... the function uses the RATE/12, if the rate is per year... it doesn't use the % value)
'ui_msg_box("NextGuess = " + alltrim(str(NextGuess,18,4)),"NextGuess = " + alltrim(str(NextGuess,18,4)) + " TotalFinanced = " + alltrim(str(TotalFinanced)),UI_INFORMATION_SYMBOL+UI_OK)
'ui_msg_box("Months = " + alltrim(str(Months,18,4)),"Months = " + alltrim(str(Months,18,4)) + " MinMonPay = " + alltrim(str(MinMonPay)),UI_INFORMATION_SYMBOL+UI_OK)
'end
DIM GLOBAL pDlg1 as waitdialog
pDlg1.Set_Title("Please Wait")
pDlg1.Create(1,"Repeating")
pDlg1.Set_Message("Calculating Finance %...")
pDlg1.Set_Bottom_Message("")
while CalcPayment < MinMonPay
CalcPayment = Payment( FinancedAmt, NextGuess, Months )
'ui_msg_box("CalcPay = " + alltrim(str(CalcPayment)) + " Rate = " + alltrim(str(NextGuess,18,4)),"CalcPay = " + alltrim(str(CalcPayment)) + " Rate = " + alltrim(str(NextGuess,18,4)),UI_INFORMATION_SYMBOL+UI_OK)
NextGuess = NextGuess + 0.0000001
end while
pDlg1.Close()
CostOfBorrowing = alltrim(str(CalcPayment*Months-FinancedAmt,18,2))
ui_msg_box("COST OF BORROWING = " + CostOfBorrowing ,"COST OF BORROWING = " + CostOfBorrowing,UI_ATTENTION_SYMBOL+UI_OK)
FinancePercent = round(NextGuess * 1200,2)

END FUNCTION

If anyone can see serious problems with this please let me know.
Thanks!

[DaveM]

Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

"Finally, I would love to be wrong, i.e., that someone out there has a simple mathematical formula that will produce a 100% right APR answer."

I gave you a roll method above that will get you the apr in post #8. - did you try that??
It takes the amount financed and amount of interest and term to get the apr.
There is no way to calculate what yuou are asking 100% without knowing absolutely all of it.
Any financial wizard will tell you that a payment is only an answer based on other.

Your payment derived by salesmen is based on total of loan which is principal + interest without knowing the apr(annual percentage rate) so the one I gave you will give you that.

function - Payment() bases on yearly like 3 is 3 years so you have to divide by 12. the interest is expecting a decimal, but the real world is looking for something like 22.80% apr so it has to be modified a bit to get the accepted number. extending for a wrong calculation by a salesman in division of payment terms can cause the calculation to be wrong.
Comes in why I wrote my on payment on apr function where days to first is taken into account and where odd months like 69 is accepted and properly calculated.
example: If you need payments to start on the first of next month and you are 15 days away, that is about 1/2 month extra interest. Causes apr to change.

But any ways - Good Luck.

[David Boomer]

Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

Thanks Dave

Dave
Sadly... I am not ever going to be a 'Financial Wizard' but... I now realize that the FinanceCharge is DETERMINED by the term and the required minimum monthly payment... it cannot be arbitrarily entered along with term and minimum monthly payment (for a given FinancedAmount; if the salesperson wants a specific FinanceCharge then the term must change or the minimum monthly payment must change, or both) .

Re: your 'roll method'
I did look at your suggestion and I thought that the code I presented today at 9:26 AM incorporated the idea that you illustrated... am I missing something?

Thanks

[DaveM]

Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

Not sure if you are or not.
run both and see if the answers are the same?

if your salesman adds the finamnt and fincharge together and divides by 24 or 36 to get a payment, he should do fine. If he divides by a payment, it is much more complicated. Also, if he lets the wait 40(+-) days to start, it is more complicated.

[MoGrace]

Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

Ok, tried out the function with a small change to return 4 decimals instead of 2 and it returned 23.9277 which seems pretty accurate to me!

[DaveM]

Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

Hi Robin,

It is accurate for loans with first payment starting the same day, but will vary with days to first affecting it(if they are there).

[MoGrace]

Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

The rate would also change if using a 365 day year instead of 360 which apparently the payment() function uses. Rule of 78's didn't change the total interest paid, only the monthly amortized amount. Then there is whether the interest is to be calculated in advance or in arrears (normal) which also only changes the monthly interest and not the total interest.

I think he needs to find an ActiveX control... ;) But for what he is doing his function is probably sufficient.

[DaveM]

Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

I think he needs to find an ActiveX control... ;) But for what he is doing his function is probably sufficient

ActiveX is not needed, but you are correct. what he is doing is sufficient for his purpose. Will it stand any kind of scrutiny? NO!
Federal and state would issue a desist order for any tool used to derive that he is using and an order to comply to standards. He is in Canada and I do not know what regulations they have.
Understand, they don't care the method used to derive payment as long as it does not exceed legal rates, but if you state a rate, it has to be absolutely accurate within rounding rules.

This function would replace the alpha payment function and has been tested in all 50 states to be within rounding for car loans. It is accurate for other loans as well, but some types use completely different calculations.

FUNCTION pmtonapr AS N (p as n, r as n, mos as n, type as n, daystofirst as n)
'Description:p=loan amount,R=rate as 7.0, mos = monthly term, type = type of term, daystofirst=how many days to first payment due
if r <= 0.00 then
    pmtonapr = p/mos
else
dim df as n
dim rte as n = (r/100)/12
df = round_UP((daystofirst-30)/30,6)
dim trm as n = 0
dim formu as n
'dim factor as n 
var->factor = round_up((rte*df)*p,8) 'round_down() does not work in v7
formu = round_up(p+var->factor,5)
pmtonapr = round_down(pmts1(formu,r,mos),2)

end if

end function