FINANCE-304 Financial Modeling

FINANCE-304 Financial Modeling

Fall 2024

Group Project 1

Due at 11:59pm on Sunday, September 29th, 2024 

Important Instructions: 

1. This group project is a group effort. Everyone in the group should collaborate and solve each question together.  

2. If you are the group leader, submit your group project as an Excel file (“Excel MacroEnabled Workbook" (with suffix “.xlsm") if you use the self-defined Getformula() function) through Canvas (One Submission Each Group). Any late submission will NOT be accepted.

3. Please make sure to write each of your group member’s full names at the top of the first worksheet.

4. Work on a separate worksheet for each problem in one Excel file.

5. You must use Getformula() or Formulatext() to show your formulas whenever you use a formula to do the calculations. Otherwise 2 points will be deducted as a punishment out of the 10 points.

You really need to draw a timeline for this problem. Today is your 40th birthday. You expect to retire at age 65 and actuarial tables suggest that you will live to be 100 years old. You want to move to Hawaii when you retire (on your 65th birthday). You estimate that it will cost you $150,000 to make the move (on your 65th birthday). Starting on your 65th birthday and ending on your 99th birthday, you withdraw $35,000 for annual living expenses. Assume the interest rate is 3%.  

(1). How much will you need to have saved by your retirement date? (2 points. Hint: this subquestion asks you to calculate the value of your total spending after you retire (the moving cost and the annual living expenses), on your 65th birthday. You can think your 65th birthday as “today" and calculate the “present value" of those costs.)  

(2). What is your savings calculated in (1) worth right now? (2 points. Hint: In this question, “right now” means now, your 40th birthday.)

(3). You start saving for this goal one year later (first saving on your 41st birthday) till your 65th birthday. How much would you need to save each year? (2 points. Hint: use the PMT() function).