BUSI 201 Project: Building a Retirement Plan with Excel Formulas

This project utilizes Microsoft Excel to create a retirement plan for an employee, focusing on calculating retirement savings and potential monthly withdrawals. The solution employs Excel functions like FV and PMT to determine the future value of retirement funds and the monthly income post-retirement. It examines the impact of varying interest rates on retirement outcomes and discusses how to modify formulas to accommodate different scenarios, such as changes in work duration or inheritance. Key assumptions, such as a constant annual interest rate and consistent salary, are outlined. The project emphasizes the importance of savings rates, early planning, and the role of Excel as a planning tool, referencing financial management principles and practices. It highlights the need to consider tax implications and retirement objectives when determining contribution amounts, and the role of Excel in helping employees objectively plan for retirement and meet their financial goals. This resource is available on Desklib, where students can find more solved assignments and past papers.

Question 3
a) The FV function has been used for the computation and the final answer is $273,819.09
which highlights the money available at the time of retirement.
b) The PMT function has been used for the computation and the monthly amount available after
retirement would amount to $1,469.89.
c) The changes in average annual interest rate tend to have profound impact on the amount
available at the time of retirement along with the monthly payment derived after retirement.
This can be illustrated using the following screenshots which highlight the impact of changes
in average annual interest rate.
The above screenshots clearly highlight the fact that a marginal change in the average annual
return has significant impact.
d) The given formulas that have been used in the attached excel can be suitably altered in order
to reflect the various changes. For instance. If the person intends to work for a longer period
of time, then suitable changes in the value of nper needs to be made in the FV function.
Similarly, if the person expects to live more or less than 30 years, suitable changes can be
made in the PMT formula with the value of the time period. Modifications can also be made

to accommodate any inheritance since in the PMT function, FV can be fed which in the
present computation has been assumed as zero. However, this can be suitable adjusted based
on preferences of the given employee (Damodaran, 2015).
e) One of the assumptions pertains to the average annual rate of 5% which has been taken to be
constant throughout the computation period. Also, the contribution of salary has been pegged
at 5% for the whole duration leading to retirement. Besides, it has been assumed that the
retirement would be taken after 30 years and also the given employee would continue to need
the retirement payment for 30 years. Additionally, the salary of the employee has been
assumed as constant for the whole period leading to the retirement.
f) One of the key takeaways from the exercise is the importance of the average annual savings
rate which makes the choice of asset class very critical. Additionally, it makes sense to start
planning for retirement at the earliest as it can potentially lead to a higher value of the
retirement fund which would lead to higher retirement monthly income. In relation to the
contribution amount, besides tax considerations, the retirement objectives need to be
considered. For instance, one key aspect to consider is the amount of monthly income that
would be required so that a suitable monthly contribution can be made (Brealey, Myers &
Allen, 2014). Additionally, for retirement planning a crucial role is played by excel which
acts as an enabling tool to determine if the future retirement objectives can be met or not
under different scenarios. This allows the employee to objectively plan for the retirement
from the salary and also ensure that the various financial objectives are met. As a result,
excel is widely used by various companies which tend to offer retirement plans for
employees based on their specific needs and preferences (Brigham & Houston, 2014).

References
Brealey, R. A., Myers, S. C., & Allen, F. (2014) Principles of corporate finance (2nd ed.). New
York: McGraw-Hill Inc.
Brigham, E. F. & Houston, J. F., (2014) .Fundamentals of Financial Management (14th ed.).
Boston: Cengage Learning.
Damodaran, A. (2015). Applied corporate finance: A user’s manual (3rd ed.). New York: Wiley,
John & Sons.