FASB 91 - Need help with calculating

[n2nhl9]

I've been searching online to find a way to use excel to calculate the effective APR for some loans that have the wrong effective APR. We do auto lending and pay origination fees to dealers, yet I can't find anything that would give me the formulas to use to get the right rate. Here's an example of what I'm trying to do.

Contract date: 5/30/11
1st pymt: 7/14/11
Payment: $432.91
# pymts: 72
Interest rate: 7.49%
Unamortized fee: -249.67
Effective apr: 7.136%

My question is what would I need to enter into excel in order for it to give the the effective APR of 7.136%?

Thanks!

 

[ryancgarrett]

I think the function you are looking for is effect. It takes the annual rate and number of compounds per year and returns the effective rate.

=EFFECT(nominal_rate,compounds_per_year)

 

[n2nhl9]

I've tried the effect formula, but it does not calculate the effective APR in the example above. The rate always comes out higher and does not take into account the origination costs associated with the loan. Perhaps it is effect, but I don't know what to input to make it calculate it correctly?

Can anyone confirm this is the correct formula? If so, what do I need to enter in the formula to get it to correctly give a "lower" effective rate?

Thanks!

 

[xenou]

Do you know the amount of the loan? How did you get the answer of 7.136 in the first place?

 

[n2nhl9]

I guess that would be of some use, no? LOL

It's $24,966.98.

 

[xenou]

It looks like the fees of -249.67 are the reason the "effective rate" is different - it must be netted against the principal or amortized against the life of the loan. It's hard to say more though - I'm not sure what the difference is between contract date and start date or what's going in with the interest during that period. I'm assuming this is monthly payments, but it's hard to arrive at precisely the same numbers. How do you know that 7.136 is the correct answer? Where did that number come from?

 

[MikeWx]

Using the PMT function, I calculated the following monthly payment:

PMT(7.49%/12,72,-24966.98) = 431.56

Your initial outlay is -24966.98 - 249.67 (the fee) = -25216.65

Your APR is the Internal Rate of Return of -25216.65 and 72 positive amounts of 431.56 each. If the initial outlay is in Cell B3 and the payments are in B4:B75, your APR formula is =IRR(B3:B75,.1/12). My result is 7.1355%.

 

[n2nhl9]

I will give that a try in the morning to see if I can get several other examples to come out the same. With rounding it just be might right!

BTW, PP asked where the effective APR came from and it was done by a computer program that automates this for us. The problem is we have several loans that had to be modified and therefore the computer effective APR is no longer valid.

I'll post if this was successful or not.

Thanks for the reponse! It's very much appreciated.

 

[n2nhl9]

Alas, I could not replicate what you get for the effective APR of 7.136%.

Here's what I have.
B3 = Initial outlay -25216.65

B4-B75 = 431.56 (Does this need to be done? I don't know of an easier way to get the payments to auto fill in the cells when the payment is calculated. I ended up coping the payment 72 times)

C 1 = IRR(B3:B75,0.1/12) = 0.5946%

Is this correct?

Thanks!

 

[xenou]

Strange. I get the same result - .0059% (with =IRR(B3:B75,0.1/12) in B2, -25216.65 in B3 and 431.56 in cells B4:B75). Excel can be maddening sometimes.