Overview
This lecture introduces Excel's What-If Analysis Data Table feature, demonstrating its value for simulating different outcomes in financial planning without rewriting formulas.
Using Excel’s FV Function for Retirement Planning
- FV (Future Value) function calculates the future value of regular investments with interest.
- Monthly payments ($200, entered as -200) are made over 20 years with a 4% annual interest rate.
- Convert annual interest rate to monthly by dividing by 12; convert years to months by multiplying by 12.
- FV formula: =FV(rate/12, years12, payment), with payment as a negative value for outflows.
Introduction to Data Table (What-If Analysis)
- Data Table allows you to simulate different outcomes by varying one or two variables in your model.
- Set up a prototype calculation, then list values to test (e.g., different interest rates) in a column or row.
- Reference the prototype formula next to your inputs for the table.
Creating a One-Variable Data Table
- Highlight the input values and the reference cell, then choose Data > What-If Analysis > Data Table.
- Use the column input cell if your test values are in a column; pick the cell with the variable you want to change.
- Data Table automatically fills simulated results based on each input value.
Creating a Two-Variable Data Table
- Lay out one set of variables in a row (e.g., years) and another in a column (e.g., interest rates).
- Reference the prototype formula in the top-left corner of the table.
- Define both row and column input cells corresponding to each variable in the prototype when setting up the table.
Formatting and Practical Tips
- Hide or format prototype values for clarity, e.g., changing font color or using emojis.
- Data tables use arrays, so individual cells can't be changed or deleted directly.
- Large data tables can slow down sheets; set calculation options to "Automatic Except for Data Tables" and press F9 to update manually.
Key Terms & Definitions
- FV (Future Value) function — Calculates the value of regular cash flows at a future date with a set interest rate.
- Data Table — An Excel tool for simulating outcomes by varying one or two input variables.
- What-If Analysis — Excel features allowing users to see effects of changing variables in formulas.
Action Items / Next Steps
- Practice creating a one-variable and two-variable data table in Excel.
- Experiment with calculation options and formatting for clarity.
- Review FV function syntax and try varying input scenarios.