Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interest Rate Help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interest Rate Help
On Wed, 19 Sep 2007 07:53:45 -0700, lawdoggy wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interest Rate Help
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) -- Regards, Tom Ogilvy "lawdoggy" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interest Rate Help
On Wed, 19 Sep 2007 09:54:04 -0700, Tom Ogilvy
wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interest Rate Help
On Wed, 19 Sep 2007 07:53:45 -0700, lawdoggy wrote:
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interest Rate Help
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) -- Dana DeLouis Windows XP & Excel 2007 "lawdoggy" wrote in message ups.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interest Rate Help
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). On Sep 20, 7:49 am, "Dana DeLouis" wrote: 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) -- Dana DeLouis Windows XP & Excel 2007 "lawdoggy" wrote in message ups.com... 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- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interest Rate Help
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%. -- Dana DeLouis "lawdoggy" wrote in message ups.com... 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). On Sep 20, 7:49 am, "Dana DeLouis" wrote: 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) -- Dana DeLouis Windows XP & Excel 2007 "lawdoggy" wrote in message ups.com... 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- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compound Interest Rate or Growth Rate | Excel Worksheet Functions | |||
to compute interest rate from principal and interest amount | Excel Discussion (Misc queries) | |||
Mtge calculation (Dly compound interest and multiple interest rate | Excel Programming | |||
APR - Annual Percentage Rate to Actual Interest Rate | Excel Worksheet Functions | |||
Effective rate of Interest and Copounding Interest | Excel Worksheet Functions |