Rate Worksheet Function

[PatJennings]

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

 

[Fred Smith]

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]

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]

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]

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]

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]

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