Examples of Excel Financial Functions
Examples of Excel Financial Functions
The Time Value of Money
Finding a growth rate:
ABC Corporation had earnings of $100,000 in 2000 and $200,000 in 2006.
What is its average annual compounded growth rate of earnings?
Note:
The average annual compounded growth rate is exactly the same thing as the average annual compounded rate of interest.
In other words, a sum of money grows by earning interest. If the interest is reinvested, it earns interest too - i.e. it compounds.
Answer:
Use the Excel RATE function. Click on the next cell to view its use
12.25%
Note use of minus sign.
Comments:
Always show your calculations, so that we can see how you got your answer.
By using Excel functions, this is done for you automatically - which is very convenient and saves you time.
Since the solutions to assigned problems are always given to you, we cannot grade you on getting the right answer.
So we grade you on showing us how to calculate the answer. That shows us that you have understood the material and can use it.
Do not use a calculator. It tells us nothing if you give an answer and say it came from a calculator.
There is a handy rule of thumb, known as the "Rule of 72."
It says that an amount roughly doubles in X periods at an interest rate of Y%, where X times Y = 72.
Example:
in the sample problem above earnings doubled in 6 years. So X = 6.
Then Y must be roughly 12% if X times Y = 72.
We see that our answer of 12.25% is roughly 12%, so the rule works.
Rate of Return:
Assume that you purchased a plot of land for $20,000. Ten years later you sold it for $80,000.
What rate of annual return have you earned on this investment?
Answer:
Use the Excel RATE function. Click on the next cell to view its use
14.87%
Note use of minus sign.
Present Value:
Assume that you are 25 years old today. Your rich uncle has just died, and his will provides that you will receive
$40,000 on your 30th birthday. But you are broke, and want money now. Your friendly bank says they will advance you
the money today, less annual interest at 8%. How much will you receive today?
Answer:
Use the Excel PV function (PV = Present Value). Click here to see the answer:
$27,223.33
Note use of minus sign.
Future Value:
Assume that you are 25 years old today. Your rich uncle has just died, and his will provides that you will receive
$40,000 today. But you are not broke, and prefer to save your money Your friendly bank says they will pay you
annual interest of 4.8% on a 6-year certificate of deposit. How much will you receive on your 31st birthday?
Answer:
Use the Excel FV function (FV = Future Value). Click here to see the answer:
$52,994.12
Note use of minus sign.
Annuities:
Annuities are a series of level periodic payments.
Consider an example of saving $100 at the end of each month for 20 years (240 months).
How much will this accumulate to at 12% interest per year (which is 1% per month).
Using Excel FV function:
$98,926
Note use of minus sign.
How long would it take to have a final accumulation of $150,000?
Using Excel NPER function:
279months or23years
What rate of interest is required for the future value in 20 years to amount to $200,000?
Using Excel RATE function:
#NUM!
per month, which is
#NUM!per year.