Project Purpose

As a college student, you are making an investment in your education. This project will look at how that investment could pay off after graduation and working a job in your desired field. You will be comparing your future finances, with a college graduate career, with someone who is a high school only graduate. Assume that the median annual income for someone who has only a high school diploma is $38,700.

Project Overview

You will use Microsoft Excel (students are required to START WITH THE PROVIDED STUDENT TEMPLATE) to answer financial questions using Excel financial functions (PV, FV, PMT), formulas, and data. Project questions will be answered in the Project 2 Quiz in IvyLearn. Students will make several comparative statements from a high school only graduate, to their college graduate career.

General Project Notes

All numerical values, other than the first 6 rows and cells B22 and C22, should use cell references and functions or formulas to determine values. Multiple sheets are not used in this project.

Each question response needs to be a minimum of 3 sentences and in paragraph form. Rephrase each question, explain how you found the result using Excel and show examples of all formulas and functions.

When writing comparative statements using absolute or relative change, identify the high school only graduate as the initial or old and the college graduate career as the final or new. DO NOT state the absolute change, relative change or difference between them is...

Sample comparative statement: As a ________, I can expect to make an annual salary of _______ more (or less if it is less) than or ______% more (or less if it is less) than the average high school only graduate.

Round all values to the nearest whole number, dollar, year or percent.

Recall in Excel, "=" indicates a formula, "/" is division, "*" is multiplication and "-" is subtraction.

Excel functions/formulas used in this project (when appropriate cell references to data should be used):

Absolute Change =final cell reference-Initial cell reference.

Relative change for cells formatted as a percent =(Absolute Change/Initial)

Relative change for cells formatted as a number =(Absolute Change/Initial)*100

Payment function =PMT(monthly interest rate, number of deposits, -amount of loan)

Present Value =PV(monthly interest rate, number of deposits, -payment amount)

Future Value =FV(monthly interest rate, number of deposits, -deposit amount)

Use your class notes for support on all functions/formulas. Instructors will go over all Excel needed in class or in video tutorials. Use only the Excel functions discussed in class or in the instructions or videos.

Completing the Excel Template and Answering the Quiz Questions

The first 6 rows of the template are data, the remaining rows are divided into 5 sections, corresponding to the 5 questions. Instructions and hints on how to complete each template section are provided, followed by the corresponding quiz question. Questions are answered in Project 2 Submit Answers here quiz in Module 5.

Open the STUDENT TEMPLATE found in module 5 below the instructions.

If you have Microsoft Excel, simply double-click the file to open it and begin completing the template. Otherwise, use Google Sheets: https://sheets.google.comLinks to an external site.

• Login using your Ivy Tech username and password.
• Click the add symbol "+" in the bottom, right corner.
• Click File > Import > Upload to Browse or drag-and-drop the template file into Sheets

1. Median Income Comparisons: To answer question 1, of the Project 2 quiz, complete row 2 - 10 of the student template. Note: The median income for a high school only graduate and the cost of the vehicle are provided and are to be used by ALL students. Do NOT change the values provided in B3 and B6.

a) Complete Cells B2, B4 and B5 (no function/formula needed).

• Enter your Name, Career Name and the Career Median Income by following the directions below.

To Find the median income of your degree career:

Go to the "U.S. Bureau of Labor Statistic Occupational Outlook Handbook" website at the following link https://www.bls.gov/ooh.Links to an external site. The provided website must be used to determine the median income of your IvyTech degree/certificate pursuit. Be sure to list the website in your question one response. Search for your college career median income by going to "Occupation Groups" on the left or by "Search Occupations" on the right. Your instructor will demonstrate in class or on video.

b) Complete Row 9 - Cell B9, C9, D9 and E9 (cell references and formulas required).

• To populate B9 and C9, use cell references to B3 and B5 (the median annual incomes).
• Create a formula in D9, using cell references to B9 and C9 to determine the Absolute Change from the high school only to your career annual income. Example: =C9-B9
• To find the relative change as a percent in E9, use cell references to D9 (the absolute change) and "/" to divide by B9. Make sure to format the cell as a percent or to *100 to make the decimal form into a percentage. DO NOT DO BOTH. Example: =D9/B9 or =(D9/B9)*100

c) Complete row 10 - Cell B10, C10, D10 and E10 (cell references and formulas required).

• To find the monthly income in B10 and C10, write a formula in each cell. Use cell references to the annual incomes and "/" to divide by 12 (12 months/year) to find monthly income.
• Find the absolute and relative change from the HS only to your college graduate career, follow the same process you used in row 9 to find absolute and relative change as a percent.

You now have everything you need to answer question one of the Project 2 quiz in IvyLearn.

Question 1) Comparing Median Annual Incomes (follow all directions in Project 2 instructions)

a) What is the college graduate career you have chosen? What inspires you to pursue this career? (3 points)

b) What are the median annual incomes for the high school only graduate and your college graduate career? Where did you find the income for your college graduate career? (3 points)

c) Use Excel and the provided template to find the absolute and relative change from the high school only income(initial) to your college graduate career income(final). Report relative change as a percent. Show examples of and explain how you found the values using Excel. (4 points)

d) Write a meaningful sentence(s) using absolute and relative change. See sample in project instructions.(4pts)

1. Mortgage Comparisons: To answer question 2, complete row 13 and 14 of the student template.

For this project, assume that a maximum mortgage payment is 25% of one's gross monthly income. For this project, the mortgage is a 30-year term with a fixed 7.2% APR.

a) Format cells B13, C13 (cell references and formulas needed).

• Use cell references to the monthly salaries in B10 and C10 and write a formula to find 25% of the monthly incomes in cells B13 and C13. Example =25%*B10

Note: If a percent symbol is used in a calculation in Excel, Excel will convert the percent to decimal form as needed. Alternatively, you can convert to a decimal and drop the % symbol and use =.25*B10.

b) Complete cells B14, C14, D14 and E14 (cell references, formulas and the PV function are required).

• Format B14 and C14 using the Excel PV (Present Value) function, the provided terms of the loan and cell references to the maximum mortgage payment allowed (B13 and C13). The result of the PV function will be the recommended maximum amount of mortgage. See your notes to complete the PV function using the methods shown in class or on video by your instructor.
• Find the absolute and relative change from the HS only to your maximum mortgage amount (not the payment amount) in D14 and E14 (use similar process as you did above for D9 and E9).

You now have all the information needed to answer number 2 of the Project 2 quiz.

Question 2) Comparing Mortgages (follow all directions in Project 2 instructions)

Assume that a maximum mortgage payment is 25% of one's gross monthly income. For this project, consider a 30-year mortgage with a fixed 7.2% APR.

a) What is the maximum mortgage payment the high school only graduate and your college graduate career? Explain and show an example of how you found the values using Excel. (4 points)

b) Use the maximum payment amounts found in part a), and the Excel PV function to find the largest mortgage the high school only graduate and the college graduate career can afford. Show an example of and explain how you found the values using the PV function. (5 points)

c) Use Excel to find the absolute change and relative change of the present values (mortgage amounts) from the HS only graduate to your college graduate career. Use the values in a meaningful sentence(s). (5 points)

1. Retirement Account: To answer question 3, complete row 17 and 18 of the provided template.

Assume 8% of the monthly incomes is deposited monthly into a retirement savings account. For this project the monthly deposits will be placed into a retirement account with an APR of 5.2% for 25 years.

a) Format cells B17, C17 (cell references and formulas needed).

• Use cell references to the monthly incomes in B10 and C10 and write a formula to find 10% of the monthly incomes. See similar process in row 13.

b) Complete cells B18, C18, D18 and E18 (cell references, formulas and the FV function are required).

• Format B18 and C18 using the Excel FV (Future Value) function, the terms of the retirement account and cell references to the monthly retirement deposits (B17 and C17), to find the future amount in the retirement accounts. The steps for using the FV formula were covered in your class on instructor video. Use the FV function as done in class.
• Find the absolute and relative change from the HS only graduate to your college graduate career retirement future value.

You now have all the information to answer number 3 of the Project 2 quiz.

Question 3) Retirement Account (follow all directions in Project 2 instructions)

Assume a monthly deposit of 8% of the monthly incomes, for 25 years, is placed into a retirement account with an APR of 5.2%.

a) How much is deposited monthly into the HS only graduate and your college graduate career retirement account? Show an example of and explain the formula used to find the amount of the deposits. (4 points)

b) If the monthly deposit is invested into a retirement account with the terms above, what will be in the high school only graduate retirement account after 25 years? What will be in your retirement account at the end of 25 years? Show an example of and explain how you found the values using the FV function. (5 pts)

c) Use Excel to find the absolute and relative change of the retirement account balances at the end of the term. Write a meaningful sentence using the absolute change and relative change values. (5 points)

1. How many years to reach 1 million in the retirement account: Complete Row 21 and 22 of the provided template to prepare to answer question 4. Assume the same conditions stated in Question 3.

a) row 21 (use cell references, formulas and the FV function).

• Use the Guess-and-Check method, with the FV function from number 3, and change the number of years in the retirement accounts, until you find the first whole year the accounts exceed $1 million. The NPER function can be used instead. If NPER is used and example and explanation of the formula must be shown.

b) Complete row 22 (use cell references, and formulas).

• Type the number of years found in "a" to first exceed $1 million in B22 and C22.
• Format D22 and E22 to find the absolute and relative change for the number of years from the HS only to yourself to exceed $1 million (use similar process as you did above for D9 and E9).

You now have all the information needed to answer number 4 of the Project 2 quiz.

Question 4) How Long to reach $1 million? (follow all directions in Project 2 instructions)

Use the terms of the FV accounts in number 3.

a) Use the Guess-and-Check method, changing the number of years in the FV functions from question 3 to find the first whole year the account will exceed $1 million for the HS only and yourself. Explain the process you used to find the answer. Note: NPER can be used as alternative. (5 points)

b) Use Excel to find the absolute and relative change of the number of years to reach $1 million in retirement from the high school only graduate to the college graduate career. Write a meaningful sentence using the absolute and relative change value. (5 points)

c) Assume you start saving for the retirement in 5 years, how old will you be when you reach the $1 million goal? Does this age align with your desired retirement age? Do you believe $1 million is a reasonable goal for the amount needed in the account? (4 points)

1. Car Loan: Complete Rows 25-27 of the provided template to prepare to answer question 5.

You are looking for a new vehicle and a car salesman is trying to convince you to buy a New Subaru Forester. Cell B6 contains the vehicle sticker price is $37,000 (taxes and fees included). You are making a $5,000 down Payment. The term of the loan is 6 years, with a 6.3% APR.

a) Format cell C25 (use cell references and formulas).

• Calculate the amount of the loan for this vehicle (sticker price - down payment) using Excel. Use a cell reference to B6 and "-".

b) Format C26 (use cell references, formulas and PMT function).

• Find the amount of the payment for this loan under the given terms. Use the PMT function, the terms of the loan and cell references to C25. See class notes for details of PMT function use.

c) Format C27 (use cell references and formulas).

• What percentage of your gross monthly income would this payment be? Explain how you found the percentages using Excel. Hint: Write a formula using cell references to the part (the payment), "/" to divide the payment by the whole (the gross monthly income), and ensure cell is formatted as a percent. If not add to your formula to convert to a percentage.

d) Is this monthly payment a reasonable payment given your likely future income? Explain your answer.

Question 5) Car Loan (follow all directions in Project 2 instructions)

You have secured a car loan with an APR 6.3%, for 6 years and will be putting $5,000 down.

a) How much will be borrowed if the vehicle price is $37,000 (taxes and fees included) and you make a $5,000 down payment? Write a formula in Excel using a cell reference to B6 and "-". (3 points)

b) Use the Excel PMT function to find what the monthly payment for this loan would be using the above terms. Show an example of and explain the PMT function used. (5 points) c) What percentage of your gross monthly salary would this payment be? Explain how you found the percentages using Excel. (4 points)

d) Is the monthly payment a reasonable payment given your likely future income? Explain your answer. (2 pts)

Question 6) Upload a copy of your financial Spread Sheet. The file must have an XLS or XLSX extension. No PDF, Word documents, etc. will be accepted as an upload for this part of the project. Directions found in question 6 of the quiz. (30 points)