Balance Sheet Module

1.  Using the Balance Sheet Excel spreadsheet, complete a personal balance sheet for your client (a married couple, herein referred to as the client) given the information below. Where appropriate, please change any titles (e.g., stock mutual fund) to provide a specific description of each asset or liability.

  1. Checking account balance: $60,000
  2. Home Depot Stock: 400 shares @ $50 per share
  3. Fidelity Magellan Mutual Fund: $10,000
  4. Roth IRA – husband invested in Vanguard Windsor Fund: $50,000
  5. Roth IRA—wife invested in Vanguard Explorer Fund: $60,000
  6. 401K husband invested in Vanguard Total World Stock Index ETF: $40,000
  7. Home: Purchased for $250,000 four years ago and has appreciated at 3% annually; original mortgage: 30 year, $200,000, 6% annual (nominal) rate, monthly payments.
  8. Porsche 911: Purchased three years ago for $50,000 and has been depreciating at 10 percent per year; original loan-- $45,000, six year, 6% annual (nominal) rate, monthly payments
  9. AMC Gremlin (Google it!): Purchased used 11 years ago. Current Value: $100, no loan
  10. Furniture, household assets, etc.: To be conservative and given these are usually depreciating assets toward $0, value of these assets will equal $0.
  11. Credit Card Balance: $1,000
  12. Student Loans, husband: $80,000
  13. For simplification, deferred taxes = $0
  14. Do not worry about the date; however, it is important to remember that the balance sheet measures the financial health at a single point in time (e.g., December 31, 20XX), not over time (e.g., Salary of $200,000 this year).

2.  Is your client a millionaire? If not, how much does their net worth have to increase for them to become millionaires?

3.  If your client bought a $1,000,000 real estate investment at fair market value, would they now be millionaires? Explain.

4.  Your client decides to reduce the mortgage balance by $30,000; therefore, their net worth will increase by $30,000. Agree? Explain.

5.  Your client decides to buy a used $40,000 boat at fair market value and obtain a $30,000 loan, by how much does your client’s cash, total assets, total liabilities, and net worth change?

6.  Your client receives a $10,000 gift and goes out and buys a new car for $30,000, taking out a $15,000 loan. How much does your client’s cash, total assets, total liabilities, and net worth change?

7.  Another couple has $10 million in assets, primarily in stock and real estate? Are they millionaires? Are they financially wealthy? Explain.

What else would you like to know about this subject?