Interest Rate Help

[lawdoggy]

I need help with the formula for figuring out what the 'real' interest
rate is when you pay a loan off early. Example car loan:

$10,000 (borrowed) 5% @ 60 months, Total with interest is: 11,322.74

Now:
I do the same as above but apply an extra $500 to the principle each
month and pay off the loan in 16 months and only pay $336.75 in
interest. What is my new 'real' interest rate for this loan?

Thanks in advance for anyone who can help! mitch

 

[Ron Rosenfeld]

I need help with the formula for figuring out what the 'real' interest
rate is when you pay a loan off early. Example car loan:

$10,000 (borrowed) 5% @ 60 months, Total with interest is: 11,322.74

Now:
I do the same as above but apply an extra $500 to the principle each
month and pay off the loan in 16 months and only pay $336.75 in
interest. What is my new 'real' interest rate for this loan?

Thanks in advance for anyone who can help! mitch

I believe you are still paying 5% per year on the borrowed funds. But the
borrowed amount is less each month than in the original scenario.

--ron

 

[Guest]

Here is one way that seems to work:

Payment: 188.71 + 500 = 688.71

=RATE(16,-688.71,10000,0,0,0.01)

produced: 0.0116550291317306

or 1.17% This is the per period interest. the Annual would be 12 times
that:
13.986%

If I check it as
=PMT(0.0116550291317306,16,10000)

I get a payment of 688.71


RATE
Returns the interest rate per period of an annuity. RATE is calculated by
iteration and can have zero or more solutions. If the successive results of
RATE do not converge to within 0.0000001 after 20 iterations, RATE returns
the #NUM! error value.

Syntax

RATE(nper,pmt,pv,fv,type,guess)

 

[Ron Rosenfeld]

Here is one way that seems to work:

Payment: 188.71 + 500 = 688.71

=RATE(16,-688.71,10000,0,0,0.01)

produced: 0.0116550291317306

or 1.17% This is the per period interest. the Annual would be 12 times
that:
13.986%

If I check it as
=PMT(0.0116550291317306,16,10000)

I get a payment of 688.71


RATE
Returns the interest rate per period of an annuity. RATE is calculated by
iteration and can have zero or more solutions. If the successive results of
RATE do not converge to within 0.0000001 after 20 iterations, RATE returns
the #NUM! error value.

Syntax

RATE(nper,pmt,pv,fv,type,guess)

Tom,

One problem is that the data provided is not consistent.

OP wrote that with the new payment of $500 extra, he would only be paying
$336.75 in interest. So that would mean total payments of $10,336.75.

16 Payments of 688.71 is $11,019.36 or $1,019.36 in interest. So either he is
only paying 15 months with a small Balloon; or the information about total
interest provided is incorrect.

If, indeed, he is only paying $336.75 in interest, then he can only have 15
payments of $688.71 plus a balloon of $6.10.

So I would use:

=RATE(15,688.71,-10000,6.1)*12 for the annual interest which is 5%.




--ron

 

[Chuck]

I need help with the formula for figuring out what the 'real' interest
rate is when you pay a loan off early. Example car loan:

$10,000 (borrowed) 5% @ 60 months, Total with interest is: 11,322.74

Now:
I do the same as above but apply an extra $500 to the principle each
month and pay off the loan in 16 months and only pay $336.75 in
interest. What is my new 'real' interest rate for this loan?

Thanks in advance for anyone who can help! mitch

Don't know where the 500.00 extra per month came from, but to pay back a loan
of 10000 at 5% in 16 months requires a monthly payment of 647.37. That is
458.66 per month extra.

Chuck

 

[Dana DeLouis]

In Excel 2007, under New | Template, I pulled up the
"Loan Amortization Schedule" which has the option for an extra payment.
Total Interest was $336.77

We now that the answer should be < 5%.

=RATE(16,,-10000,10000+336.77,,5%)*12
2.49%

Now we plug this rate back in the loan template to check. It appears
reasonable if we change the loan period from 1 to 2 years. (can't do 16
months)

 

[lawdoggy]

Thanks to everyone who has replied. I guess this was not an easy
question.

Dana, your formula (answer) seems to make the most sense to me and
the interest should be a smaller amount than the original (I would
think).

 

[Dana DeLouis]

I think Ron was right in that the answer should be 5%.
Here's a different way to look at it.
Using the Loan Template, if we do not make extra payments, our cash flow
will be
-10000
+188.71 for 60 months.

Hence (3% guess)
=IRR(E1:E61,3%)*12
5.00%.

If we make extra payments, all this does is shorten the loan period.
From our cash perspective...
-10000
+688.71 (15 times)
+6.06 ( 1 time)

=IRR(A1:A17)*12
5.00%

So, basically, no change. Just a shorter payback period. I agree we Ron
that it might be the same 5%.