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.
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.
via International Skeptics Forum https://ift.tt/2XGJqbQ
Aucun commentaire:
Enregistrer un commentaire