Objective: to develop a personal balance sheet using Excel, and learn the techni
ID: 2818166 • Letter: O
Question
Objective: to develop a personal balance sheet using Excel, and learn the technique to analyze one’s financial position.
Due date: September 16th, 2018
There are two parts to this assignment. Please show all parts on one Excel file. For each part, also explain your answers verbally including the assumptions you made to complete the part.
Part 1. Create a personal balance sheet for Mr. Ecks using the information that is provided below.
Ecks makes $60,000 per year.
His current bank accounts include a savings account with $8,000, another savings account with $13,000, and a checking account with $10,000.
He has $400 in cash, and bond investments valued at $7,000.
He has other personal property (furniture, electronics) valued at $1,000.
He holds three credit cards. The current balances and interest rates on these cards are $400 (12%), $1200 (15%), and $2300 (18%).
He owns a car valued at $4,000. He does not have an auto loan.
He has a house valued at $200,000 and his mortgage balance is $97,000.
In the first spreadsheet, show his present situation.
In a second spreadsheet, show his personal balance sheet after he does the following:
Pays off all three credit cards
Wins a lottery of $1,000,000
Sells his car and purchases a new car valued at $60,000
Pays off his mortgage loan balance
Part 2. Prepare a new spreadsheet for Mr. Ecks showing his lifetime economic net worth.
In the first spreadsheet, show his financial net worth and economic net worth.
In the second spreadsheet, illustrate how you obtain Mr. Ecks’ gross human capital and implicit liabilities
Assume he is now 30 and lives until age 90
Assume the appropriate valuation rate for Mr. Ecks is 4%
Assume his salary annually grows at 3%, and he earns salary until age 60
Assume his non-discretionary consumption is 50% of his salary
Specifically, the second spreadsheet should show:
A column containing age # (i.e. 20, 21, 22, …)
A column containing year # (i.e., 1, 2, 3, …)
Four columns showing, by year, salary, PV of salary, implicit liabilities, and PV of implicit liabilities
A set of cells to the upper right that contain the valuation rate, gross human capital, implicit liabilities, financial assets, and explicit liabilities
A few clarifications:
Part 2 a): Use information up to Part 1 b) to calculate his financial net worth.
Part 2 b): Assume his non-discretionary consumption is 50% of his salary and is fixed at $10,000 per year when he retires.
Explanation / Answer
The above problem is related to financial Statement. The first part explains about the assets and liabilities availed in the current year:-
Year 1
Year 2
In Year 1 Assets are more than Liabilities but in Year 2 his financial net worth is equal to
243400+3000+60000= 306400-100900 =$205500
Liabilities Amount Assets Amount Credit Cards 3900 Savings Account(8000+13000+10000) 31000 Mortgage 97000 Investment 7000 Cash 400 Furniture, electronics etc 1000 Car and House 204000 Total 100900 Total 243400 Liablities Amount Assets Amount Total Liabilities 100900 Total Assets in Year 1 243400 Lottery(100000-97000) 3000Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.