ISF Logo   IS Forum
Forum Index Register Members List Events Mark Forums Read Help

Go Back   International Skeptics Forum » General Topics » Computers and the Internet
 


Welcome to the International Skeptics Forum, where we discuss skepticism, critical thinking, the paranormal and science in a friendly but lively way. You are currently viewing the forum as a guest, which means you are missing out on discussing matters that are of interest to you. Please consider registering so you can gain full use of the forum features and interact with other Members. Registration is simple, fast and free! Click here to register today.
Tags excel , spreadsheets

Reply
Old 24th November 2019, 05:31 PM   #1
Brainster
Penultimate Amazing
 
Brainster's Avatar
 
Join Date: May 2006
Posts: 17,302
A Simple Problem that Excel 2007 Choked On

As some of you may know, I tutor students in finance. I'm also a huge comic book fan, so when a student came in with a problem involving Scrooge McDuck, I was thrilled. In one of the most famous Scrooge stories ever, Back to the Yukon (rated the #1 Donald Duck story of all time at Inducks), Uncle Scrooge is headed back to Alaska, where he made the first part of his fortune during the 1898 gold rush. He had loaned Gravel Gertie $1000 and he calculated that 50 years later, at compound interest, she owed him 1 billion dollars.

That was basically the problem that the student presented to me (complete with the Scrooge McDuck references), and of course the question was, what is the applicable interest rate assuming annual compounding.

This is a fairly easy problem for Excel to handle; the only tricky part is that you need to switch the sign on either the 1,000 or the 1 billion to make them negative (because that tells Excel that the flows go in opposite directions). All I had to do was enter =rate(50,0,-1000,1000000000).

And Excel gave me the error code #num. There is absolutely no good reason for that error message. In fact, if you reduce the last input to 1 million, so that your formula is =rate(50,0,-1000,1000000), excel correctly calcs a rate of about 14.82%. So it was not a case of bad syntax, it was just a case of the magnitude of the numbers.

Fortunately I know Excel backwards and forwards, so I was able to show the student how to solve for the rate using the Goal Seek method, which revealed that Scrooge was charging Gertie a rate of about 31.82%.

I suspect what is going on here is that Excel uses iteration and interpolation to find the rate, which is sometimes the only way to do it, but actually not in this case. The basic formula for the future value (1 billion) of a present value at a given rate for 50 years is (1000) is FV=PV(1+r)^50. In this case we don't use the reversed sign on the 1000, so our formula is 1,000,000,000=1000*(1+r)^50. Dividing both sides by 1000 reduces it to 1,000,000=(1+r)^50. Take the 50th root of both sides (something Excel does trivially) and you get 1.3182=1+r. Not hard to solve from there.

I was surprised to see this basic an error.
__________________
My new blog: Recent Reads.
1960s Comic Book Nostalgia
Visit the Screw Loose Change blog.
Brainster is online now   Quote this post in a PM   Nominate this post for this month's language award Copy a direct link to this post Reply With Quote Back to Top
Old 24th November 2019, 06:18 PM   #2
DevilsAdvocate
Philosopher
 
DevilsAdvocate's Avatar
 
Join Date: Nov 2004
Posts: 5,319
I don't think it is a basic error. The RATE function solves through iteration so that it can solve in all cases. Excel recognizes that won't always work, which is why it has the "guess" parameter. From the help file:

Quote:
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.
If you get a #NUM! error, the resolution is to enter a value for "guess". This will solve without error:

=RATE(50,0,-1000,1000000000, 0, 0.3)

The person who wrote the function chose to use a single algorithm and obviously realized that there would have to be limitation on convergence and iterations, and therefore added the guess parameter to compensate. The advantage of a single algorithm is simplicity and consistency. If it solved the formula different ways in different circumstances, it could result in different levels of precision that could leave users scratching their heads.

Of course it would be possible to create a more robust function that chooses different methods of solving based on different variables. Perhaps you could have the class assignment to write their own "better" formula that could solve the formula without a guess parameter.
__________________
"You really don't command the enterprise." - Leonard Cohen
DevilsAdvocate is offline   Quote this post in a PM   Nominate this post for this month's language award Copy a direct link to this post Reply With Quote Back to Top
Old 24th November 2019, 07:09 PM   #3
Brainster
Penultimate Amazing
 
Brainster's Avatar
 
Join Date: May 2006
Posts: 17,302
Originally Posted by DevilsAdvocate View Post
I don't think it is a basic error. The RATE function solves through iteration so that it can solve in all cases. Excel recognizes that won't always work, which is why it has the "guess" parameter. From the help file:



If you get a #NUM! error, the resolution is to enter a value for "guess". This will solve without error:

=RATE(50,0,-1000,1000000000, 0, 0.3)

The person who wrote the function chose to use a single algorithm and obviously realized that there would have to be limitation on convergence and iterations, and therefore added the guess parameter to compensate. The advantage of a single algorithm is simplicity and consistency. If it solved the formula different ways in different circumstances, it could result in different levels of precision that could leave users scratching their heads.

Of course it would be possible to create a more robust function that chooses different methods of solving based on different variables. Perhaps you could have the class assignment to write their own "better" formula that could solve the formula without a guess parameter.
At least in the Excel 2007 version I have, there is no "guess" input in the rate function. There is a "guess" in the IRR function, but to be honest that seems to be legacy as the IRR function always returns the right value for me. I just checked. If I do -1000 in year 0, and +1 billion in year 50 (with 0 for the cash flows in between), the irr function gives 31.83% (I rounded incorrectly when I said 31.82%, it's 31.8257%)

So that's two quick and easy ways to find an answer in excel that excel can't seem to solve on its own.
__________________
My new blog: Recent Reads.
1960s Comic Book Nostalgia
Visit the Screw Loose Change blog.
Brainster is online now   Quote this post in a PM   Nominate this post for this month's language award Copy a direct link to this post Reply With Quote Back to Top
Old 30th November 2019, 03:09 AM   #4
a_unique_person
Director of Hatcheries and Conditioning
 
a_unique_person's Avatar
 
Join Date: Jul 2002
Location: Waiting for the pod bay door to open.
Posts: 41,541
20 iterations isn't much. Can't they increase that?
__________________
Continually pushing the boundaries of mediocrity.
Everything is possible, but not everything is probable.
“Perception is real, but the truth is not.” - Imelda Marcos
a_unique_person is offline   Quote this post in a PM   Nominate this post for this month's language award Copy a direct link to this post Reply With Quote Back to Top
Old 30th November 2019, 10:21 PM   #5
Brainster
Penultimate Amazing
 
Brainster's Avatar
 
Join Date: May 2006
Posts: 17,302
Originally Posted by a_unique_person View Post
20 iterations isn't much. Can't they increase that?
That's another thing that might be legacy. Back in the bad old days, spreadsheets often took forever to recalc, and so the first thing you had to do when you started a new spread was to tell only to recalc when you pressed F9. I had spreadsheets back in the 1980s where I would press recalc. Then I'd walk down the hall, grab a cup of coffee, chat with the receptionist for a few minutes and come back to my office to find that it was 50% recalced. Might be time for a trip to the bathroom.

When I got my first 386 machine I remember freaking out when I hit recalc and nothing happened. Until I noticed that some visible numbers had changed. It had recalced so quickly that I didn't even see the "% recalced" message.
__________________
My new blog: Recent Reads.
1960s Comic Book Nostalgia
Visit the Screw Loose Change blog.
Brainster is online now   Quote this post in a PM   Nominate this post for this month's language award Copy a direct link to this post Reply With Quote Back to Top
Reply

International Skeptics Forum » General Topics » Computers and the Internet

Bookmarks

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -7. The time now is 06:24 PM.
Powered by vBulletin. Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.

This forum began as part of the James Randi Education Foundation (JREF). However, the forum now exists as
an independent entity with no affiliation with or endorsement by the JREF, including the section in reference to "JREF" topics.

Disclaimer: Messages posted in the Forum are solely the opinion of their authors.