Microsoft Excel 2010 Expert Level 6

[Pages:20]Microsoft Excel 2010 Expert Level 6

Course Handbook Supplement

By Richard Rost

Published By

Amicron Computing



First Printing 6/17/2011 Copyright 2011 by Amicron Computing

All Rights Reserved

Welcome

Welcome to Microsoft Excel 2010 Expert Level 6.

This handbook is designed to be a supplement to the full 599CD video course for Microsoft Excel 2010 Expert Level 6. We recommend you use this handbook to follow along with the class videos. This handbook is not meant as a stand-alone study guide.

We do recommend that you watch the course videos one time through, paying attention to the lessons covered. Follow along with the course videos using this guide. Take notes on the pages where needed. Then, watch the videos a second time, practicing the examples yourself on your own website.

Table of Contents

Welcome ............................................................................................................... 2 Table of Contents..................................................................................................2 Introduction ...........................................................................................................3 Lesson 1: Excel Financial Terms ..........................................................................4 Lesson 2: Loan Calculator ....................................................................................5 Lesson 3: Investment Calculator...........................................................................9 Lesson 4: Interest Rate Calculator......................................................................10 Lesson 5: Millionaire Calculator ..........................................................................11 Lesson 6: Initial Deposit......................................................................................12 Lesson 7: Home Value Calculator.......................................................................13 Lesson 8: Loan Amortization ..............................................................................14 Lesson 9: Credit Cards .......................................................................................18 Review ................................................................................................................20

Excel 2010 Expert 6

Page 2 of 20

Introduction

Welcome to Microsoft Excel 2010 Expert Level 6, brought to you by . I am your instructor, Richard Rost.

Objectives for today's class are to learn about:

Financial Terms Common Functions Loan Calculator Investment Calculator Interest Rate Millionaire Calculator Home Value Calculator Loan Amortization

We will be using Microsoft Office Excel 2010 in this class. If you are using Microsoft Office Excel 2007 or older, you should visit my website and look for my Excel 2000, 2003 and 2007 series. This class follows Excel 2010 Expert 5.

The Beginner series covers the basics, formatting data, function and illustrations, charts and tables. If you don't know how to do any of that, I strongly recommend you take those classes, along with the previous Expert Level classes, before attempting Excel 2010 Expert Level 6.

This is the Expert Level 6 class for Microsoft Excel 2010. This is for the user who is comfortable with using Excel and understands all the concepts explained in the Beginner classes.

Excel 2010 Expert 6

Page 3 of 20

Lesson 1: Excel Financial Terms

In this lesson we'll learn about some of Excel's Financial Terms and Functions. These functions can be used for many different financial calculations like figuring out a monthly loan payment, the future value of an investment, and many more.

Here are the common terms and functions:

PV ? Present Value also known as the principal amount or initial value of a loan

FV ? Future Value is the amount due on your loan at some future time

PMT ? Payment is the amount you're paying every period

RATE ? Interest Rate is how much the bank is making off your loan

NPER ? Number of Periods is the term of the loan

Simple Interest means when you have a certain amount of money and the lender is going to charge you a certain percentage of interest for a specific term (i.e., $1,000 at 5% interest equals $50 in interest).

Compound Interest usually applies to accounts like a savings account. Here a bank will give you a specific interest rate on your account and they compound it monthly based on your average daily balance. Once the interest amount is calculated it is added to your balance each month. This makes you more likely to keep your money in the bank.

Calculating Simple Interest is very straight forward and a simple math calculation. Calculating Compound Interest is a bit more complicated, so these Excel functions are useful in calculating Compound Interest.

Excel 2010 Expert 6

Page 4 of 20

Lesson 2: Loan Calculator

In this lesson we'll learn how to build a Loan Calculator to figure out the monthly payment on a mortgage. Let's start with a blank worksheet in Excel and enter some sample information for our mortgage:

Most of the financial functions want months if that's what your period is. If you're doing a bi-weekly or bimonthly mortgage, your number of periods will be different. Now that we have our information, we can calculate our payment using the PMT function. Keep in mind that it's going to require the interest rate divided by the number of payment periods per year. In this case it's 12 for 12 months. Here is what our PMT formula will look like:

Excel 2010 Expert 6

Page 5 of 20

Here is what our payment would be based on this formula. It's displayed in red because it is considered as money going out or spending, so that's a negative number. To make it a positive number, just multiply by "-1":

Now that we know what our monthly payment is, we can calculate our total payments. Part of the formula will include the down payment as well since that is part of our money going to the bank:

When we hit enter, you'll notice we get a fractional component for our payment. You may not want this so we can use one of the Rounding functions we learned in the last class to eliminate the fraction. Here's what our payments would be once we've applied a Rounding function:

Excel 2010 Expert 6

Page 6 of 20

Now let's figure out how much interest we're paying on this loan: When we hit enter, our interest amount should be this:

Excel 2010 Expert 6

Page 7 of 20

The nice thing about Excel is that we can look at different scenarios to see which one is the best option:

Try changing the some of the variables (i.e., down payment, term, period, interest rate) in column C to see how much savings or how much more you would be spending based on those numbers.

Excel 2010 Expert 6

Page 8 of 20

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download