Financial Functions Excel

Question

The casino threw in a new twist. In addition to the previous offer (options 1 and 2):

1) $30,000 five years from now, or

2) $25,000 today

They are offering to pay you an annuity (option 3):

3) $9,000 today and $4,000 each year for the next five years.

The interest rate for this new offer is still 3.5% each year compounded monthly. Which choice provides the

most money at the end of five years: 1, 2 or 3? In cell M12 of the sample sheet on the last page of this

exercise, the value of the annuity is provided. You need to write the formula to calculate the value of this

annuity. Cell N12 contains an IF statement to make the determination on what choice to make with the

casino.

The IF statement should check whether the annuity is larger than the maximum of options 1 and 2 (using

the MAX function). If the annuity is larger, print “Take the annuity”, otherwise print whatever is in cell

J10 (the answer to question 3b).

Answer 1

1. Summarize the given data in an Excel Table
2. Be sure to label and understand the units (either $ or percentage)
3. Use the Compound Interest formula for option 2.
4. Use the equation for the value of an annuity.
5. Calculate in Excel directly OR
6. Use the built in Excel Function
7. (use Future Value of an investment FV() in Excel for Option 2)
8. (use Present Value of an investmnet PV() in Excel for Option 3)
9. Answer is Option 1 in this case.

(See video and pause video for details and Excel formulas shown.)