| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rate 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rate Worksheet Function
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. "PatJennings" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rate Worksheet Function
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% "Fred Smith" wrote in message ... 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. "PatJennings" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rate Worksheet Function
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. "PatJennings" wrote in message ... 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% "Fred Smith" wrote in message ... 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. "PatJennings" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rate Worksheet Function
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 "Fred Smith" wrote in message ... 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. "PatJennings" wrote in message ... 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% "Fred Smith" wrote in message ... 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. "PatJennings" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rate Worksheet Function
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. "PatJennings" wrote in message ... 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 "Fred Smith" wrote in message ... 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. "PatJennings" wrote in message ... 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% "Fred Smith" wrote in message ... 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. "PatJennings" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rate Worksheet Function
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 "Fred Smith" wrote in message ... 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. "PatJennings" wrote in message ... 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 "Fred Smith" wrote in message ... 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. "PatJennings" wrote in message ... 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% "Fred Smith" wrote in message ... 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. "PatJennings" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rate Worksheet Function
Why would only a few result in the #NUM error?
I believe the derivative is throwing the calculation off a little. If you wish, here is a custom function that begins at a limit and works it's way back. (hence removing the guess) It doesn't have all the checks in this version, nor does it switch routines when the derivative is flat. Sub TestIt() Dim Yr As Long For Yr = 1 To 17 Debug.Print Yr; ": "; FormatPercent(iRate(Yr, -204, 0, 100000), 2) Next Yr End Sub Function iRate(NPer, Pmt, Pv, Fv) '// = = = = = = = = = '// Find Rate with Type=1 '// By: Dana DeLouis '// = = = = = = = = = Dim r As Double Dim n As Double Dim num As Double Dim den As Double Dim d As Variant Const MaxLoop As Long = 20 Const Dummy As Long = 1 n = NPer Set d = CreateObject("Scripting.Dictionary") 'Best Guess @ Limit where Rate = 0 r = (-2 * (Fv + n * Pmt + Pv)) / (n * (Pmt + n * Pmt + 2 * Pv)) d.Add r, Dummy On Error GoTo FoundSolution Do num = Fv + Pv * (1 + r) ^ n + (Pmt * (1 + r) * (-1 + (1 + r) ^ n)) / r den = (n * Pv * r ^ 2 * (1 + r) ^ n + _ Pmt * (1 + r) * (1 + (1 + r) ^ n * (-1 + n * r))) _ / (r ^ 2 * (1 + r)) r = r - num / den d.Add r, Dummy Loop While d.Count <= MaxLoop r = "Did not Converge" FoundSolution: iRate = r End Function Returns: 1 : 48,919.61% 2 : 2,064.60% 3 : 652.47% 4 : 341.57% 5 : 220.52% 6 : 159.01% 7 : 122.62% 8 : 98.88% 9 : 82.29% 10 : 70.12% 11 : 60.85% 12 : 53.56% 13 : 47.71% 14 : 42.91% 15 : 38.91% 16 : 35.53% 17 : 32.64% -- HTH :) Dana DeLouis "PatJennings" wrote in message ... 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 "Fred Smith" wrote in message ... 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. "PatJennings" wrote in message ... 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 "Fred Smith" wrote in message ... 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. "PatJennings" wrote in message ... 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% "Fred Smith" wrote in message ... 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. "PatJennings" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| APR - RATE Function | Excel Worksheet Functions | |||
| Rate function | Excel Worksheet Functions | |||
| How to use RATE function? | Excel Discussion (Misc queries) | |||
| How to use RATE function? | Excel Worksheet Functions | |||
| Possible Variable in Rate Function | Excel Worksheet Functions | |||
Linear Mode
