Calculating future savings using Goal Seek
It is possible to calculate future savings for an account using Excel. The calculations will be based on the amount being put away or paid, a constant interest rate and savings period. To perform this we will be using the FV function and an Excel feature called Goal Seek.
The FV Function
The FV function will be used to calculate the future value based on the payments, interest rate and savings period.
The following example shows the total savings made by putting away £150 each month, at an interest rate of 5.5% for 3 years. This is done using the function below;
=FV(C5/12, D5*12, B5)
Goal Seek is a simple to use and brilliant What If Analysis tool that enables you to assess how to achieve a specific goal.
For example, we would like to save £12000 which will be used as a deposit for a house. We know the interest rate and how long we wish to save for, but do not know how much we would need to put away each month in order to reach our £12000 goal.
This scenario is perfect for Goal Seek.
Assessing the Payments
Let’s run Goal Seek on the spreadsheet to calculate how much we should be paying into the savings account each month.
Accessing Goal Seek is different in Excel 2003 and Excel 2007. However from there on the steps are the same.
- In Excel 2003, click Tools > Goal Seek. Or in Excel 2007, click the Data tab, What-If Analysis and then Goal Seek
- The Goal seek dialogue box appears. Enter C8 in the Set cell: box
- Enter 12000 (omit the currency symbol) in the To value: box
- Enter B5 in the By changing cell: box
- Click Ok
- Goal Seek runs and comes up with a result of £307.35 Monthly payments. Click Ok to keep the solution, or Cancel to return to the previous values