Loans and Bills

1
Your Virtual Life
2
Excel/ Loan Amortization

• Open the Excel workbook attached at the bottom of
  this webpage called Bill.xlsx.
• On the worsheet called Bills, insert a custom
  header (Insert, Header Footer, Header) that
  gives your name, date, and page .

3

• Right click on sheet tab name at the bottom of
  the page.
• Select Insert....
• Switch to the Spreadsheet Solutions tab.
• Select Loan Amortization.
• Change the name of this sheets bottom tab to
  Sallie Mae.

4

• In the Lender name box type in Sallie Mae. This
  will be the lender supplying you with your
  federal student loan.
• After grant/ scholarship awards and personal
  student/ family funding, the average student at
  public colleges in 2008-2009 (National Center for
  Education Statistcs) graduated with a 23,000
  loan to repay for their education. Use this
  figure for the Loan amount. (Chances are most of
  you will be much higher than this when you
  graduate since NJ is a high maintenance state!)
• Fixed government student loan rates are up to
  6.8. Use this for the Annual interest rate.

5

• The standard Federal loan is repayed over a 10
  year stretch after graduation. Use this for the
  Loan period in years.
• You will make 1 payment per month so use 12 as
  the Number of payments per year.
• Imagine you are in your Senior year of college
  now and will graduate this coming June. Loan
  repayment usually grants a 6 month grace period
  before starting to pay back the loan to give you
  time to get situated career-wise. Use the first
  day of this coming Decembers date as your Start
  date of loan.

6

• Leave Optional extra payments empty.
• Once you enter the information, notice what your
  Scheduled payment on the right is every month
  for the next 10 years.
• Notice the Total interest on the right you will
  pay backremember that this is above and beyond
  the 23,000 you are paying back. The interest is
  the profit they make from loaning you the money!

7
College Loan 2

• Now lets see what someone that wants to go on to
  a graduate degree program might experience.
• USA Today reported that "Tom Dillon, 19, a
  pre-pharmacy major at the University of
  Connecticut, is carrying 52,000 in student
  loans. And he's just getting started. When he
  gets his pharmacy doctorate in four years, he
  expects his debt to exceed 150,000!

8

• Open a new loan amortization sheet
• Change the bottom tab name of this sheet to
  Dillon.
• Loan amount will be Dillons amount of 150,000.
• Lender name will be Wells Fargo so you can see
  how much more expensive a personal loan from a
  private bank will be.
• Annual interest rate will be 9.02.
• Loan period in years will be 20 to make the
  monthly payment more affordable.
• Start date of loan will be the first day of this
  coming Decembers date.
• Number of payments per year will be 12.

9

• Leave Optional extra payments empty.
• Once you enter the information, notice what your
  Scheduled payment on the right is every month
  for the next 20 years.
• Notice the Total interest on the right you will
  pay backremember that this is above and beyond
  the 150,000 you are paying back!!!

10

• House Loan/ Mortgage
• Now lets se what it would be like to try and buy
  a house after you graduate and start your first
  job. Open a 3rd amortization sheet and rename the
  bottom tab Mortgage.

11
House Loan/ Mortgage

• A very average house might cost 400,000 in this
  area. Banks might expect you to put 10 as a
  downpayment before being willing to loan you the
  remainder. Your Loan amount will be 360,000
  since you already had to give 40,000 as a
  downpayment.

12

• Lender name will be Sovereign Bank.
• Annual interest rate current mortgage rates vary
  from bank to bank, but 6.59 is common.
• Loan period in years A typical first time buyer
  would often obtain a 30 year mortgage.
• Number of payments per year will be 12.
• Use the first day of the upcoming July date as
  your Start Date of Loan.

13

• Leave Optional extra payments empty.
• Once you enter the information, notice what your
  Scheduled payment on the right is every month
  for the next 30 years.
• Notice the Total interest on the right you will
  pay backremember that this is above and beyond
  the 360,000 you are paying backand how old will
  you be when you finally own that home!!!???

14
What do you think ...time to go home and thank
mom and dad for how hard they are working to pull
all of this off? time to be a little more
serious and work harder in school to get good
grades so colleges and employers will offer you
more financial aid or better salaries? time to
ante up at home a little more and help out
without being asked? time to start becoming a
little less materialistic?... so now lets
move on to accepting the fact that getting an
apartment is a more likely option right out of
school and try and survive paying those monthly
bills.
15
Keeping Recordsof Personal Bills
16

• Select the sheet labeled Bills in your workbook.
• Remember to double click the column dividers to
  auto-size each column to fit your data as you
  continue the exercise! If you ever see in a
  cell, it means the cell needs to be wider to see
  the text or numbers.

17
Entering Your Expenses

• Highlight all of columns A-O. These cells will
  need to recognize numbers as dollar values. Hold
  down Control 1 (one), then select Number,
  Category, Currency. For Symbol choose , check
  to see if 2 decimals is selected, and select OK.

18
Row 4/ College Loan

• Decide if you think you will be a typical college
  grad or more like those that go on to law school
  or medical school in your career.
• Use either the Sallie Mae monthly payment
  (264.68) for the average college graduate
• or
• Dillon (1351.52)for advanced degrees like
  doctors, lawyers etc.
• Put it in B4. Copy and paste this amount using
  the auto-fill handle in C4-M4.

19
Row 5/ Rent

• Your share of the rent from http//app.com/ .
  Select Apartments on the top of the new page.
  Decide what area of NJ and options matter to you
  in apartment offerings
• Location, location, location?
• 1,2, or 3 bedrooms? If you choose 1 bedroom you
  pay all of the rent, 2 bedrooms pay ½ the rent, 3
  bedrooms pay 1/3 the rent.
• Furnished or unfurnished?
• Laundry facilities?
• Pets?
• Put your share of the rent from B5-M5.

20
Row 6/ Car Payment

• Forget the new car...you have college loans to
  pay back. Buy a used car from Enterprise
• Type in your zip code and select make/ model from
  the drop downs.
• Select one of the available cars. Below the car
  will be your payment in a phrase like Estimated
  at per month.
• Record your payment in B6-M6.

21
Car Insurance

• Paid bi-yearly January payment 1,478 (make the
  same payment again in July)

Put this in B7 and H7 only!
22
Gas Card
(C8-N8)

• Find the tank size (not the MPG!!!) of the car
  you bought-- Google the following phrase
• Model of your car fuel tank capacity...look for
  a search result that says some number of gallons
  (it will likely be between 14-30 gallons---eg
  15.5 gl)
• Now use the calculator on your iGoogle page or
  the computer (Start/ Programs/ Accessories/
  Calculator) and calulate your monthly fuel bill
  for the car...
• ...You will fill it on average 5 tanks per month,
  so your monthly gas will be 5 x fuel tank
  capacity x current price per gallon (What is the
  lowest you have noticed lately?...ask if you
  dont watch the price at the pump when mom and
  dad fill up).
• Put this figure in B8-M8.

23

• Credit Card
• This is only food and essentials!!!
• January/ 700
• February/ 420
• March/ 393
• April/ 380
• May/ 456
• June/ 444
• July/ 472
• August/ 450
• September/ 350
• October/ 439
• November/ 602
• December/ 1,512

The joys of having roomies
B9-M9
24

• Your share of the Electric bill
• January/ 32
• February/ 29
• March/ 30
• April/ 30
• May/ 24
• June/ 27
• July/ 34
• August/ 37
• September/ 25
• October/ 22
• November/ 21
• December/ 27

(B10-M10)
25

• Your share of the Natural Gas bill
• (We will pretend it is not covered in the rent)
• January/ 70
• February/ 67
• March/ 65
• April/ 69
• May/ 50
• June/ 49
• July 51
• August/ 50
• September/ 49
• October/ 52
• November/ 53
• December/ 64

B11-M11
26
Home and Wireless
Choose the TV, Phone Line, Internet B12-M12 Comca
st- 99/ month (Triple Play) 70/ month (Double
Play) Verizon- 85/ month (Triple Play) 70/
month (Double Play) Optimum- 100/ month (Triple
Play) 89/ month (Double Play)
Which services do you need?
Choose the Cell Phone Plan/ B13-M13 (Unlimited
talk, text, web) Verizon - 130/ month ATT-
120/ month T-Mobile - 85/ month Sprint- 115/
month Metro PCS- 40/ month
Triple Play
Double Play
27

• Misc. ATM
• -January/ 350
• -February/ 270
• -March/ 300
• -April/ 260
• -May/ 320
• -June/ 300
• -July/ 570
• -August/ 340
• -September/ 260
• -October/ 300
• -November/ 280
• -December/ 440

B14-M14
28
Using the Auto Sum Feature

• Select cell N4.
• Select the drop down for AutoSum on the Formulas
  tab, Function Library section, and select Sum and
  then press ENTER. Note the other choices
  available for future reference.
• Copy and paste the N4 formula in N5-N14 using the
  autofill handle (Dont know what this is? Ask!!).
• This will give you the spending for the year in
  each category... Notice which items cost you the
  most. Could you save by making some better
  choices?

29
Inserting a Function

• Select cell B15 and select the fx symbol on the
  left side of the toolbar on your toolbar.
• When the Insert Function box comes up, make sure
  the word Sum is highlighted in the Select a
  function box. Select OK.
• A new Function Arguments box will appear. Drag it
  out of the way if necessary and highlight the
  cells you want it to add up by clicking and
  dragging your mouse on cells C4-C14.
• Click OK if Number 1 says B4B14 in the function
  box. Use the auto-fill handle to paste this
  formula in C15-M15.
• This shows your total monthly expenditures.
  Notice which months are most expensive think...
  why that might be the case?

30
Type Your Own formula

• In cell N15 put thefollowing formula
• SUM(N4N14)
• This will give you the total spending for the
  year.

31
Figure out your Monthly Disposable Income(The
amount of income left to an individual after
taxes have been paid, available for spending and
saving.)

• In cell A17 put the name of the career you chose
  in Naviance.
• In cell B17 put the NJ salary for your career. Go
  to Naviance and look up the starting salary for
  your career under the wages tab by using the
  figure under the 90 made at least column.
• Uncle Sam, Christie, and other deductions will
  immediately affect your gross income. In your
  early career you will likely lose 15 off the top
  in taxes, health care, etcand thats being
  nice!!! In cell C17 type the following formula
• B17.85
• Select Enter (Return). You now have your
  disposable income for the year.

32

• Set up a formula to automatically deduct your
  bills and expenses from the yearly. In cell D17
  type the following formula C17-N15
• BTWyou havent bought any clothes, gone
  shopping, taken vacations, or put money away
  for a house yet!!!
• Are you solvent or are you in debt? Should you
  reconsider your freedom until your salary
  increases and move back home?...
• Or
• did you burn that bridge behind you in your
  teen years of exploring and testing your
  parents limits?
• Do you want to buy a house someday? How long will
  it take you to save for a down payment? With all
  of that cash you had laying around after your
  first year of moving out?
• Hope you get the message
• good grades lead to good jobs good behavior
  builds bridges thinking about this is a step--
  acting on it is a long journey that requires time
  management, self discipline, and good character.