Using a Spreadsheet for What-If Calculations

The second part of assignment 2 is an exercise in using a spreadsheet to perform what-if calculations. FV stands for future value. It is a formula that will calculate how much money you will have in the future given,

1. a certain amount of money in the bank today,
2. the interest rate this money is earning (which is also the interest rate any new money you add will be earning)
3. how much you are willing to put aside each year, and
4. the number of years you are willing to wait

For example,

Here's how you could use a spreadsheet like the one above to help you make a decision. Let's say you would like to take an around-the-world cruise. The cost is $8500. You have $2000 in the bank now but are thinking of spending $500 of that on a trip to Branson this year. To get started you enter the following into Excel:

Hum? That's not quit enough so you try to decide which you would rather do:

1. Put the trip off for another year (change C2 to 4)
2. Save more each year (increase C3)

You start increasing the value of C3 until the value in C5 is greater than $8500. Doing this you discover you will have to save almost $1900 each year:

This seems like quite a stretch so you decide changing the value of C2 to 4. (Putting the trip off for one year.):

You are still saving $1900 each year, which you plan to reduce, but it occurs to you that if you are putting this trip off for another year you might be able to afford the trip to Branson this year. So, the first thing you do is take the cost of the Branson trip ($500) out of current savings:

(Notice that the amount in C5 drops by more than $500. It drops by $500 plus the interest you would have earned on $500 for 4 years.)

Now, you start reducing the value of C3 until C5 is just over $8500:

Now you start to feel satisfied that you found the optimal solution to the problem. You will take a trip to Branson this year, save $1480 each year for 4 years and then you will have enough for an around-the-world trip.