PROJECT 3: Financial Project Student Guide

NOTE: This is meant only as a guide! Numbers and titles may appear different in this guide but the placement and

spreadsheet setup is the same. Minor changes may also have been made such as font size, bold letters, and highlighting

which is not required!

This is a help with calculations. Please refer to your Financial Project description for all the different questions that you

need to answer in your report for each category. You are not required to turn in your spreadsheet but you will want to

explain how you used each function in your paper.

INCOME COMPARISON

a.

Look up MEDIAN salary on the website given. Specific directions showing how to locate the median

salary are posted in a separate document.

b. See project description for information to be included in reflection paper.

HOUSING

Determine the largest value of a house that could be bought using a 30 year mortgage or 15 year mortgage at the rate

given by searching online for the current APR. In this example below, 3.5% is used for 30 years. NOTICE: $100,000 is

used as an example for many of the calculations and 25% is taken out for your mortgage value. You will use the salary

that you found for your own job on the website for your desired career. A value of $20,000 is also assumed for a High

School Graduate.

You will need to reference the project for what percentage of income is dedicated to a mortgage!

Excel Calculations for Housing Comparison (Your Salary)

1) Figure your gross monthly income. For example: if you were going to make 100,000 a year, then your gross

monthly income would be 100,000 12 = 8333.33. This will bw based on data from the website listed for

median income for your declared field of study - not just a salary you want to make.

YOUR SALARY 12 = _____________________

2) Next, calculate 25% of your gross monthly income. For example, if your gross monthly income is 8333.33

then you will multiply 8333.33 x 0.25 = 2083.33

3) Use Excel to determine the largest value of a house that can be bought with your desired salary.

a.

Open the Excel document

b. In any cell type =PV(0.035/12, 30*12, __________) and press enter (your answer from part 2 should

go in the blank)

Check your formula to make sure it uses the exact numbers from the project!

c.

EXPLANATION OF FORMULA

i. PV is present value

ii. 0.035/12 is the monthly interest rate

iii. 30*12 means that you will make 12 payments a year for 30 years

iv. The last number is the monthly payment that you can afford (use your actual value from

part 2).

d. The value of the house that you can afford will appear in the cell.

Excel Calculations for Housing Comparison (High School Salary)

1) Figure gross monthly income for an average high school graduate. 20,000 12 = _________________

2) Calculate 25% of the gross monthly income. Multiply your above answer by 0.25 _________________

3) Use Excel to determine the largest value of a house that can be bought with the high school diploma salary.

a.

In Excel, in any cell: type =PV(0.035/12, 30*12,__________ ) and press enter (in the blank, you will

put your answer from part 2)

b. EXPLANATION OF FORMULA

i. PV is present value

ii. 0.035/12 is the monthly interest rate

iii. 30*12 means that you will make 12 payments a year for 30 years

iv. The last number is the monthly payment a high school graduate can afford (use value from

part 2).

c.

The value of the house an average high school graduate can afford will appear in the cell.

RETIREMENT

Determine the largest value of retirement you could have after a designated amount of years for investment. For this

guide, 40 years will be used at a return of 4.5% using exactly 5% of our income each month.

You will need to reference

the project for what percentage of income is dedicated to a retirement account and what the rate is!

Excel Calculations for Retirement Comparison (Your Salary)

1) Figure your gross monthly income that is going directly towards retirement. For example: if you were going

to make 100,000 a year, then your gross monthly income would be 100,000 12 = 8333.33. Then, 5% of this

would be 0.05 x 8333.33 = 416.67

Try this calculation for your own salary, and for the high school graduate.

2) Use Excel to determine the largest value of a retirement with your desired salary.

a.

Open the Excel document

b. In any cell type =FV(0.045/12, 40*12, __________) and press enter (your answer from the previous

part should go in the blank.)

c.

EXPLANATION OF FORMULA

i. FV is present value

ii. 0.045/12 is the monthly interest rate

iii. 40*12 means that you will make 12 payments a year for 40 years

iv. The last number is the monthly payment that you can afford (use your actual value from

part 2).

d. The value of the house that you can afford will appear in the cell.

3) Apply the same technique to finding a high school graduate's retirement value.

4) Next, find the years needed to get a value of $150,000 for a retirement account. Note: this may be lower

than 40 years for some professions! You can do this by adjusting the value for the time (the number of

months needed) inside of the equation for your retirement function. Choose the month that is closest to the

value of $150,000, to one decimal.

NOTE: The project asks you to find the absolute or relative change of some items. Recall, absolute change is (New - Old)

and the relative change is (New -Old)/Old. You will want to calculate that for each item required by the project! 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 this scenario?