I really need help with \'a\', and with \'b\', I only need help understanding ho
ID: 2335217 • Letter: I
Question
I really need help with 'a', and with 'b', I only need help understanding how to set up the equation in Excel to calculate the required savings amount for years 11-25. Thank you.
Short Case Study with Excel The purpose of this problem is to (1) solve a simple problem with MS Excel and (2) practice solving a complicated problem using MS Excel. Problem 4.26 (50 pts) Suppose you have three goals in your financial planning for saving money. First you would like to be able to retire in 25 years from now with a retirement income of $10,000 (today's dollars) per month for 20 years. Second, you would like to purchase a vacation home in Sedona in 10 years at an estimated cost of $500,000 (today's dollars). Third, assuming you will live until your life expectancy, say 20 years after your retirement, you would like to leave a cash contribution to UC Merced in the amount of $1,000,000 (actual dollars) so that they dedicate a building in your name. You can afford to save $2,000 (actual dollars) per month for the next 10 years because you got a great job at an engineering firm. Assume that the general inflation rate is 4% compounded monthly and the property value in Sedona increases at an annual rate of 5% compounded monthly. Your first retirement withdrawal will be made 25 years and 1 month from now. Before retirement, you would be able to invest your money at an annual market interest rate of 10% compounded monthly. But after retirement, you will invest your assets in more conservative financial assets at an annual rate of 6% compounded monthly a) Calculate total amount you will need achieve your first goal (i.e. $10,000 (today's dollars) per month is how much in total at the end of 20 years, hint: this will be impacted by inflation and the market interest rate)? (20 pts) b) What is the required savings in each month in years 11 through 25? Show your results with a sample calculation sheet (20 pts) and a MS Excel sheet (10 pts) You will need to submit: Sample handwritten calculation sheet with all necessary calculations to show how you thought through this problem. Clearly mark calculations for parts a) and b) A printed MS Excel sheet, that is nicely formatted, with all the information in one page (i.e. the text isn't cut off, etc) For your MS Excel sheet, you will get full credit as long as you attempt the problem fully, on one page, and the text is not cut off. We are not looking for a "correct answer" but more an attempt at analyzing the problem. Feel free to write a sentence or two to explain your thought process.Explanation / Answer
USD 4,68,156.2943
b. Results are shown in the excel attached and highlighted
USD 4,68,156.2943
b. Results are shown in the excel attached and highlighted
- 4% 6% Principal Interest 0 1 2 3 4 5 6 7 8 9 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 36 37 38 39 40 41 42 43 44 45 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 10000 10407.42 10831.43 11272.72 11731.9867 12209.97 12707.42 13225.14 13763.95 14324.72 14908.33 15515.72 16147.85 16805.74 17490.43 18203.02 18944.64 19716.47 20519.75 21355.75 22225.82 23131.34 24073.74 25054.54 26075.3 27137.65158 325651.819 338919.4 352727.5 367098.1 382054.3 397619.8 413819.4 430679 448225.6 466487 485492.4 505272.1 525857.6 547281.9 569579 592784.5 616935.5 642070.4 668229.3 695454 723787.9 325651.819 319230 312934.8 306763.7 300714.3 294784.2 288971.1 283272.6 277686.5 272210.5 266842.5 261580.4 256422 251365.4 246408.5 241549.3 236785.9 232116.5 227539.2 223052.1 218653.5 USD 56,44,534.70 468156.2943