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
Goal Seek Video Tutorial
More by this Author
Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...
Microsoft Excel can do some amazing things. I like to attempt little projects testing what Excel can do. I was asked the other day to simulate a cup draw using Excel and this is the result. This article will give you...
Brock Lesnar is a phenomenon of a man and an athlete. Brock Lesnar weighs a lean 265 lbs and yet possesses extreme speed, agility and athleticism. How a man carrying so much muscle mass can maintain such high levels of...
No comments yet.