Transcript Excel: Mortgages

EXCEL: MORTGAGES
A
mortgage is when a person takes out
a loan from a bank to buy a home. In
this project, we will select a house,
select a bank, and plan out a mortgage
payment schedule. Use
www.trulia.com to find the homes. Use
www.chase.com to find the mortgage
rates under: Products & Services =>
Mortgages => Buy a Home.
 Use
the website to pick a favorite home in
the Bronx and find out the cost. Open up a
new Excel document and save the document
with your name included in the file name.
Write the information for the most expensive
house and the least expensive that you would
be comfortable living in. To make the words
fit, click the mouse and drag in the little
lines separating the different columns.
Creating The Mortgage Formula
 Set up the spreadsheet as shown. The formula will
multiply the house cost times the interest rate. The
interest rate is for a year and payments are made per
month so it is divided by 12. The payment subtracts
from the loan amount.
***Draw This
Table Into
Notes***
DRAG THE FORMULA TO MULTIPLE
MONTHS

Excel will automatically copy formulas and
numbers when you select it and drag the little
solid square in the lower right corner of the
selected area. Select the Loan Amount, Interest
Rate and Payment as shown. Drag the little box
downward and watch as Excel fills in lots of
numbers. See how far it has to be dragged down
To get the Loan Amount to zero for both Favorite
and Cheapest House.
 Excel
Mortgages
 Find the Nicest House in The Bronx
 Creating the Mortgage Formula
 Drag the Formula to Multiple Months
Excel: Lunch Appreciation
 Go to http://www.opt-
osfns.org/schoolfood/public1/default.aspx and find the
calendar for HS Lunch.
 Enter the data as shown with
columns marked with the
number 1 under “Like A Lot”
Could Eat It and “Won’t Eat
It”. Be sure to place the labels
on both the top and the
bottom.
ADDING UP THE COLUMNS

To add up the numbers
automatically, select the
information and use the
AutoSum key in the top right
corner on the home tab. The
program does the calculations
and places the totals on the
bottom. Set it up as shown in
order to have proper
formatting for graphing.
 To
graph the accumulated data, select just
the bottom titles and sums. Then, click the
insert tab and select the pie graph under the
charts menu. The pie graph will now display
each of the columns shown as a colorful,
easy to understand graphic. It is easy to see
which students are a more fussy eater by
looking at the wedge showing
“Won’t Eat It”.
Like A
Could
Won't
Adding Additional Graphs
 There are a lot of different kinds of graphs available to
show the same data. Find another graph that does not
display the data very well. Find a graph that does show
the data very well. Explain in a paragraph, under the
three graphs, what it is that has been accomplished
during this section.
Summary
 Excel: Lunch Appreciation
 Adding up the Columns
 Inserting a Pie Graph
 Adding Additional Graphs

 Go
to www.bls.gov/emp/ep_chart_001.htm to
find out information on how education affects
salary.
Add the Unemployment rates and weekly
salary for Doctoral Degrees, Bachelors
Degree, H.S. Diploma and Less Than H.S
diploma as shown to your notes
Monthly and Yearly Salary
 There are 4 weeks in a month and 52 weeks in a year.
Multiply the doctor’s weekly earnings by 4. To do this,
use the equals sign, click on the doctor’s number for
weekly earnings and use the * for multiplication and
the number 4. Use * 52 for the year.
= <click on 1551> * 4
= <click on 1551> * 52
DRAGGING THE FORMULAS

The formulas under the Monthly Earning column
and Yearly Earnings column should be dragged
down. To do this, click on the small, solid squre
while selecting the two cells with the formulas.
Then, drag the mouse down to the bottom of the
data table.
A
person can usually afford to pay about a
third of their salary towards their mortgage
to buy a house. Go back to the first section
of the excel spreadsheet and see how much
of an education a person will need to be able
to afford the 2000 monthly payment. Explain
the answer with a paragraph in a text box by
using the Insert Tab : Text Box. Create a
useful graph under Insert Chart for the Salary
Data.
Summary
 Excel: Million Dollar Education
 Monthly and Yearly Salary
 Dragging The Formula
 Salary Analysis
EXCEL: EARLY RETIREMENT
The S&P 500 gives a person an idea on
how well the stock market is doing. If
the market is doing well, people get a
higher percentage of money from
investment.
 Go to the Wikipedia web site and look up
S&P 500. On that page, navigate to the
part that says total annual returns.
Copy and paste this information into the
project excel spreadsheet.


To find the average rate from the
stock market, select the percentages
and use the “average” function with
the selection arrow on the AutoSum
button in the top right corner of the
Home Tab. This number will give an
idea of how much a person may make
in the stock market. If the number is
higher, people made more money that
year. If the number is negative,
people lost money.
Path to Millionaire
 With careful planning, a financial manager can figure
out how long it will take to save a million dollars. It is
easier to save if the money is invested. The higher
percentage returned from the investment, the faster
the money will grow. The more money added
monthly, the faster the investment will grow.
CALCULATE INVESTMENT RETURN

Using the formula shown, a
financial planner can figure out
how long it will take to make a
million dollars. After entering
the formula, drag the selected
formula while clicking on the
small solid square in the lower
corner. Correct the formula to
properly show a monthly return
rather than erroneous yearly
return as shown.
 Excel
Early Retirement
 Average Rate of Return
 Path to Millionaire
 Calculate Investment Return
Data Analysis/Rubric
 There are several ways to earn points on this project. A
rubric adds all possible values and returns a sum.
Remember to use the AutoSum to make the math
easier. In some cases, a student may choose to earn
more points on one section. Be certain to complete
enough credit to get the desired grade.
RUBRIC, PART 1
College and Careers
 A great way to have a successful career is to keep in
charge of peoples money. Become an accountant or a
financial planner and use MS Excel for success.
Additional, business math and science majors will use
spreadsheets all the time to analyze data. Nearly any
career or major will benefit from being able to keep
track of numbers and create plans.
SUMMARY
Data Analysis/Rubric
 Rubric Part 1
 Rubric Part 2
 College and Careers
