Question

NET WORTH ANALYSIS

Based on Public Records and other sources we have accumulated information regarding John, as follows:

1. According to the County Assessor of Property, we have identified that John’s residence was valued at $100,000, in 2008, and that the property maintained the same value for subsequent years. The monthly mortgage payment was $500. By the end of 2008, John owed $90,000 and by the end of 2009, he owed $40,000. As of December 2010, the balance was paid off.

2. Research at the County Clerk’s Office identified that in 2008, John owned a 2006 Toyota. According to the Auto Dealer’s Black Book the Toyota was valued at $20,000, and John owed $10,000 at year end. Information recovered from his residential trash indicated the auto loan to be $166.66 monthly.

3. In January 2009, John traded in the Toyota, and purchased a used 2008 BMW 399i Coupe, valued at $40,000. Throughout the year, he paid a monthly auto loan of $208.22, and by year end the balance on the loan was paid down to $5,000. In 2010, he paid the same loan payment through the end of the year, and apparently paid additional funds as the loan was paid off by the end of the year. The BMW maintained its value throughout 2010.

4. In January 2010, John purchased a new Lexus 453 Sports Coupe, valued at $50,000. According to the dealership John paid in a Cashier’s Check from Bank of America II.

5. It was discovered that John maintained an investment portfolio at Bank of America II, for each of the three years. The value of the account maintained a $30,000 balance each year.

6. In 2009, John purchased a speed boat valued at $30,000 and still owned the boat throughout 2010.

7. John had a certificate of deposit at Bank of America II. It has been learned that the CD was valued at $25,000 in 2008 and 2009, and that by the end of 2010 an additional $25,000 was purchased.

8. Payroll records indicate that John was paid $37,000 in 2008, $40,000 in 2009 and $42,000 in 2010. Additionally, he receives $4,000 yearly from a part-time job.

9. General living expenses for 2008 and 2009 was $15,000 annually and in 2010 increased to $20,000 annually.

				FY 2008	FY 2009	FY 2010
	Assets
	Residence
	Automobiles
	Investment Portfolio
	Boat
	Certificates of Deposit
	Total Assets
	Liabilities
	Mortgage
	Auto Loans
	Total Assets
	Income
	Salary
	Other Income
	Total Income
	Expenses
	Mortgage Payments
	Auto Loan Payments
	General Living Expenses
	Total Expenses
	Net Worth =
	Change in Net Worth
	Plus Total Expenses
	Total
	Less Known Income
	Income from Unknown Sources

Answer 1

Hello there. I am solving the question is excel and attaching screenshots of the same, first with numbers and then with formulas, which will explain the calculations which have gone behind the numbers.

However, I still had to make some assumptions which are as follows:-

1. For automobiles, the values are given for year beginning. As no data is given to get year end values, I have assumed that the value is same at year end.

2. Same with boat, as no decrease in value is given, I have assumed it to be same throughout.

Net worth= Total assets less Total Liabilities

Hope it helps.