Calculating effective interest rate

Welcome to the board.

Shouldn't the rate be higher than 5% if the borrower has to pay the commission?

=RATE(60, -188.71, 10000*(1-1%))*12

I don't think I understand the transaction ...

Who is lending to whom, and who is paying a commission to whom?

Originally Posted by YoDadio

I am trying to calculate the effective rate [sic] on a loan after paying a commission given that:
Loan amount $10,000
Loan Term 60 Mo
Loan Contract Rate 5.00%
Loan Payment $188.71
Commission Rate 1.00%
Commission Amount $100.00
Total Interest $1,322.74
Net interest/income after commission $1,222.74
Through trial and error I was able to determine it is 4.635% (i.e., all things being equal a rate of 4.635% without a commission yields the same net income as 5.00% loan with a 1.00% commission)

There are a couple of issues with definitions.

First, when you say "effective" rate, apparently you mean rate "in effect" or the imputed rate, not "effective" (compounded) v. nominal (simple) rate. That's fine; I just want to clarify in anticipation of other responses.

Second, normally the commission is paid up-front, reducing the amount of the loan proceeds. Thus, the imputed rate is the APR as defined by US "truth in lending" regulations, which is the nominal (simple) annual IRR of the cash flows.

To that end, the APR would be about 5.414780%, which is calculated as follows:

Please Login or Register  to view this content.

The key is to use the full loan proceeds (10,000) to calculate the payment (the monthly cash flow), but to use the net loan proceeds (10,000 less commission; the initial cash flow) to calculate the APR.

I am inclined to think the APR is the "effective" rate you should be looking for.

-----

[ERRATA: See my subsequent posting, which is based on a clarifying posting that you submitted while I was writing this. I believe my subsequent posting should supersede the calculations below.]

However, you seem to define "effective" rate differently. I am not familiar with your definition; so I cannot say it is right or wrong.

You reduce the actual total interest by the commission, and the imputed rate is the rate for a loan that results in that total net interest.

For that definition, the imputed rate would be about 4.635217%, which is calculated as follows:

Please Login or Register  to view this content.

The derivation of the monthly payment is as follows (where "x" is the imputed rate to be determined):

Please Login or Register  to view this content.

We use 12*RATE to determine "x". We substitute the left-hand side for the right-hand side above, which is the monthly payment at annual rate "x".

This might be "much ado about nothing" because, again, it is unclear whether this is how "effective" rate should be defined.

You might be the best arbiter of that decision if you are a professional in the loan industry, so presumably you know what's right, or if this is the definition of "effective" rate that was given to you by an expert, for example an instructor.

[.... deleted ....]

ERRATA....

Originally Posted by YoDadio

I am paying the commission "out of pocket" to the vendor(s) that generate the sale/loan, consequently my income/profits are reduced by the commission. I am trying to determine the profitability of paying a higher commission (in the hopes of driving up volume).

Fair enough. But usually, the "effective" rate is the IRR of the cash flows, which takes "time value" into account.

If you are the lender and you, not the borrower, pay the loan broker's commission at the outset, the initial cash flow is the loan amount plus commission. The subsequent cash flows are the monthly payments based on the loan amount.

So the annual IRR would be about 4.590919%, calculated by:

=12*RATE(60,PMT(5%/12,60,-10000),-10000*(1+1%))

For simplicity and consistency, I assume the annual IRR should be the same as the APR. In the US, that is a nominal/simple rate, not compounded.

Originally Posted by YoDadio

Thanks Joeu2004! That is what I am looking for. I have no clue what/how/why it works but thanks.

I assume you are referring to the formula
=12*RATE(60,(PMT(5%/12,60,-10000)*60-10000*1%)/60,-10000).

I did explain the derivation in response #5. But I think I can do a better job.

The total interest for the original loan at 5% is PMT(5%/12,60,-10000)*60 - 10000.

Likewise, the total interest for a loan at x%, the "effective" rate to be determined, is
PMT(x%/12,60,-10000)*60 - 10000.

Obviously we cannot calculate that directly because we do not know "x".

But if we knew the value of PMT(x%/12,60,-10000), we would calculate x% with the formula =12*RATE(60,PMT(x%/12,60,-10000),-10000), substituting the value of PMT(x%/12,60,-10000).

You said the total interest at x% should be the total interest at 5% minus the commission, which is 10000*1%. So we know:

Please Login or Register  to view this content.

So the right-hand side is the value of PMT(x%/12,60,-10000). Substituting the right-hand side for
PMT(x%/12,60,-10000), the formula for x% becomes:

Please Login or Register  to view this content.

-----

But I still question the use of that definition of the "effective" rate, which is about 4.635217%.

Again, the annual IRR of the cash flows is about 4.590919%, calculated by:
=12*RATE(60,PMT(5%/12,60,-10000),-10000*(1+1%))

With that formula in A1, note that the total interest for a loan of 10000*(1+1%) would be:
=PMT(A1/12,60,-10000*(1+1%))*60 - 10000*(1+1%)

[ERRATA] Instead of saying "a loan of 10000*(1+1%)", I should say "the normal amortization of 10000*(1+1%)".

That value is about 1222.74, which is indeed the total interest of the original loan at 5% (about 1322.74) less commission (10000*1%), your net profit.

I have not decided in my own mind whether the last formula makes "good sense". But I think it's interesting that it results in your net profit.

GIGO? Food for thought.