Forum Index Register Members List Events Mark Forums Read Help

 International Skeptics Forum A Simple Problem that Excel 2007 Choked On

 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.

 24th November 2019, 05:31 PM #1 Brainster Penultimate Amazing     Join Date: May 2006 Posts: 17,431 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.
 24th November 2019, 06:18 PM #2 DevilsAdvocate Philosopher     Join Date: Nov 2004 Posts: 5,329 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
 24th November 2019, 07:09 PM #3 Brainster Penultimate Amazing     Join Date: May 2006 Posts: 17,431 Originally Posted by DevilsAdvocate 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.
 30th November 2019, 03:09 AM #4 a_unique_person Director of Hatcheries and Conditioning     Join Date: Jul 2002 Location: Waiting for the pod bay door to open. Posts: 41,756 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
 30th November 2019, 10:21 PM #5 Brainster Penultimate Amazing     Join Date: May 2006 Posts: 17,431 Originally Posted by a_unique_person 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.

International Skeptics Forum