Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
Homework answers / question archive / Sara B
Sara B. Goode wishes to retire in the year 2022 with personal savings of $500,000 (1997 spending power). Assume that the expected inflation rate in the economy will average 3.75% per year during this period. Sara plans to invest in a 7.5% per year savings account, and her salary is expected to increase by 8.0% per year between 1997 and 2022. Assume that Sara's 1997 salary was $60,000 and that the first deposit took place at the end of 1997. What percent of her yearly salary must Sara put aside for retirement purposes to make her retirement plan a reality?
please help me answer in excel, thankyou
1. First, Annual Salary Income in 1998 => B9 = (B8*(1+$B$2)). For every subsequent year until 2022, we simple copy paste the same formula
2. Second, Annual Savings in 1997 => C8 = (B8 * $B$4) where we assume annual rate of savings from salary income to be 10% to begin with such that we are saving 10% of our income as savings. We will later adjust this rate (B4) to get the desired rate for desired target retirement at the end. For every subsequent year until 2022, we simple copy paste the same formula
3. Third, ROI (return on investment of our savings in 1998 => D9 = (C8 * $B$3) since we are given in the question that first deposit took place at the end of 1997. For every subsequent year until 2022, we simple copy paste the same formula
4. Fourth, we find Total value of our annual savings in 1998 => E10 = (C9 +D9). For every subsequent year until 2022, we simple copy paste the same formula
5. Fifth, we consider inflation factor (Inflactor) to get the real worth of our annual value of savings. We take 1997 inflation factor as 1 as it is mentioned that Sara’s target retirement amount in 2022 has to be at 1997 purchasing power. In 1998, Inflactor => F9 = (F8*(1+$B$1)). For every subsequent year until 2022, we simple copy paste the same formula
6. Sixth, we calculate inflation adjusted total worth of annual savings which in 1997 => G8 = (E8/F8). For every subsequent year until 2022, we simple copy paste the same formula
7. Seventh, we aggregate the total sum of inflation adjusted total worth of savings during 1997-2022 to find the accumulated retirement target which is equal to => G34 =SUM (G8:G33).
8. Finally, we change different values of B4 to get the desired retirement value in G34. For given G34 target in question which is $500,000, we find the most approx. B4 value is 17.3724% which is the percent of her annual salary that Sara must put aside every year to get her retirement savings target of $500,000 at the end of 2022.
