Financial Project

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.  You will be comparing your future finances with someone who is not a college graduate.  Assume that the median annual income for a high school graduate is $22,500 (although the actual value is higher).  Use this value in comparison statements with proper absolute and relative change statements.

In this project you are required to do the following (you must submit both Excel and Word documents): 

Create an excel sheet to help calculate the correct values for housing and retirement.  Although this is not a graded part of the project, only Excel can produce the correct result (and not a simple calculator for most questions).  Refer to the excel help in the book or notes in your course on how to find mortgage or retirement values.  You will use the PV, FV, PMT formulas in Excel.

Your written portion of the project will consist of answering short answer questions given below.  Each short answer solution should be a minimum of 5 sentences and 50 words.  For each, use proper quantitative reasoning and wording to address the solution.  This should include sophisticated and elegant solutions to clearly convey your message to the reader.  Make sure to use your spreadsheet to answer “what if” questions with specific values and provide support for your statements.  To do this, make changes in your spreadsheet and analyze the results of the manipulations – be quantitative and specific with correct absolute and relative change statements.

HINT: As you are writing your absolute or relative change sentence, make sure to address “from” what value “to” what value.  Writing sentences such as, “The absolute/relative change is…” will NOT be counted as correct.  What is the meaning of an absolute or relative change in each scenario?

 

Occupation

1. Explain what job you have chosen and why. Use the median salary from the following website: https://www.bls.gov/ooh.  Compare your salary to that of a high school graduate.  How does your salary compare to the high school graduate?  Use BOTH an absolute change and relative change statement for your comparison.

 

Mortgage Loan

2. Assume you are planning on spending a certain amount of income towards a mortgage. If you spend 18% of your monthly salary towards a house, what is the highest price house you can afford?  What is the highest priced house a high school graduate can afford?  Use an absolute or relative change statement to compare the house prices of the high school graduate and your house.  Assume that the standard APR is 4.45% for 30 years.  Make sure to show at least 1 sample formula that you used in Excel to get the house price.
3. If you were to take a 15 year loan instead of a 30 year loan and the APR was a 3.9% instead of 4.45%, what is the highest priced home you can afford using 18% of your salary? Assuming all payments are made on time, what amount of interest will you pay on the home?  Refer to your mortgage value for 30 years (APR 4.45%).  If you made all payments for the life of the loan as scheduled, how much interest will you pay on the 30 year mortgage?  Compare the amounts of interest paid on both mortgages using an absolute or relative change statement.

 

Retirement Account

4. Assume that your selected job has a retirement plan that allows you to save for 25 years. If 5% of your monthly income is put into a retirement account, how much should you expect if the annual return is 6.50%?  How much should a high school graduate expect to get if they also save 5% of their salary for 25 years?  Use an absolute or relative change statement to compare the retirement accounts of the high school graduate and your retirement. Make sure to show at least 1 sample formula used to get the retirement price.
5. Consider the retirement investment with 6.50% APR for 25 years using 5% of your monthly income. Which scenario (A, B, or C) will give you the best value for retirement?  For each scenario, find the relative change from the original retirement amount to the doubled amount.
6. If you were to double the APR (from 6.50% to 13%), how much would you have in your retirement account? B. If you were to double the contribution into the account (from 5% to 10% of monthly salary), how much would you have in your retirement?
7. If you were to double the time that you are investing (from 25 years to 50 years), how much would you have in your retirement account?

 

Salary Increase

6. If your job gives a salary raise of 1.30% each year, what will be your salary after 10 years? (In other words, after 10 raises have occurred.)  Write an absolute or relative change statement comparing the current salary to the salary after 10 raises.  What will be your salary after 20 years?  Write an absolute change statement from your current salary to the salary after 20 years.
7. How long will it take (rounded to the whole year) for your salary to be twice as much as when you started assuming you get a 1.30% raise every year? If the high school graduate also had a 1.30% raise each year, how long would it take for him/her to get twice as much money (rounded to the whole year)?  Compare the years for both the college graduate and the high school graduate.  Explain the reasoning for your answer.

 

Submit BOTH Excel and Word files following your instructor’s directions for submission.