This is a excel project, please help me. Thank you! You can input the number wha
ID: 2551751 • Letter: T
Question
This is a excel project, please help me. Thank you! You can input the number whatever you want.
Defined contribution pension plans have increased in popularity. Indeed, many companies have terminated their defined benefit plans and almost all new pension plans are defined contributions plans. These plans are easy to administer and shift the investment risk to the employee.
There are many diverse employment opportunities in insurance, and there are wage rates that go along with various occupations. The Feelings Mutual Insurance Company has employees at many pay levels. The employee benefits office likes to demonstrate the importance of Feelings Mutual’s noncontributory (employer pays all), defined contribution, pension plan by projecting a newly-hired employee’s future pension balance. Of course Feelings Mutual requires the employee to sign a form noting that the projection is not a promise of a future balance and that actual performance will, most certainly, vary from the projection.
Your spreadsheet should have reference cells for these items: age, starting salary, employer contribution rate, wage increase rate, and pension fund rate of return.
The spreadsheet should have columns (not rows) showing an employee’s age, year of participation in the plan, annual salary (starting salary in year 1, starting salary indexed by the wage increase rate in the second year, etc), annual pension contribution (which increases each year as it’s a percent of salary and salary increases each year), and the annual ending pension balance (the previous year’s ending balance times one plus the assumed pension ROR plus the “new” contribution for the year). Assume that pension contributions are made at the end of each year – so the balance at the end of first year (Year 1) will be the pension contribution for the first year. The formulas used should incorporate reference cell addresses. Provide projections for 40 years (use the copy command... do not retype the formula 39 times!).
Feelings Mutual currently contributes 5 percent of an employee’s salary to the plan each year, but the contribution rate may change in the future, so a reference cell for the contribution rate is needed. Feelings Mutual offers several investment options (S&P 500 fund, balanced fund, growth fund, etc.) that have provided different rates of return over time, so a pension ROR cell is needed.
Your instructor will test to see if your spreadsheet meets the requirements described above by inputting data for a hypothetical new-hire (e.g., a 25 year-old employee starting at $w per year, assuming x% raises, with y% of salary contributed by the employer, and a z% ROR on pension assets). (10 points)
Explanation / Answer
Statement Showing Annual Employer Contribution for Pension & Rate of Return on Pension Contribution Years Age of the Employee (Years) Year of Participation in th ePlan Annual Salary (in $) Annual Salary Increase Rate Annual Pension Employer Contribution Rate Annual Pension Employer Contribution (in $) Annual ending pension balance (in $) Pension Fund Rate of Return A B C D = Annual Salary for Prior Year + Increase E F G =D*F H =Annual Pension Contribution + Prior Year Contribution I = (Current Contribution - Prior Year Contribution)/Prior Year Contribution 1 25 2018 12,000 10% 5% 600 600 0% 2 26 2019 13,200 10% 5% 660 1,260 110% 3 27 2020 14,520 10% 5% 726 1,986 58% 4 28 2021 15,972 10% 5% 799 2,785 40% 5 29 2022 17,569 10% 5% 878 3,663 32% 6 30 2023 19,326 10% 5% 966 4,629 26% 7 31 2024 21,259 10% 5% 1,063 5,692 23% 8 32 2025 23,385 10% 5% 1,169 6,862 21% 9 33 2026 25,723 10% 5% 1,286 8,148 19% 10 34 2027 28,295 10% 5% 1,415 9,562 17% 11 35 2028 31,125 10% 5% 1,556 11,119 16% 12 36 2029 34,237 10% 5% 1,712 12,831 15% 13 37 2030 37,661 10% 5% 1,883 14,714 15% 14 38 2031 41,427 10% 5% 2,071 16,785 14% 15 39 2032 45,570 10% 5% 2,278 19,063 14% 16 40 2033 50,127 10% 5% 2,506 21,570 13% 17 41 2034 55,140 10% 5% 2,757 24,327 13% 18 42 2035 60,654 10% 5% 3,033 27,360 12% 19 43 2036 66,719 10% 5% 3,336 30,695 12% 20 44 2037 73,391 10% 5% 3,670 34,365 12% 21 45 2038 80,730 10% 5% 4,036 38,401 12% 22 46 2039 88,803 10% 5% 4,440 42,842 12% 23 47 2040 97,683 10% 5% 4,884 47,726 11% 24 48 2041 107,452 10% 5% 5,373 53,098 11% 25 49 2042 118,197 10% 5% 5,910 59,008 11% 26 50 2043 130,016 10% 5% 6,501 65,509 11% 27 51 2044 143,018 10% 5% 7,151 72,660 11% 28 52 2045 157,320 10% 5% 7,866 80,526 11% 29 53 2046 173,052 10% 5% 8,653 89,179 11% 30 54 2047 190,357 10% 5% 9,518 98,696 11% 31 55 2048 209,393 10% 5% 10,470 109,166 11% 32 56 2049 230,332 10% 5% 11,517 120,683 11% 33 57 2050 253,365 10% 5% 12,668 133,351 10% 34 58 2051 278,702 10% 5% 13,935 147,286 10% 35 59 2052 306,572 10% 5% 15,329 162,615 10% 36 60 2053 337,229 10% 5% 16,861 179,476 10% 37 61 2054 370,952 10% 5% 18,548 198,024 10% 38 62 2055 408,047 10% 5% 20,402 218,426 10% 39 63 2056 448,852 10% 5% 22,443 240,869 10% 40 64 2057 493,737 10% 5% 24,687 265,556 10%
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.