Challenge Exercise 1 - How Much Do You Really Need to Earn to Become a Millionaire? Learning Objective: In this exercise, you will use your Excel skills and knowledge of Finance to "fact check" a story reported on the CNBC web site in 2019. The basic question is: How much salary do you need to earn to save 15% of your income and retire with $1 million? You will build the model and test the results. Inputs are incorporated in the spreadsheet template: - Savings goal at retirement: $1,000,000 - Base case annual rate of return: 4.00% - Base case starting age: 25 - Age at retirement: 65 - Years to save: Retirement age - Starting age Background: We have done several exercises on this theme. In E01, you showed how compounding results in significantly more savings over time than does the assumption of "simple interest". In E02, you demonstrated how starting a savings plan early in life results in tremendously more savings at retirement than if you delay. In E04, you showed how to build a model of savings for a goal and then using those savings over time to fund retirement. E05 and E06 showed how to use present value techniques to value bonds. In 2019, I came across an article on the CNBC web site stating how much you would have to EARN in order to save 15% of those carnings and retire with $1 million. The purpose of this exercise is determine whether CNBC did their math correctly. Required Assignment: - Launch Exeel and download the template from Canvas. - This spreadsheet comes in 2 parts. In Part 1 (through row 15), you can use the PMT function to determine the annual savings required over an assumed number of deposits and at an assumed annual rate of return to generate $1 million at age 65 (assumed retirement age). Then, assuming that savings amount is 15% of your salary, you can calculate the amount of salary needed. Then, you can use a Data Table to figure out what salary you would need at different assumptions of starting age and annual return. - In Part 2, you will build a schedule to verify your answer. - Here are some tips for Part 1 and Part 2. - Part 1: - The base case Goal, Annual return, Starting age, and Age at retirement have been entered for you. Geal (Future Value): Base case annual retum, r : Base case starting age: Age at retirement: Years to save (Retirement age - $ tart age) PV of Goal at r : Annual savings required: If savings = 15% of salary, lmplied salary = Data Table to determine annual salary if.a. Inputs for schedule to check results at base case: Annual deposit interest rate, r. $1,000,000 4.00% 25 65 Number of deposits Use PV of single sum formula Use PMT function in Excel Compute implied salary Annual rate of return, r 4.00%. 5.00% 8. 60:4 25 30 35 $0.00 4.00% Beginning of End of Year lnterest earned year balance Deposit and reinvested $0 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 48 49 50 51 52 53 55 56 57 58 59 60 61 62 62 63 64 End of year balanse Deposit Deposit - Balance after 40th depesit and interest earned is the $1,000,000 goall Challenge Exercise 1 - How Much Do You Really Need to Earn to Become a Millionaire? Learning Objective: In this exercise, you will use your Excel skills and knowledge of Finance to "fact check" a story reported on the CNBC web site in 2019. The basic question is: How much salary do you need to earn to save 15% of your income and retire with $1 million? You will build the model and test the results. Inputs are incorporated in the spreadsheet template: - Savings goal at retirement: $1,000,000 - Base case annual rate of return: 4.00% - Base case starting age: 25 - Age at retirement: 65 - Years to save: Retirement age - Starting age Background: We have done several exercises on this theme. In E01, you showed how compounding results in significantly more savings over time than does the assumption of "simple interest". In E02, you demonstrated how starting a savings plan early in life results in tremendously more savings at retirement than if you delay. In E04, you showed how to build a model of savings for a goal and then using those savings over time to fund retirement. E05 and E06 showed how to use present value techniques to value bonds. In 2019, I came across an article on the CNBC web site stating how much you would have to EARN in order to save 15% of those carnings and retire with $1 million. The purpose of this exercise is determine whether CNBC did their math correctly. Required Assignment: - Launch Exeel and download the template from Canvas. - This spreadsheet comes in 2 parts. In Part 1 (through row 15), you can use the PMT function to determine the annual savings required over an assumed number of deposits and at an assumed annual rate of return to generate $1 million at age 65 (assumed retirement age). Then, assuming that savings amount is 15% of your salary, you can calculate the amount of salary needed. Then, you can use a Data Table to figure out what salary you would need at different assumptions of starting age and annual return. - In Part 2, you will build a schedule to verify your answer. - Here are some tips for Part 1 and Part 2. - Part 1: - The base case Goal, Annual return, Starting age, and Age at retirement have been entered for you. Geal (Future Value): Base case annual retum, r : Base case starting age: Age at retirement: Years to save (Retirement age - $ tart age) PV of Goal at r : Annual savings required: If savings = 15% of salary, lmplied salary = Data Table to determine annual salary if.a. Inputs for schedule to check results at base case: Annual deposit interest rate, r. $1,000,000 4.00% 25 65 Number of deposits Use PV of single sum formula Use PMT function in Excel Compute implied salary Annual rate of return, r 4.00%. 5.00% 8. 60:4 25 30 35 $0.00 4.00% Beginning of End of Year lnterest earned year balance Deposit and reinvested $0 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 48 49 50 51 52 53 55 56 57 58 59 60 61 62 62 63 64 End of year balanse Deposit Deposit - Balance after 40th depesit and interest earned is the $1,000,000 goall