International Skeptics Forum

International Skeptics Forum (http://www.internationalskeptics.com/forums/forumindex.php)
-   Computers and the Internet (http://www.internationalskeptics.com/forums/forumdisplay.php?f=23)
-   -   A Simple Problem that Excel 2007 Choked On (http://www.internationalskeptics.com/forums/showthread.php?t=340493)

Brainster 24th November 2019 05:31 PM

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.

DevilsAdvocate 24th November 2019 06:18 PM

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.

Brainster 24th November 2019 07:09 PM

Quote:

Originally Posted by DevilsAdvocate (Post 12903428)
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.

a_unique_person 30th November 2019 03:09 AM

20 iterations isn't much. Can't they increase that?

Brainster 30th November 2019 10:21 PM

Quote:

Originally Posted by a_unique_person (Post 12908594)
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.


All times are GMT -7. The time now is 10:02 AM.

Powered by vBulletin. Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
2015-19, TribeTech AB. All Rights Reserved.