Finance Project

profilenirmak
FIN_RetirementProject.xlsx
Home>Business & Finance homework help>Accounting homework help>Finance Project

Sheet1

Finance
Project: The Discovery to Reaching Retirement
Name:
Semester:
Course:
Where a cell is highlighted in yellow, a relationship has been set up -- that is, an equation to compute the required value. For instance, you can use the PV function that is built into the spreadsheet to compute present value where it is required, or you can input the appropriate equation to complete the computation. Only enter values (numbers) where cells are highlighted in white and where instructed. The computations should be based on information provided in other cells--that is, you should refer to the locations of the cells that contain the appropriate information when creating the computational relationship. Instructions as to what needs to be computed in the yellow-highlighted cells are given in the cells that are highlighted in blue. If you want the results of some of your computations to appear as positive numbers, you will have to insert a negative sign prior to the function you use for the computation (i.e., insert a negaqtive sign immediately after the equal sign).
Section I: Salary Expectations
Years From Now or After Starting Your Career
1-10 11-20 21-30 31-40 41-50
Salary:
% Change: ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Enter the average annual salaries that you expect to earn during each 10-year period that is applicable to your career expectations. Then, create the relationship (equation) necessary to compute the percentage change in salary from one period to the next for each 10-year period. If you expect to retire in 20 years or sooner, use five-year increments for the salary estimates.
Section II: Life Expectancy
Current age:
Retirement age: Years to retirement: 0
Life expectancy age: Years of retirement: 0
Enter the requested age information in the blank cells. Your "life expectancy age" should be based on the results of the life-expectancy quizzes that you took.Set up the appropriate relationships (equations) to compute the values requested in the cells highlighted in yellow.
Section III: Risk Preferences
You must take the risk tolerance quizzes listed in the general instructions. Input the proportions of your retirement funds that coincides with the risk tolerance results.
Percent
(Weight)
Cash & short-tem bonds: (E.g. Savings account. Little risk involved when regulations are in place to guarantee balances when a bank closes.)
Bonds: (E.g. Investor acts as creditor. Value is not based on corporate profit but rather predictable cash flows.)
Stocks--domestic: (E.g. Ownership holdings in corporations such as Google, Apple, or McDonald's. Higher risk involved as value is based on corporate profits.)
Stocks--foreign: (E.g. Ownership holdings in foreign corporations such as Toyota or Alibaba. Additional risk involved dealing with currency exhange or political ramifications.)
Total: 0.00% If the total is not equal to 100.0%, change the weights until it is. The weights should be based on the results of the risk tolerance quizzes that you took.
Section IV: Retirement Goals
The expected returns for the following types of investments:
Percent Expected Rate Portfolio Rate
(Weight)
Cash: 0.00% 0.10% 0.00%
Bonds: 0.00% 6.20% 0.00%
Stocks--domestic: 0.00% 9.70% 0.00%
Stocks--foreign 0.00% 12.40% 0.00%
Total: 0.00% Weighted average 0.00%
Using the inputs from the previous section, the "weighted average" is computed using your unique risk preferences.
A. Retirement income
Average annual income during retirement--stated in today's (current) dollars:
Average annual inflation rate during your career: 2.10%
Years to retirement: 0
Average annual income at retirement--stated in inflation-adjusted dollars: $0
Enter your desired "annual" retirement income in K53 stated in today's monetary value. The value in cell K55 will be the same as the value shown in cell G22 in Section II. The values in cells K53-K55 must be used to compute the answer in cell K56. The cell highlighted in yellow designates the value of your desired retirement income in the future. For example, living off a $30,000 salary today would be the same standard of living as a $50,000 salary 24 years from now. (a.k.a Effect of Inflation)
B. Amount needed in your retirement fund when you retire
Average annual income during retirement--stated in inflation-adjusted dollars: $0
Weighted average expected rate of return on your retirement fund: 0.00%
Years of retirement (how long you expect to be retired): 0
Total amount neeeded at retirement to meet your retirement goals: $0
Using the inputs you provided in previous sections; the value in cell K64 determines the total amount to which your retirement fund must grow to during your working years. (a.k.a. Require Future Value prior to taking Retirement)
C. Value of current "retirement" investments at retirement (if you do not have an existing retirement fund, input $10,000 in cell K69 and complete the rest of this section)
Present (current) value of any retirement funds that you currently have:
Expected rate of return on your retirement fund in an average market: 0.00%
Years to retirement: 0
Total amount neeeded at retirement (in all retirement funds): $0
Value of current (existing) retirement funds at the time you retire: $0.00
Additional amount needed at retirement to meet your retirement needs: $0
For cell K69, enter the present day value for all retirement accounts. (Mute Funds, Plans in vest, 401k, 403b, IRAs, etc.) The relationships above are to determine how much any funds that you currently have invested for retirement will be worth at your retirement if these funds are invested at the average rate computed in cell H48.
D. Required annual contributions to your retirement fund
Amount above existing funds needed at retirement to meet your needs: $900,000
Use the value given in cell K80 to compute the value in K85; this amount will differ from the amount in cell K74 if that amount is less than $800,000:
Years to retirement: 0
Expected rate of return on your retirement fund in an average market: 0.00%
Annual contributions required to accumulate amount needed at retirement: ERROR:#NUM!
Set up the relationship required to determine the amount that must be contributed to your retirement fund each year to accumulate the total amount that is needed in the fund when you retire. You can use the PMT function built into the spreadsheet for this computation. The values in cells K79, K81, and K84 will automatically be entered by the spreadsheet from the relationships you created earlier. These values must be used to compute the answer in cell K85.
Above-average market conditions:
Expected rate of return on your retirement fund in an above-average market:
Annual contributions required to accumulate amount needed at retirement: ERROR:#NUM!
Projections and planning are merely and plainly not actual figures. Nor are projections deemed as guaranteed; therefore, it would be prudent to be aware that a change in economy is likely. To account for economies where the environment "exceed expectations", enter a rate of return in cell K89 that is 3.1% greater than the average expected return computed in cell H48. The value in cell K90 will correspond to the adjustment in your annual contributions.
Below-average market conditions
Expected rate of return on your retirement fund in a below-average market:
Annual contributions required to accumulate amount needed at retirement: ERROR:#NUM!
Similar to the procedure above, it is wise to acknowledge any liklihood of "below than average" expectations in the market. In cell K96, enter a rate of return that is 2.2% less than the average expected return computed at the beginning of this section. The values in cells K79-K81 and K96 must now be used to compute the answer in cell K97.
E. Re-compute the contributions that are needed to accumulate the amount of funds required at retirement assuming that you wait to begin your retirement fund so that the number of payments you make (years) will be only 50 percent (1/2) of the years reported in Section II, which is used for the earlier computations in this section.
In this section, you must perform the same computation as you did in Part D for the normal, or average, market, except the contributions to the retirement fund will be for a different number of years. This section is to show you the consequence of "waiting".
Amount above existing funds needed at retirement to meet your needs: $900,000
Use the value given in cell K106 to compute the value in K111; this amount will differ from the amount in cell K74 if that amount is less than $800,000:
Expected rate of return on your retirement fund in an average market: 0.00%
"Adjusted" years to retirement (50 percent of the original number of years):
Annual contributions required to accumulate amount needed at retirement: ERROR:#NUM!
Here you will enter "half" the years until your retirement to the cell of "Adjusted years". This relationship set is to show you the negative effect of most people in society choosing to wait later to start contribting. Cell K111 will be the total amount that is needed in the fund each year to reach the same goal at retirement.
Above-average market conditions:
Expected rate of return on your retirement fund in an above-average market:
Annual contributions required to accumulate amount needed at retirement: ERROR:#NUM!
In cell K115, enter a rate of return that is 3.1 percent greater than the average expected return given in cell K108. This cell should mirror cell K89.
Below-average market conditions
Expected rate of return on your retirement fund in a below-average market:
Annual contributions required to accumulate amount needed at retirement: ERROR:#NUM!
In cell K122, enter a rate of return that is 2.2 percent less than the average expected return given in cell K108. This cell should mirror the figure in cell K96. The value in cell K123 is to determine the annual contributions in a consequence scenario in which a worse-than-expected economy exists.