HOFSTRA UNIVERSITY HOFSTRA UNIVERSITY FRANK G. ZARB SCHOOL OF BUSINESS Department of Finance SPREADSHEET PROJECT GOAL: The goal of this project is for each student to develop skills in using...

Finance 101 Term Projecct


HOFSTRA UNIVERSITY HOFSTRA UNIVERSITY FRANK G. ZARB SCHOOL OF BUSINESS Department of Finance SPREADSHEET PROJECT GOAL: The goal of this project is for each student to develop skills in using spreadsheets for time value analysis. A properly developed spreadsheet allows one to easily recalculate any results quickly by changing only a couple of numbers and then simply printing the new versions. GENERAL REQUIREMENTS: This project is may be done alone or as groups of up to three students! Students/groups submitting identical spreadsheets will receive a zero. GENERAL INFORMATION: You expect that upon graduation you will get a job that pays $50,000 per year in the first year. Until the end of the tenth year, your salary will grow 10% per year. Beginning in year eleven, your salary will grow 3% per year until the end of the fortieth year. At the end of year forty, you will retire. During all of your working years, you plan on contributing 6% of your salary to your 401k account. Your employer will match 60% of your contribution up to a maximum of 5% of your salary (e.g. if you contribute 2.5%, your employer will contribute 1.5% and if you contribute 12%, your employer contributes 3% or 60% times 5%). Until retirement, you plan on investing in the stock market and believe that you should be able to earn a 7.5% rate of return. After retirement, you plan to shift a significant portion of your portfolio into money market instruments and bonds so that you expect to earn only a 5.5% rate of return. At retirement, your life expectancy will be 23 years. During retirement, your income will need to grow 2% per year to offset inflation. At the time of your death, your assets will be completely exhausted with nothing left to relatives, friends or charities. Finally, you expect that Social Security will have run out of money by the time you are eligible. Assume that all cash flows occur at the ends of the years including retirement withdrawals. Ignore taxes. SPECIFIC REQUIREMENTS: All numbers must be entered into the spreadsheet except where derived from other numbers (for example, all of the interest rates should be entered into the spreadsheet once. Any formula that uses an interest rate should refer to the cell containing the proper rate. NOTE: You may NOT use built-in spreadsheet functions for any of the computations. Your spreadsheet should be well organized and properly labeled. Appearance is important. 1) Calculate your salary and 401k contributions for each year prior to retirement. 2) Calculate the value of your retirement account each year (until your demise). 3) Calculate your retirement income for each year subsequent to your retirement. Print out your spreadsheets plus one additional spreadsheet with formulas displayed in the cells in which they were used. Make sure that your name(s) is(are) at the top of the spreadsheet (in the computer and on the printout-no credit will be given otherwise) and that all the results are properly labeled. You should use Excel. Reminder: ALL CALCULATED NUMBERS MUST BE ENTERED AS FORMULAS. DUE: Monday, Dec. 9, 12:50 PM sharp (Late spreadsheets will not be accepted).