ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software

Calculating future savings using Goal Seek

Updated on March 16, 2011

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


    0 of 8192 characters used
    Post Comment

    No comments yet.