APR Rate on a Loan

[Union70]

Can anyone help me with this?
I'm trying to find the Interest Rate of a loan if I'm given Original
amount of loan, term and Payment.
Example
A2 = Original Amt of Loan
A3 = Term in Months
A4 = Payment
A5 = Interest Rate Yearly

B2 = 17420.00
B3 = 48
B4 = 413
B5 = This should be 6.49 %.

Either I'm slow or I'm missing some steps. Can anyone give me a
formula to get this rate?

Thanks,
Union

 

[Ron Rosenfeld]

Can anyone help me with this?
I'm trying to find the Interest Rate of a loan if I'm given Original
amount of loan, term and Payment.
Example
A2 = Original Amt of Loan
A3 = Term in Months
A4 = Payment
A5 = Interest Rate Yearly

B2 = 17420.00
B3 = 48
B4 = 413
B5 = This should be 6.49 %.

Either I'm slow or I'm missing some steps. Can anyone give me a
formula to get this rate?

Thanks,
Union

Look at the RATE worksheet function.

It will give you the interest rate PER PERIOD. Since your periods are MONTHS
and you want the annual rate, merely multiply your answer by 12.


--ron

 

[joeu2004]

I'm trying to find the Interest Rate of a loan if I'm given Original
amount of loan, term and Payment. Example
A2 = Original Amt of Loan
A3 = Term in Months
A4 = Payment
A5 = Interest Rate Yearly
B2 = 17420.00
B3 = 48
B4 = 413
B5 = This should be 6.49 %

6.49% is the nominal annual interest rate. I suspect that
is indeed what you want. But your subject line asks for the
APR, aka effective annual interest rate, which is something
else.

To compute 6.49% with the above numbers, you could use:

=12*RATE(48, 413, -17420)

RATE(...) computes the nominal monthly rate. Multiplying
by 12 gives the nominal annual rate -- 6.4858% in this case.

To determine the effective annual interest rate (what you
really pay in interest due to monthly compounding), you could
use:

=FV(RATE(48,413,-17420),12,,-1) - 1

Remember to format the cell as Percentage with 2 or more
decimal places. The result is 6.6821% in this case.

Caveat: Technically, even that is not the "APR", at least not
in the US mortgage business. US law requires that the
mortgage APR on the Reg Z document include a litany of
closing costs. Unfortunately, not every lender interprets the
law the same way, so it is difficult to compare what I call the
"Reg Z APRs". However, I believe that most lenders quote
the "APR" computed above in advertistements, since the "Reg
Z APR" cannot be known until near closing. Be sure you
understand which "APR" a lender is quoting.

 

[Union70]

Everyone thanks for the suggetions. I'm going to use the rate formula.
It works out great. I want to thank everyone for their input.

Thanks,
Unio