*Please show all steps and functions used in Excel
Problem Statement Suppose that you begin planning your investments on your 40th birthday (the sooner you start, the better!), retire at the age of 65, and expect some amount of growth in your salary year to year. Further suppose that you intend to invest some consistent proportion of your income each year and wish to understand the final value of your retirement account based on what proportion of your income you invest each year. Finally, to make the math easier, let's assume that you deposit each year's investment all at once at the beginning of the year. NOTE: Like the demonstration video from this Module, you can tweak and reuse this template for your own personal planning :) Part 1 Suppose you've set a goal of having a million dollars in your retirement portfolio by the age of 60; begin by developing a what-if model using the template provided to represent the problem above i.e. to assess what balance you can expect at the age of 60. To make the math easy, assume for the moment that both your salary growth and your portfolio's return on investment will be consistent each year. To validate your model, enter an Annual Investment Rate of 7.5%; if you have built the model correctly, you should find a Total Profit of $907,782. Once you've confirmed that your what-if model is correct, use Goal Seek to determine the minimum Annual Investment Rate necessary to have a balance of at least $1,000,000 when you turn 60. File Home Insert Page Layout Formulas Data Review View Add-ins Help Analytic Solver Data Mining A1 fx B D E F G H I J K L 1 2 Starting Age 3 Current Salary 4 Starting Portfolio Balance 5 Salary Growth Rate 6 Portfolio Growth Rate 7 8 Annual Investment Rate 40 $85,000 $50,000 5% 10% 8.71% 9 Year Beginning Balance 1 2 3 4 5 6 7 8 9 10 Salary Investment Amount nvestment Return Ending Balance Age at Next Birthday 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 11 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 12 13 14 15 16 17 18 19 20 Part 1 Part 2 Problem Statement Suppose that you begin planning your investments on your 40th birthday (the sooner you start, the better!), retire at the age of 65, and expect some amount of growth in your salary year to year. Further suppose that you intend to invest some consistent proportion of your income each year and wish to understand the final value of your retirement account based on what proportion of your income you invest each year. Finally, to make the math easier, let's assume that you deposit each year's investment all at once at the beginning of the year. NOTE: Like the demonstration video from this Module, you can tweak and reuse this template for your own personal planning :) Part 1 Suppose you've set a goal of having a million dollars in your retirement portfolio by the age of 60; begin by developing a what-if model using the template provided to represent the problem above i.e. to assess what balance you can expect at the age of 60. To make the math easy, assume for the moment that both your salary growth and your portfolio's return on investment will be consistent each year. To validate your model, enter an Annual Investment Rate of 7.5%; if you have built the model correctly, you should find a Total Profit of $907,782. Once you've confirmed that your what-if model is correct, use Goal Seek to determine the minimum Annual Investment Rate necessary to have a balance of at least $1,000,000 when you turn 60. File Home Insert Page Layout Formulas Data Review View Add-ins Help Analytic Solver Data Mining A1 fx B D E F G H I J K L 1 2 Starting Age 3 Current Salary 4 Starting Portfolio Balance 5 Salary Growth Rate 6 Portfolio Growth Rate 7 8 Annual Investment Rate 40 $85,000 $50,000 5% 10% 8.71% 9 Year Beginning Balance 1 2 3 4 5 6 7 8 9 10 Salary Investment Amount nvestment Return Ending Balance Age at Next Birthday 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 11 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 12 13 14 15 16 17 18 19 20 Part 1 Part 2
