Lucas and Sara Investment and Retirement Case Study
Lucas and Sara are about to celebrate their one-year anniversary. They are both 22 years old. Sara graduated and has her first salaried job with benefits. Lucas has only two semesters left of school and will focus on his studies until he graduates. As part of the transition into full time work and more stable income, Lucas and Sara have decided to get serious about long term savings. They specifically want to focus on retirement savings and plan to retire at age 67.For retirement planning purposes, they assume they will live another 20 years after retiring.
Sara's new job pays a yearly Gross Income of $52,000 (not including benefits). Use the top portion of the Excel "Retirement Income Projection Worksheet" to determine how much Sara will need to monthly contribute to her retirement needs. For planning purposes, use a 2.5% inflation rate. Keep in mind this is still an early estimate, but still a reasonable starting point.
After calculating their desired annual retirement income, Lucas and Sara consider the following general sources of retirement income:
Social Security: This is a fixed percentage of income based on the case study.
Company-sponsored benefits:
oPensions vary by company, but Lucas and Sara are hoping to find work that provide this benefit. They recognize that if this does not happen soon, they may need to find other ways of saving that money for themselves.
oSince Sara's current company provides a 401k, she and Lucas would like to begin investing in that right now (It provides a 7% annual rate of return)
Personal Investment Plan: Lucas and Sara realize that they will need to begin saving money in other ways as soon as possible. For now, they decide to focus on other investments. They have found one with a historic rate of return of 8% annually, in a reputable mutual fund. In the future they will look to other types of accounts and investments as well.
Use the middle portion of the "Retirement Income Projection Worksheet" to explore different percentages of retirement income that might come from these different sources. Note how changing the percentage in column C affects the future annual income will need to come from each account. After considering a similar spreadsheet, Lucas and Sara decide to plan for:
30% of their future annual income from either Social Security or a company pension, which is fixed and is based on current retirees.
30% of their future annual income from Sara's 401k
40% of their future annual income from other personal investments
Using these percentages and the worksheet, answer the following questions:
1.What is the total amount they will need in their 401k to retire based on the 4% rule?
2.How much will they need to save each month to accomplish this?
3.What is the total amount they will need in other personal investments, to retire?
4.How much will they need to save each month to accomplish this?
Lucas and Sara identify 5% of their current monthly budget that they can dedicate to beginning their 401k and personal investments. They will use this as a starting place and look for more ways to increase this soon. Sara has spoken with her human resources department and has chosen a 401k option that has historically retuned 7% annually. Her company will match her contributions dollar for dollar up to 3%. This means that her employer will contribute a matching percentage of whatever percentage she decides to contribute to this retirement account, but the max they will contribute is 3% of her salary.
Use the bottom portion of the worksheet to answer the following questions about how they will invest this 5%:
Option 1: Sara contributes 1% of her gross income to her 401k and 4% to their other investments. Will this be enough to generate the savings Sara is planning for retirement? Do you think she needs to save more (or do they have more than they need?) in each account? How much, for each account?
Option 2: Sara contributes 4% of her gross income to her 401k and 1% to their other investments. Will this be enough to generate the savings she is planning for retirement. Do they need to save more (or do they have more than they need?) in each account? How much, for each account?
Option 3: Identify a percentage of Sara's gross income that you think would best help Lucas and Sara reach her retirement goal. What percentage do you recommend they contribute to Sara's 401k? What percentage for their other investments? Is the 5% total contribution enough or did you have to choose a higher percentage?
Now, after figuring out how Sara's 401K and Lucas and Sara's other investments will help their retirement hopes, answer the questions in the Lucas and Sara - Investment and Retirement Case Study assignment.
I also need the answers for the excel screenshoot.
AutoSave . Off) Retirement Income Projection Worksheets, Student Version Updated Winter 2021 - Template-1 - Excel Bianca Lucas BL X File Home Insert Page Layout Formulas Data Review View Help Share Comments Insert Calibri 12 ~ A" A" ab Wrap Text General Ex AY O Delete v Paste BIUvv VAV E Merge & Center $ ~ % 9 00 20 Conditional Format as Cell Sort & Find & Analyze Formatting Table ~ Styles v Format Filter ~ Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis D11 X V fx A B C D E F G H 1 Lucas and Sara - Investment and Retirement Case Study - Projection Worksheet 2 W How much annual income will be needed in retirement? A Current Age Retirement Age Present/Desired Annual Income (A) 00 Desired Retirement Annual Income - Based on Investment Counselor Recommendation (B) $0.00 9 10 11 Inflation Adjusted Income at Retirement 12 Years TO Retirement (number of years of working) 0 13 Inflation Rate (%) 14 Total Inflated Retirement 15 Future Value of Income at Inflated Rate (C) FV **This is the annual income you will need to meet you 16 17 18 How much needs to be saved to generate this income? 19 20 Sources of Retirement Income 4% Rule Template Case Study + 1AutoSave (. Off) Retirement Income Projection Worksheets, Student Version Updated Winter 2021 - Template-1 - Excel Bianca Lucas BL X File Home Insert Page Layout Formulas Data Review View Help Share Comments Insert Calibri 12 ~ A" A ab Wrap Text General Ex AY O Delete v Paste BIUV vvAv E Merge & Center $ ~ % 9 00 20 Conditional Format as Cell Sort & Find & Analyze Formatting Table ~ Styles v Format Filter ~ Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis D11 X A B C D E F G H 17 18 How much needs to be saved to generate this income? 19 20 Sources of Retirement Income 4% Rule 21 Social Security (Fixed based on current Investor adisor recommendations) 30% $0.00 $0.00 22 Company 401 K /Pension - Ignores tax implications $0.00 $0.00 23 Savings and Investments - Ignores tax implications $0.00 $0.00 24 Total Needs 30% $0.00 $0.00 25 26 Financial Needs at retirement based on the rule of thumb 4% Rule or 25X Rule 4.00% $ 27 Years of Retirement accomodated without impact of inflation 25 28 29 30 401k/Pension Savings and Investments 31 Estimated Rate of Return Estimated Rate of Return 32 Annual Savings Required (Payment) PMT Annual Savings Required (Payment) PMT 33 Monthly Savings Required Monthly PMT Monthly Savings Required Month 34 35 You could save the above amount yourself, or you can take advantage of your employer's benefits, which might include matching your 401K contribution. Whatever 36 amount your employer may contribute to your 401k is the portion that you don't have to contribute yourself. 37 38 39 How Lucas and Sara might save up this money for Sara's retirement amount Cara's arace income. co on Current Savings Vorcuc Amount Dequired Template Case Study +AutoSave (. Off) Retirement Income Projection Worksheets, Student Version Updated Winter 2021 - Template-1 - Excel Bianca Lucas BL X File Home Insert Page Layout Formulas Data Review View Help Share Comments Insert Calibri 12 ~ A" A" ab Wrap Text General Ex AY Delete v Paste $ ~ % 9 00 20 Conditional Format as Cell Sort & Find & Analyze E Merge & Center Formatting Table " Styles v Format Filter ~ Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis D11 X V fx A B C D E F G H 38 39 How Lucas and Sara might save up this money for Sara's retirement amount 40 Sara's gross income: $0.00 Current Savings Versus Amount Required 41 Option 1 % of Gross APR $ Amount per month Option 1 42 Sara's 401k Contribution #VALUE Monthly Estimate 401K #VALUE! 43 Company's Contribution #VALUE Monthly Req. 401K $0.00 44 Total Monthly 401k Contribution #VALUE! 0.00% #VALUE! Shortfall/Surplus #VALUE! 45 Monthly Est. Savings #VALUE! 46 Monthly Req. Savings $0.00 47 Shortfall/Surplus #VALUE! Monthly 48 Savings and Investments 0.00% #VALUE! Surplus/Shortfall #VALUE! 49 50 Option 2 % of Gross APR $ Amount per month Option 2 51 Sara's 401k Contribution #VALUE! Monthly Estimate 401K #VALUE! 52 Company's Contribution #VALUE! Monthly Req. 401K $0.00 53 Total Monthly 401k Contribution #VALUE! 0.00% #VALUE Shortfall/Surplus #VALUE! 54 Monthly Est. Savings #VALUE! 55 Monthly Req. Savings $0.00 56 Shortfall/Surplus #VALUE! Monthly 57 Savings and Investments 0.00% #VALUE! Surplus/Shortfall #VALUE! 58 59 Option 3 % of Gross APR $ Amount per month Option 3 60 Sara's 401k Contribution #VALUE! Monthly Estimate 401K #VALUE! 61 Company's Contribution #VALUE! Monthly Req. 401K $0.00 62 Total Monthly 401k Contribution #VALUE! 0.00% #VALUE Shortfall/Surplus #VALUE! 63 Monthly Est. Savings #VALUE! 64 Monthly Req. Savings $0.00 65 Shortfall/Surplus #VALUE! Monthly Template Case Study +AutoSave (. Off) Retirement Income Projection Worksheets, Student Version Updated Winter 2021 - Template-1 - Excel Bianca Lucas BL X File Home Insert Page Layout Formulas Data Review View Help Share Comments Insert Calibri 12 ~ A" A" ab Wrap Text General Ex AY Delete v Paste BIUV vvAv E $ ~ % 9 00 20 Conditional Format as Cell Sort & Find & Analyze Merge & Center Formatting Table " Styles v Format Filter ~ Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis D11 X V fx A B C D E F G H 40 Sara's gross income: $0.00 Current Savings Versus Amount Required 41 Option 1 % of Gross APR $ Amount per month Option 42 Sara's 401k Contribution #VALUE Monthly Estimate 401K #VALUE! 43 Company's Contribution #VALUE! Monthly Req. 401K $0.00 44 Total Monthly 401k Contribution #VALUE! 0.00% #VALUE! Shortfall/Surplus #VALUE! 45 Monthly Est. Savings #VALUE! 46 Monthly Req. Savings $0.00 47 Shortfall/Surplus #VALUE! Monthly 48 Savings and Investments 0.00% #VALUE! Surplus/Shortfall #VALUE! 49 50 Option 2 % of Gross APR $ Amount per month Option 2 51 Sara's 401k Contribution #VALUE Monthly Estimate 401K #VALUE! 52 Company's Contribution #VALUE! Monthly Req. 401K $0.00 53 Total Monthly 401k Contribution #VALUE! 0.00% #VALUE! Shortfall/Surplus #VALUE! 54 Monthly Est. Savings #VALUE! 55 Monthly Req. Savings $0.00 56 Shortfall/Surplus #VALUE! Monthly 57 Savings and Investments 0.00% #VALUE! Surplus/Shortfall #VALUE! 58 59 Option 3 % of Gross APR $ Amount per month Option 3 60 Sara's 401k Contribution VALUE Monthly Estimate 401K #VALUE! 61 Company's Contribution #VALUE! Monthly Req. 401K $0.00 62 Total Monthly 401k Contribution #VALUE! 0.00% #VALUE! Shortfall/Surplus #VALUE! 63 Monthly Est. Savings #VALUE! 64 Monthly Req. Savings $0.00 65 Shortfall/Surplus #VALUE! Monthly 66 Savings and Investments 0.00% #VALUE! Surplus/Shortfall #VALUE! 67 Template Case Study +
