please link google sheet or excel file so i can see the formulas . I have trouble setting up the one way data table with duration and average withdraw
Supposel 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 ofthe initial withdrawal amount, the rate of return earned on the account, and the annual ination 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 rst 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 l: 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 ination], 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 ination rate of 3% but you cannot wimdraw 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 S) 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 [COUNTIFlBeginning Balance Range,\">0") will return the number of years that begin with a positive balance) and the average withdrawal amount (AVERAGEIF[AnnuaI Withdrawal Range,">0") will nd the average of all withdrawals that are positive). To do: 0. Name your file 'LastnameFlrstAsthMsx" and submit on iLearn by 8:00 pm on 9116120. 1. On sheet 1: Draw an inuence 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. 3. Your account initially contains $300,000 + the last 5 digits of your student ID number, e. g., ifyour ID number is 987654321, then your initial account balance is $354,321. b. Initial withdrawal amount is $25,000. c. Withdrawal ination rate is 3% per year. d. Annual rate of return on your account is 4%. On Sheet 2: Make a line chart showing beginningofyear account balance over the years. 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? c. If your initial withdrawal is $20,000 and you believe that you'll live 20 yea rs 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? PE\
