You will develop an individual ‘life’ financial plan using the excel spreadsheet template provided in the Project D2L folder. You are to fill in (and add rows and columns as needed) the information for both your personal income statement and your personal balance sheet. You will need to add the respective formulas in the spreadsheet, so that the income statement and balance sheet are accurate and “tie” together.
For example, your school loans (if you have them or expect them) would go into your liabilities and then you’d have lines in recurring expenses on your income statement as you pay off the loan. Input your expected salaries, costs, deductions, investments, etc. and summarize your assumptions in a clear manner so the logic can be easily understood. You will decide what your expected salary increases will be, when you will buy a house, what the house will cost, how much money you will plan to save in your retirement account vs. your regular savings account and when you will make major purchases.
Research median salary data for your expected career and median home prices and/or rent prices in the area you plan to live. You will need to provide an amortization table for any loans that you incur (such as home mortgage and or car payments. You could do this in a separate worksheet in your spreadsheet).
You will also need to determine your income tax rate for each year. This can be found using the current US federal income tax brackets. You will need to research how these work and apply the correct formula in your spreadsheet. For simplicity, state income tax can be ignored.
You are expected to enter the appropriate formulas into the spreadsheet (linking your income statement and balance sheet appropriately) so that you will have a picture of your budget, your growing net worth, and your retirement portfolio. Also, some line items are missing on the spreadsheet and you will need to add them to make your spreadsheet complete and accurate.
Develop a plan that will generate an adequate amount of money to retire at age 55 (if you are currently in your early twenties. If you are older, then you may provide an appropriate retirement age). Complete the analysis out to age 95 to ensure that you will have adequate resources to provide for your entire life. Research what average rate of return can reasonably be expected in your 401k and other investments. Assume that you receive NO social security benefits in later years (therefore, your investments have to provide all your required income in your retirement years). Again…. clearly show your assumptions and explain your logic in the spreadsheet.
For salary increases, you can grow them at some rate higher than inflation, but you may want to limit the salary increases to matching inflation after 45 or 50 years old to be conservative. You need to grow your expenses over time with both inflation and life style. For example, your travel budget should dramatically increase as you get out of school and begin your career/life. Health care should probably grow at 2X inflation at least for the next 10 years.
Also, we will ignore SS and Medicare and state taxes for this spreadsheet to simplify it. However, there are rows in red that we won’t use for the project that have these items. They are there if you want to add them to the spreadsheet after the class for your own personal use.
The spreadsheet has a LOT of comments. They have been put there to answer the majority of the questions that we (my TA and me) get during office hours. Please look to the spreadsheet for answers before you come to me or the TA with questions.
1) You’ll need to change row 2 to have the correct years.
2) You’ll need to change row 3 to align with the age for the person you are representing in the spreadsheet. I’d recommend starting the date/age with the first year you are out of college and begin working.
3) Row 9, 10, and 11 of the personal income statement would only occur later in life when you are taking money out of your retirement accounts. These distributions will be taxed as ordinary income.
4) Exemptions have been eliminated in the tax legislation for 2018, however, there are still child credits. Research the appropriate tax rates, credits (google 2019 IRS tax tables, click on Forbes URL, it has a good summary of tax rates).
5) You either take a standard deduction or an itemized deduction in a respective year, not both.
6) If you take the standard deduction you can’t deduct the interest on your house. You can only deduct interest on a home loan and property taxes if you itemize your deductions.
NOTE: We have had issues with students using Google Docs. It is a great collaborative tool, however, when you go to upload to D2L to hand in the assignment, often the formula’s of the spreadsheet are lost. Since we require to see the formulas to grade the project properly this is not acceptable. You may want to use the Microsoft free online version of excel.
The benefit is that it seems to download acceptably and the formulas are intact, however, it renders very slowly when working collaboratively among group members. I recommend that you just have the spreadsheet on your local computer work on it and use some revision control and email from member to member to make the necessary edits. This is collaboratively a bigger hassle, but you will get the spreadsheet to download accurately.
Last Updated on March 19, 2020 by EssayPro