As an Ivy Tech student, you are making an investment in your education. This project will look at how that investment will pay off if you graduate and get a job in your desired field.
The median annual income for a high school graduate is $25,000. This is the value that you will be comparing your expected salary and lifestyle to in the following areas:
1.) Income Comparison: Research your expected income after you graduate college. Assume you obtain whatever level of education needed to enter your desired area of employment and that you will have a salary equivalent to the annual Median salary found at: http://www.bls.gov/oes/current/oes_in.htm#27-0000 *You will need to click on your desired career in order to see the Annual Median wage.
• Explain what your desired area of employment is and what the median annual salary for that position is.
• Compare your expected salary with that of the median income of a high school graduate.
2.) Housing Comparison: Current 30 year mortgage rates are at 4.25% and a good rule of thumb is to spend no more than 28% of your gross income on house payments (Note: We are using a different formula here than the 'Total Expense Ratio' in the book, so you will not need to know tax and other info). Use Excel to determine the largest value of a house that could be bought using a 30-year mortgage at 4.25% with payments that are 28% of the gross monthly income for someone with only a high school diploma and then do the same calculation using your expected salary. Also, assume 3.5% interest on a 15-year mortgage, and calculate the largest value of house that could be afforded under this type of mortgage. Make a couple statements comparing your findings. Use Excel to create an amortization table for ONE of the following: a 30 year mortgage with your anticipated income, a 15 year mortgage with your anticipated income, a 30 year mortgage for someone with the income of a high school graduate, and a 15 year mortgage for someone the income of a high school graduate. Highlight the tenth year of the amortization table. How much of the loan have you paid off after 10 years? (This is related to the idea of home equity, which is the current value of the home minus the amount owed on the home. Note that you build equity more quickly with a 15-year mortgage, because more of each payment goes toward repaying the principal.) Calculate the total price of each home. How much more interest is paid? (We are ignoring other closing costs that in reality we would have to pay.)
• Compare the value of the house that a high school graduate can afford and that you expect to be able to afford for each type of mortgage.
• Label the amortization table in Excel.
• Make sure you have highlighted the tenth year in the amortization table.
• Compare the total price of the 30 year mortgage with the total price of the 15 year mortgage for BOTH your expected salary, and that of a high school graduate.
• Explain what Excel function was used for these calculations.
3.) Retirement Comparison: Your future employer will match your 401k investments 50%, but your high school counterpart does not receive help with their retirement plan from their employer. You and your high school counterpart are able to invest at least 5% of your salary in a 401k investments that will earn a 2.99% annual return. Use Excel to determine the balance on such a 401K after 40 years of investment for the high school graduate and for your expected income. Also use Excel to determine how many years it would take for the high school graduate and for your expected investment to reach $150,000.
• Compare the value of the 401K investment of the high school graduate and for your expected income.
• Compare the time it would take for the 401 k investment of the high school graduate and for your expected income to reach $150,000.
• Clearly label these calculations within Excel.
4.) Raises: The high school graduate's income is not expected to increase over the next five years, but assume that your field will guarantee you a 2.5% raise on your annual salary each year for the next five years. Determine what your new annual salary will be given these five years of raises. Using this new income, recalculate the 30 year mortgage from part 2. Also recalculate the 40 year 401 k information from part 3 using this new income (use the new income and 40 years for this, don't worry about the complication of 5 years of changes).
• Compare your initial salary with the salary after 5 years of raises.
• Determined the increased salary.
• Address how your increased salary might impact your mortgage and 401K values.
5.) Change of Careers: It can be hard to predict if you will stay in your current career forever. Pick a second career from: http://www.bls.gov/oes/current/oes_in.htm#27-0000 and look up the Median annual salary. Compare the salary of this job to the one that you picked in part 1 of the project. How would this change affect your financial planning?
• Compare the salaries of your two chosen careers.
• Reflect on how a change of careers might affect your housing and 401K values.
The Excel file contains the amortization table and all computations for the housing and retirement sections including different income levels. The Word file contains over 400 words explaining which functions were used and how income information was obtained.