Suppose that during your working career you accumulate $300,000 in a retirement savings account. Upon retiring, you stop making deposits to your account and start withdrawing funds annually as income to live on. This assignment looks at how long your retirement account will last as a function of the initial withdrawal amount, the rate of return earned on the account, and the annual inflation rate applied to your withdrawals (e.g., 3%). In particular, assume that you make withdrawals from your account at the beginning of each year, so if you withdraw $25,000 the first year of retirement, your intermediate account balance would be $275,000. Then, over the course of the first year, assuming a 4% rate of return, your savings increase by $11,000, leaving a balance of $286,000 at the end of the year 1 (= the start of year 2). At the start of year 2, you withdraw $25,000*(1.03) = $25,750 from the account (in order to keep up with inflation), and then earn 4% on the balance over the rest of the year 2, and so on. Withdrawals in subsequent years keep increasing by the annual inflation rate of 3% but you cannot withdraw more than the beginning-of-year balance, so your account never becomes negative. Set up an Excel model that tracks, for 35 years, the following 5 quantities (in $) in successive columns: Beginning Balance, Annual Withdrawal, Intermediate Balance, Annual Return, and Ending Balance. At the end of 35 years, calculate the duration of your retirement account in years (COUNTIF(Beginning Balance Range,">0") will return the number of years that begin with a positive balance) and the average withdrawal amount (AVERAGEIF(Artnual Withdrawal Range,">0") will find the average of all withdrawals that are positive). ID # : 96431 To do: 0. Name your file "LastnameFirst-Asmt2.xlsx" and submit on iLearn by 8:50 pm on 9/16/20. 1. On Sheet 1: Draw an influence chart for this situation, with initial withdrawal amount as the decision variable, and duration and average withdrawal amount as the two outputs. Use Excel's Insert > Shapes to make rectangles, arrows, etc. This will take more time than on Assignment 1. 2. On Sheet 2: Create a spreadsheet model following the formatting used in class for inputs, decision variables, calculated quantities and outputs. Use range names for important quantities. a. Your account initially contains $300,000 + the last 5 digits of your student ID number, e.g., if your ID number is 987654321, then your initial account balance is $354,321. b. Initial withdrawal amount is $25,000. Withdrawal inflation rate is 3% per year. d. Annual rate of return on your account is 4%. 3. On Sheet 2: Make a line chart showing beginning-of-year account balance over the years. 4. On Sheet 2: Make a 1-way data table where the initial withdrawal varies from $15,000 to $50,000 in $5,000 increments; the output columns should be the duration of your retirement account and the average withdrawal amount. 5. On Sheet 2: Make a 2-way data table for the duration of your retirement account in years) where the initial withdrawal amount varies from $15,000 to $50,000 in $5,000 increments, and the annual rate of return on the account varies from 1% to 7% in 1% increments. Highlight all cells in the data table for which the duration of your retirement account is at least 15 years. 6. On Sheet 3: Briefly answer the following four questions: a. What kind of relationship is there between the initial withdrawal and duration? b. What kind of relationship is there between the initial withdrawal and the average withdrawal amount? If your initial withdrawal is $20,000 and you believe that you'll live 20 years in retirement, what annual rate of return must you earn on your account? d. If you think you'll live a long time in retirement, what are the keys to a successful retirement income strategy? C. Suppose that during your working career you accumulate $300,000 in a retirement savings account. Upon retiring, you stop making deposits to your account and start withdrawing funds annually as income to live on. This assignment looks at how long your retirement account will last as a function of the initial withdrawal amount, the rate of return earned on the account, and the annual inflation rate applied to your withdrawals (e.g., 3%). In particular, assume that you make withdrawals from your account at the beginning of each year, so if you withdraw $25,000 the first year of retirement, your intermediate account balance would be $275,000. Then, over the course of the first year, assuming a 4% rate of return, your savings increase by $11,000, leaving a balance of $286,000 at the end of the year 1 (= the start of year 2). At the start of year 2, you withdraw $25,000*(1.03) = $25,750 from the account (in order to keep up with inflation), and then earn 4% on the balance over the rest of the year 2, and so on. Withdrawals in subsequent years keep increasing by the annual inflation rate of 3% but you cannot withdraw more than the beginning-of-year balance, so your account never becomes negative. Set up an Excel model that tracks, for 35 years, the following 5 quantities (in $) in successive columns: Beginning Balance, Annual Withdrawal, Intermediate Balance, Annual Return, and Ending Balance. At the end of 35 years, calculate the duration of your retirement account in years (COUNTIF(Beginning Balance Range,">0") will return the number of years that begin with a positive balance) and the average withdrawal amount (AVERAGEIF(Artnual Withdrawal Range,">0") will find the average of all withdrawals that are positive). ID # : 96431 To do: 0. Name your file "LastnameFirst-Asmt2.xlsx" and submit on iLearn by 8:50 pm on 9/16/20. 1. On Sheet 1: Draw an influence chart for this situation, with initial withdrawal amount as the decision variable, and duration and average withdrawal amount as the two outputs. Use Excel's Insert > Shapes to make rectangles, arrows, etc. This will take more time than on Assignment 1. 2. On Sheet 2: Create a spreadsheet model following the formatting used in class for inputs, decision variables, calculated quantities and outputs. Use range names for important quantities. a. Your account initially contains $300,000 + the last 5 digits of your student ID number, e.g., if your ID number is 987654321, then your initial account balance is $354,321. b. Initial withdrawal amount is $25,000. Withdrawal inflation rate is 3% per year. d. Annual rate of return on your account is 4%. 3. On Sheet 2: Make a line chart showing beginning-of-year account balance over the years. 4. On Sheet 2: Make a 1-way data table where the initial withdrawal varies from $15,000 to $50,000 in $5,000 increments; the output columns should be the duration of your retirement account and the average withdrawal amount. 5. On Sheet 2: Make a 2-way data table for the duration of your retirement account in years) where the initial withdrawal amount varies from $15,000 to $50,000 in $5,000 increments, and the annual rate of return on the account varies from 1% to 7% in 1% increments. Highlight all cells in the data table for which the duration of your retirement account is at least 15 years. 6. On Sheet 3: Briefly answer the following four questions: a. What kind of relationship is there between the initial withdrawal and duration? b. What kind of relationship is there between the initial withdrawal and the average withdrawal amount? If your initial withdrawal is $20,000 and you believe that you'll live 20 years in retirement, what annual rate of return must you earn on your account? d. If you think you'll live a long time in retirement, what are the keys to a successful retirement income strategy? C