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)

Savings information to be used in the FV function
Savings information to be used in the FV function

Goal Seek

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.

Using Goal Seek to calculate the payments required
Using Goal Seek to calculate the payments required

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.

  1. In Excel 2003, click Tools > Goal Seek. Or in Excel 2007, click the Data tab, What-If Analysis and then Goal Seek
  2. The Goal seek dialogue box appears. Enter C8 in the Set cell: box
  3. Enter 12000 (omit the currency symbol) in the To value: box
  4. Enter B5 in the By changing cell: box
  5. Click Ok
  6. 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

Useful Links

Calculating loan repayments in Excel

Hub showing the use of Excel's PMT function to calculate repayments on a loan

Microsoft Excel training guides

Online Microsoft Excel training guides and quizzes. Improve your existing skills and learn new Excel tips.

Goal Seek Video Tutorial

More by this Author

  • Create an Interactive Excel Chart using Option Buttons
    6

    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...

  • Simulate a Cup Draw using Excel
    0

    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 Nutrition and Workouts
    2

    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.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working