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. 
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:
=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. 
Quote:
So that's two quick and easy ways to find an answer in excel that excel can't seem to solve on its own. 
20 iterations isn't much. Can't they increase that?

Quote:
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.
© 201519, TribeTech AB. All Rights Reserved.