 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / May 2008Help with RATE function
My task is to deteremine an equivalent loan interest rate to a lease payment. ie: Lease payment term is 60 months, payment is $135.35, funded amount is $5,000 I've been trying to use the RATE function, but need some help entering the data properly. Every manner I've tried results in a NUM error. =RATE(60,(133.35),5000) is my last feeble attempt (I am referrencing cells, I just enered the numbers for clarity. I tried the payment as a negative after reading another post. I left out the $ formatting sign, just in case. What am I missing? Try =Rate(60,-135.35,5000) I'm sure more knowledgeable people will jump in and give you an explanation. Hopefully this will get you going. > My task is to deteremine an equivalent loan interest rate to a lease > payment. [quoted text clipped - 13 lines] > > What am I missing? Getting closer. The negative payment produces a real result, but the rate calculated is 1.5% and it should be about 19% Thanks! >Try =Rate(60,-135.35,5000) > [quoted text clipped - 20 lines] >> >> What am I missing? It gives me 1.7525 for a monthly rate. Multiplied by 12 = 21.03%  SignatureJim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Joe" <JoSchmo@yahoo.com> wrote in message Getting closer. The negative payment produces a real result, but the rate calculated is 1.5% and it should be about 19% Thanks! On Sat, 3 May 2008 14:41:16 -0700, " Ted-im" <ted-im@sbcglobal.net> wrote: >Try =Rate(60,-135.35,5000)> >I'm sure more knowledgeable people will >jump in and give you an explanation.> >Hopefully this will get you going. Ahhhh! What a dumbass I am. Thanks so much. I wish the hints that Excel provides when you insert a function were more descriptive. >It gives me 1.7525 for a monthly rate. >Multiplied by 12 = 21.03% > Getting closer. > The negative payment produces a real result, but the > rate calculated is 1.5% and it should be about 19% If you are saying that you already know that the lease interest rate should be about 19% and you continue to have trouble getting that result even with 12*RATE(60,-135.35,5000), the answer might lie in the explanation on the following web pages: http://www.leaseguide.com/lease08.htm http://www.leaseguide.com/lease09.htm >> Getting closer. >> The negative payment produces a real result, but the [quoted text clipped - 4 lines] >result even with 12*RATE(60,-135.35,5000), the answer might lie in the >explanation on the following web pages: I just did some manual regression to find the equivalent interest rate, but I'm building a spreadsheet to relate money factors to interest rates and evaluate the value to the dealers and their customers. I've found I'm far better in sales than I am in finance, Excel can be tough when you use it sparingly as I do >http://www.leaseguide.com/lease08.htm >http://www.leaseguide.com/lease09.htm Yep, the dealers will bang you when they can. They got me once about 10 years ago, but today I'm hip to their tricks I need to "back out" the interest rate from a table of money factors or buy rates that a lease company has provided. The reason is to explain and compare lease costs to dealers we're trying setup leasing programs for. The lender has given us the rate tables, but I prefer to have more understanding. Thanks all! > Try =Rate(60,-135.35,5000) > I'm sure more knowledgeable people will > jump in and give you an explanation. When using the financial functions, inflows and outflows must have opposite signs. Which is which depends on your point of view (lender or borrower). So you could also write RATE(60,135.35,-5000). It should also be noted that RATE returns the __periodic__ interest rate -- the rate per each of the 60 periods. If the period is not in years, we have to annualize the rate. Typically, multiply by 12 to convert a monthly rate to an annual interest rate, at least for US loans. Thanks for your explanation Ted On May 3, 1:41 pm, "Ted-im" <ted...@sbcglobal.net> wrote: > Try =Rate(60,-135.35,5000) > I'm sure more knowledgeable people will > jump in and give you an explanation. When using the financial functions, inflows and outflows must have opposite signs. Which is which depends on your point of view (lender or borrower). So you could also write RATE(60,135.35,-5000). It should also be noted that RATE returns the __periodic__ interest rate -- the rate per each of the 60 periods. If the period is not in years, we have to annualize the rate. Typically, multiply by 12 to convert a monthly rate to an annual interest rate, at least for US loans. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.