BUSI 201 Intermediate Business Computer Applications-The Using of PMT
- Review the work completed by each group member for the individual submission portion of the Microsoft Excel Project. Together, determine which project will represent the group in your final version, making any adjustments or corrections as needed.
We’ll use Mitchell’s spreadsheet.
- Unhide the Retirementworksheet in the Payroll file. Choose 1 employee from the Payroll worksheet to create a retirement plan for. This worksheet will help you calculate how much is being saved for retirement, and how that will affect the amount that can be withdrawn through the retirement years.
Let’s use employee Oliver Pittman.
- Begin by determining the yearly/monthly gross income that this employee can expect given his/her current pay and hours.
- Many companies offer to match retirement contributions when their employees participate in a 401(k) plan. In this scenario, the employer will contribute up to 5% of the employee’s gross income if the employee also contributes 5%. (E.g. employees contributing 3% of their income would receive an additional 3% from the company and employees contributing 7% would receive an additional 5% from the company, etc.)
Mitchell, will you complete steps 2a and 2b?
- Use a formula to add a contribution amount of your choice as well as any company match in cells B14:B15. Use an IF function to automatically determine the company match based on your chosen percentage. Be sure to use the assumption cells B22:B23 in your formulas. Test various contribution amounts to ensure that your formula works in all cases.
I can work on this step.
- Roth IRAs can be used for low to upper middle class workers to fund retirement with post-tax dollars (i.e. net pay). Consider adding additional funds to a Roth IRA in cell B16. Total the contributions to your retirement in cell B17 and calculate the monthly contributions (D12:D17). The monthly contribution will now appear in cell B2.
Dan, will you work on this one (2d)?
- Calculate the percentage (%) of income that is being contributed to retirement in B20.
Jennifer, would you work on this step (2e) as well as 3a (below)
- Now that you have calculated the amount to save for retirement, you will calculate how much this amount will yield in 30 years and what the monthly income will be in retirement.
- In cell B5, use the appropriate financial function to calculate how much money will be in the account when the employee is ready to retire. Remember to use a negative value for the PMT argument. There is no money currently in the account and payments will be applied at the end of every
- Once you have saved for retirement, how much can you draw from the retirement account every month over the 30 years that you expect to be retired? Assume that at the end of 30 years, the account balance will be zero and the payments to you will be made at the beginning of every month. (Hint: Use the PMT function to calculate the payments to yourself. Use the ending balance in your retirement account as the present value. Add a negative sign before the present value so the result appears as a positive)
Answer:
The percentage (%) of income that is being contributed to retirement in B20 is found to be 15%. With the help of Gross income and total contribution to the retirement, we calculated it.
Using PMT function, the amount of money in the account would be $11951.68 according to the monthly contribution of $574.08, years until retirement 30and average annual return 4%. The total amount of money would be $218062.48. We assumed that there is no money currently in the account and payments would be applied at the end of every month.
If the average annual return is changed from 1%,2%,3% 4% ,5%, 6%, 7% and 8% respectively, then amount of money are $6888.96, $9227.75, $10544.09, $11951.68, $13444.10, $16654.70 and $18357.86. The annual return is found to be $11951.68. This would move me toward over-saving rather than over-estimating and being caught short retirement.
As per PMT function, we calculated the monthly payment value at retirement in cell B5. It is found to be = $ 218620.48.
Now, we used NPER function to get the years of retirement plan. B20 is already given in the B20 cell as 13%. We calculated the total contribution at retirement as $497.54 instead of $574.08. Now, applying the new monthly amount of contribution with respect to the same amount of yearly interest rate and average annual return, we found the years in retirement 13.4 years.
According to the B20, I am going to work in bank for 13.4 years. Therefore, it could be said that I want to retire early. If I wanted to have money left at my death to leave behind as an inheritance, the amount is = $ 218620.48.
The PMT formula changed to NPER formula.
Considering the assumptions that has been in the computation of the retirement is the use of the PMT functions. Additionally, monthly rate has been assumed in deriving the contributions that has to be made in the retirement plan. On the other hand, the contribution treatment of 15% has been considered to derive gross income generated from the retirement plan.
On analysing the project an important consideration that can be introduced in this context is that I have learned that a monthly contribution of $574.08 for a span of 30 years at the interest rate would result in yearly contribution of $6,888.96. Additionally, I have learned that on my contributions of 8% I can gain a yearly return of 3,674.11 and on the other hand from my company match I can attain a yearly contribution of 2.296.32. As understood from the contribution margin I have understood that based on the monthly basis my contribution value stands $306.18for each month while the company match based on the monthly contribution stands 191.36.
I have learned that the total amount of the monthly retirement contribution stands 547.08 based on the company match of 5% while my contribution based on my retirement has stood 8%. As evident from the analysis I have understood that there are certain excel functions that make the computations easy for me to compute my retirement plan based on the amount of contribution that would be made by me. Additionally, I have also learned regarding the use of the PMT functions that have contributed to my learning knowledge and have contributed significantly to my practical skills.
The retirement plan has provided me significant understanding regarding the future. As evident from computation retirement plans is considered as the investment plans that lets me to allocate my portion of savings to get accumulated over the period of time and given that I am provided with the steady source of income after my retirement. I have learned from the retirement computation that if though a person has a better amount of savings, a retirement plans is considered as a vital source of income for an individual. I have realised that savings get exhausted in no span of time and I have also understood that they are at times used in emergencies to meet the need of urgency. Hence, computing and investing through careful means in the retirement plan serves me with secure amount of cash flow for meeting my basic regular needs following the retirement.
I have understood that on making a constant investment in return the amount grows manifold times because of the compounding impact that makes lot of difference to my final savings. A correct computation of the retirement plan would help in planning for retirement in the phased manner. On practically breaking down the returns based on the yearly and monthly basis pension plan have been considered as the better mode of investment plans which helps in ensuring that I have secured life following retirement. Additionally, the use of excel tools have been help to me in understanding the appropriate calculation tools that are needed in the computation of the retirement plan. Conclusively, having a right pension plan enables me to plan for my retirement in a phased manner and simultaneously acts as the saviour in the future period.
Bibliography
Barth, M.E., Beaver, W.H. and Landsman, W.R., 2001. The relevance of the value relevance literature for financial accounting standard setting: another view. Journal of accounting and economics, 31(1), pp.77-104.
Chewning Jr, E.G. and McKie, A., 2002. Accounting for asset retirement obligations. The CPA Journal, 72(5), p.56.
Franzoni, F. and Marin, J.M., 2006. Pension plan funding and stock market efficiency. the Journal of Finance, 61(2), pp.921-956.
Kotlikoff, L.J., 1995. Generational accounting. NBER Reporter, p.8.
Scott, W.R., 1997. Financial accounting theory (Vol. 2, No. 0, p. 0). Upper Saddle River, NJ: Prentice hall.
Buy BUSI 201 Intermediate Business Computer Applications-The Using of PMT Answers Online
Talk to our expert to get the help with BUSI 201 Intermediate Business Computer Applications-The Using of PMT Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
