 |
 | 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 / Worksheet Functions / May 2008Rate Worksheet Function
Using the values in columns A, B, and C, I have the following data solving for rate in column D. I have changed the iteration count and the minimum value under Tools, Options, Calculation to no avail. Why are the #NUM results appearing? Can the real values be solved? Thanks. A B C D E 1 206 100,000 48444% 206 2 206 100,000 2054% 412 3 206 100,000 650% 618 4 206 100,000 340% 824 5 206 100,000 220% 1030 6 206 100,000 #NUM! 1236 7 206 100,000 #NUM! 1442 8 206 100,000 #NUM! 1648 9 206 100,000 #NUM! 1854 10 206 100,000 #NUM! 2060 11 206 100,000 #NUM! 2266 12 206 100,000 #NUM! 2472 13 206 100,000 #NUM! 2678 14 206 100,000 43% 2884 15 206 100,000 39% 3090 16 206 100,000 35% 3296 What do columns A,B,C and D represent? Nper? PV? Pmt? What's the addtional column (206, 412, 618, etc.) for? What formula are you using to calculate the rate? Regards, Fred. > Using the values in columns A, B, and C, I have the following data solving > for rate in column D. I have changed the iteration count and the minimum [quoted text clipped - 102 lines] > 35% > 3296 I am using the standard RATE function with payments at the beginning of the period. =RATE(B5,-C5,0,D5,1) The final column in the data in my earlier message is not used in the calculation. It is merely the aggregate of the payments made. I attempted to provide the data in table format. I will try again. =RATE(B5,-C5,0,D5,1) B C D E Num Pmyts Pymyts Future Value Rate 1 206 100,000 48444% 2 206 100,000 2054% 3 206 100,000 650% 4 206 100,000 340% 5 206 100,000 220% 6 206 100,000 #NUM! 7 206 100,000 #NUM! 8 206 100,000 #NUM! 9 206 100,000 #NUM! 10 206 100,000 #NUM! 11 206 100,000 #NUM! 12 206 100,000 #NUM! 13 206 100,000 #NUM! 14 206 100,000 43% 15 206 100,000 39% 16 206 100,000 35% 17 206 100,000 33% > What do columns A,B,C and D represent? Nper? PV? Pmt? What's the addtional > column (206, 412, 618, etc.) for? [quoted text clipped - 109 lines] >> 35% >> 3296 As you've found out, the iteration count in Tools does not appy to Rate. It applies if you are using Goal Seek. As it states in Help, when Rate gives you #Num, you must experiment with the Guess parameter to get it to converge to a valid result. For example, =rate(6,-206,0,100000,1,.5) will return an answer (159%) Try setting the guess in each one of your formulae, and you should get proper results. Regards, Fred. >I am using the standard RATE function with payments at the beginning of the >period. =RATE(B5,-C5,0,D5,1) [quoted text clipped - 212 lines] >>> 35% >>> 3296 Thanks Fred. Adding the "Guess" factor fixed it. But I'm curious: this problem surfaced within a list of rate calculations. With the guess factor in place, the rate calculations in front of and behind the #NUM results did not change when I applied a guess factor. Why would only a few result in the #NUM error? The calculated rates were significantly different from the guess factor. Thanks again. Pat > As you've found out, the iteration count in Tools does not appy to Rate. > It applies if you are using Goal Seek. [quoted text clipped - 229 lines] >>>> 35% >>>> 3296 Functions which calculate interest rates use an algorithm called Newton-Raphson. I've programmed it, and I'm amazed to see how fast it converges from the guess to the proper result. I've used it to calculate thousands of rates of return. In the real world (at least my real world), I've never seen it fail to converge. Your calculations look academic, as opposed to real world, to me. Rates of return greater than 33% are hard to come by. As it was explained to me, Newton-Raphson is calculating the tangent of the curve to approximate where it should look next. It's like trying to find the edge of the lake using the slope of the land. Sometimes the land is so bumpy that the algorithm diverges away from, rather than converges toward, the lake. In these cases, you have to give it a different starting point (ie, guess). If you want more information, just Google "Newton Raphson". The discussions will keep you occupied for as long as you want. Regards, Fred. > Thanks Fred. Adding the "Guess" factor fixed it. But I'm curious: this > problem surfaced within a list of rate calculations. With the guess [quoted text clipped - 238 lines] >>>>> 35% >>>>> 3296 Thank you for sharing your knowledge with me and helping me fix my calculations. Wow! I don't think I'm ready for that discussion group. The "rates" are real world. They represent the payment of a death benefit of a life insurance policy with a constant annual premium. The table represents having received the benefit after the number of years that the premium has been paid. Obviously, it is an extremely unlikely event to happen sooner rather than later as the (invested - not an accurate description for a premium) payments are made. Since the benefit is fixed and the aggregate cost of the premiums increase over time, the implicit rate reduces over time. Pat > Functions which calculate interest rates use an algorithm called > Newton-Raphson. I've programmed it, and I'm amazed to see how fast it [quoted text clipped - 261 lines] >>>>>> 35% >>>>>> 3296 |
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.