Page of 3

ZOOM

FINA 3724 Excel Project -2 Due Date: 11/01/2021 1 Retirement Analysis The purpose of this project is to make students utilize the concepts and the tools we learn in class and apply them in real world problems. You will prepare a comprehensive retire plan for yourself. The plan is to be as real and meaningful as you can possibly make it. All estimates and assumptions are to accurately reflect your realistic expectations about the future. I acknowledge that there are both finance and non-finance students in this class. I, however, strongly believe that all of you will benefit from this project at a personal level regardless of your major. Please read the information below carefully for the details of the project, and dont hesitate to contact me if/when you have questions and/or need clarification. Project-1 consists of 4 steps: The mathematical analysis is to be performed in Excel to be flexible so that they can be updated as your life progresses. If you are married, you may use joint income and joint expenses. Also, be sure to consider Social Security and company pension plans. Step 1: Determine your annual retirement expenses will be. Use current dollars. Show each major element. Include everything. Don't cut yourself short. Allow enough funds to pursue recreational activities. Dont forget that for most retired people medical expenses are a major expense. This is a critical part of making this assignment meaningful. Put this info on a separate tab and label it Budget. Step 2: a. Estimate what you believe inflation will average between now and when you retire. Justify your estimate. Note that historical inflation figures can be found on the internet. b. Determine how many years you will work between now and when you retire. Then estimate how long you will live after retirement. Clearly state these assumptions and justify them in your write-up. c. Calculate the future value of your annualized budget based on the assumed inflation rate and the number of years that you plan on working until retirement. d. Put this info on a separate tab and label it FV of Budget.

FINA 3724 Excel Project -2 Due Date: 11/01/2021 2 Step 3: a. Compute how large a retirement balance is needed to retire. (This will be the present value of the stream of annual withdrawals you will need to make during your retirement years. Note that each year while you are retired the amount you need will continue to grow by the inflation rate. But at the same time, the remaining retirement balance after each withdrawal will earn the rate of return. b. Estimate what average rate of return that you believe is possible given the types of investments you plan to make. Justify your estimate. Be conservative. This rate may change between now and when you retire as you attempt to avoid risk. For example, you may begin with mostly stocks and may end with mostly bonds. c. This rate is what you will as your discount rate when you are calculating the PV of future cash flows. d. Now that you have the PV of retirement balance you need to have accumulated until the age you plan to retire, you can calculate the annual savings (or annual contributions, or annual payments) you must make into an investment account that will pay you the rate of return that you assumed that you can achieve. e. Put this info on a separate tab and label it Retirement Balance. Step 4: a. Compute how much must be saved every year to reach your retirement goal. It is likely that you will save more later in life than at the present since your income will rise. Thus, you will have to estimate your expected income at each point in your career and then estimate what portion of this will be saved. The annual savings that you calculated in Step 3, part d should give you an idea about the annual savings you need to start with. b. After you calculate the savings you must have each year; you can calculate the savings you must have each month to meet that annual goal. c. Given your annual savings; you will find the FV of each years savings in the year you plan on retire. (Hint you can name this column Accumulation. The FV of annual savings you must have each year until your retirement will keep accumulating in each row of this column. d. You can name the next column Remaining. In each row of this column, you will subtract that years accumulated amount from the retirement balance you must have at the beginning of your retirement.

FINA 3724 Excel Project -2 Due Date: 11/01/2021 3 e. The last row is the age you will retire. Thus, the row in the Cumulation column must match the retirement balance you must have, and the row in the Balance needed column must be zero. f. Put this info on a separate tab and label it Annual Savings. Note 1: I pay attention to the format. Neat work goes a long way! Note 2: There is no specific right or wrong way to set this assignment up. It will be graded on correctness of calculations, reasonableness of assumptions and their justifications, flexibility, and elegance of spreadsheet design. Note that this is a significant assignment and counts 10% of your total grade. Our quality expectations will be very high. Your project should reflect an understanding of TVM, risk and return, alternative investment opportunities, and spreadsheeting. Note 3: Feel free to discuss any aspect of this assignment on the discussion board. It is appropriate to share and discuss layout, assumptions, investment rates, inflation rates and any other issue where you want feedback. I expect the discussion to be mostly among yourselves but will monitor and contribute where needed. Note 3: A grading rubric is attached to your assignment on Canvas. Overall, you gave 100 points available to you for this project. Grading will be based on format, calculations, and rigor.