Hey, want to know what your investment will be worth in 5 years? 10 years? Want a sure thing? Excel correctly calculates the future value of any investment! Provided …

• You make a series of regular investments – even if it is just one “lump sum.”
• The interest rate that you earn is “constant” – that is a “fixed annual interest rate.”

OK, so now you understand that neither I nor Excel are “touting sure thing” winners in the stock market. No one can do that. But, you can’t beat Excel when it comes to accurate – and easy to use – financial calculations.

In this video lesson, I demonstrate how to use both the =FV() and =PMT() Functions to calculate Future Values. I also show you how to perform “What-If” Analysis using a One-Input Data Table.

Here are the steps to follow in this Excel Video Lesson:

1. Input values for Interest Rate (RATE), Number of Periods (NPER) and your Investment (PMT). These are the three required arguments for the =FV() Function. Use the Function Argument Dialog Box to ensure that you use the correct syntax for this function.
2. One of the “optional” arguments is “Type.” If you omit this, Excel defaults to a “0″ which means that you will make your investment contribution at the end of each period. A “1″ means that the contribution is made at the beginning of each period. Over a number of years, this can make a significant difference in the “Future Value” of your investment.
3. FV is also an “argument” in the =PMT() Function. If you want to calculate how much you need to contribute each month to reach a Savings Goal, use the =PMT() Function. However, in this case, the FV – while showing as an “Optional Argument” will refer to the cell containing your Savings Goal.
4. Create a One-Input Data Table to perform “What-if” Analysis – e.g. to substitute a series of different interest rates in your =PMT() – or =FV() Function.

NEW! Download the Practice File for this Excel Video Lesson:

• future-values

Find the Excel Video Lesson you want – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007″ is now available to purchase. I invite you to visit my online bookstore for more details.

Related Videos

Related Videos

• Click and Drag a Data Series to Goal Seek Inside a Chart
  
• Create a Two-Input Data Table
• Goal Seek to Optimize the Results of the =PMT() Function
  

You may be familiar with the expression, “Trust but Verify!” President Reagan used this phrase when referring to the Soviet Union’s agreement to disarm their missiles. It is also a good approach to follow when you receive Excel data from someone else:

1. Never open up a data file unless you trust the source.
2. Never make a decision on the data until you verify the accuracy of the formulas and the structure of the spreadsheet.

In this Excel Video Lesson, I demonstrate how to use the GoTo Special Dialog box to verify the accuracy of your data.

Here are the steps to follow in this lesson:

1. Choose Edit – GoTo – Special – Formulas to highlight all of the cells containing formulas in the worksheet.
2. The Keyboard Shortcuts for “GoTo” are Ctrl+G or the F5 Key.
3. In the “Special” dialog box, you can also choose “Constants.” This may make it easier to spot the cells that are “hard-coded” with a constant value when they should contain a formula.
4. You can narrow your selection to “Text,” “Logical,” Numeric,” etc. formulas.
5. Use the Ctrl+~ (Tilde) Shortcut to show the actual formulas in your worksheet.

NEW! Download the Practice File Used in This Excel Training Video

• go-to-special

Find the Excel Video Lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007″ is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Videos

• How to reveal the formulas in all cells
  

In this Excel training video I will show you how to use the Formula Auditing Toolbar to:

1. Learn about the relationships among your formulas and cells by tracing precedents & dependents.
2. Finding the source of your formula errors – and correcting them!
3. Evaluate your formulas step-by-step to better understand how they are built.
4. Setting up a small window to “watch” your key formulas change as you enter and edit data in cells.

Here are the steps to follow in this video training lesson:

1. Select Tools,  Formula Auditing, Show Formula Auditing Toolbar.
2. Select a cell containing a formula and experiment with the icons to Trace Precedents and Trace Dependents. Click the icon several times to trace back as far as you can go.
3. Select a cell and Evaluate its Formula. Step In and Step Out to learn how the formula is built.
4. Paste a list of all of the Names in your workbook. Select Insert, Name, Paste, Paste List.
5. Select a cell with a formula and click the Show Watch Window icon to see how the formula results change as you enter and edit data in cells that feed into that formula.

Find the Excel Training Video that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007″ is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons

• Name cells, ranges and constants and use them in your formulas

I use named cells, ranges and constants in my Excel formulas and workbooks for 3 reasons:

1. They are easier to explain (to others and to myself 6 months after I create a formula.)
2. They are easier to adapt (to other locations in the workbook – absolute cell references not required!)
3. They are easier to update (especially named constants in formulas when e.g. rates change.)

These are the steps to follow in this Excel training video:

1. Select the cells that you want to name and also the labels in the left column and top row.
2. Choose, Insert, Name, Create. Make sure that Left column and top row are selected.
3. To verify that your names have been created, click the Name box drop down list of names. Select a name and Excel takes you directly to that cell or range.
4. To substitute Name cells and ranges in existing formulas: Choose Insert, Name, Apply, OK
5. When you are writing a new formulas and you want to use a name, use the F3 “Paste Name” shortcut combination to select the name you want in the formula and click OK.
6. A “Named Constant” does not refer to a cell. Select Insert, Name, Define and then Name the Constant and in the “Refers to” part of the dialog box type in e.g. =0.0825 if you want your Named Constant to refer to a Sales Tax rate of 8.25%

Find the Excel Video Training Lesson that you want - Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007″ is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Video Training Topics

• Name a cell and use it in a formula

Learn how to create formulas in Excel that link to cells in multiple worksheets – or other workbooks. Then, if a value in one of your linked cells changes, your formula updates to reflect that change!

In this Excel video lesson we started by creating a template which we copied to create multiple worksheets (for Jan, Feb, Mar, etc.)

Let’s write a formula to SUM the sales from the Jan, Feb & Mar worksheets to create our 1st Quarter Summary Report. We will also review the Data, Consolidation method to accomplish the same goal.

Here are the steps to follow in this Excel Video Lesson:

1. For Summary or Consolidation reports it is best to start with a template.
2. In Cell D4 of the “1st Quarter…” worksheet we type “=sum(” and then click the worksheet tab for “January” and select cell D4.
3. Hold down the “Shift” key and click the March worksheet tab.
4. Notice that in the Formula Bar, Excel has selected the range from January:March for cell D4.
5. After you complete the formula with a “)” you can copy the formula to the other cells in the report.
6. Data, Consolidation is easy to calculate if you have organized your data based on a Template. You are consolidating “by Position.”

Find the Excel Video Lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007″ is now available to purchase. I invite you to visit my online bookstore for more details.

Related Excel Video Lessons

• Consolidate Excel data by position