annuity calculate

UNM CS-150L Lab 8 Fall 2008 Lab 8: Excel: Calculating Future Value of an Annuity and Retirement Income, using the IF statement. Due: Sunday November 9 at midnight. The focus of this lab is the calculations associated with a retirement annuity. The term annuity is used in finance theory to refer to any terminating stream of fixed payments over a specified period of time usually in connection with the valuation of the stream of payments, taking into account time value of money concepts such as interest rate and future value. Annuities are classified by payment dates. The payments (deposits) may be made weekly, monthly, quarterly, yearly, or at any other interval of time. An ordinary annuity is an annuity whose payments are made at the end of each period (e.g. a month, a year). In this lab you will create spreadsheets outline retirement annuities. These annuities have two phases: the accrual phase, in which payments are made into the annuity account and the pension phase, in which payments are made out of the annuity. You will be making an Excel workbook with three worksheets titled “Research” “FV”, “Accrual table”, and “Variations”. The answers and calculations listed below must be included in your workbook. It is up to you to organize, format, color, and highlight the information in a professional looking, and easy to read manner. Excel has the built-in function, FV(rate, nper, pmt), for calculating the future value of an annuity. The Excel function performs the following calculation: FVannuity ⎛ (1 + rate) nper − 1 ⎞ ⎟ = pmt × ⎜ ⎟ ⎜ rate ⎠ ⎝ Where rate is the periodic interest rate (APR divided by the number of periods per year), nper is the total number of periods of the annuity, and pmt is the amount contributed to the annuity each period. One serious limitation to this equation, and to the Excel FV function, is that it assumes that the contribution amount remains constant. Most 401(k) programs have contributions that increase as the person’s salary increases. In this lab we will use the Excel FV function and overcome its limitations. 1) Research Worksheet - [20 points Total]: Before you can plan your retirement, you need to have some idea of the number of years you plan to work before retirement, the salary you expect to make, how much you can put into retirement savings and what rate return you can expect on the retirement investment. In this section, you will do some research to determine reasonable guesses for these values. a) [4 points]: Choose a person for whom you will be calculating the retirement income. This can be you, a different person or a fictional person. Somewhere on the research worksheet, include the person’s name, and the year when he or she plans to retire. It will be assumed that the person starts working in 2009. State this somewhere on the research sheet. Also, enter a calculation of the total number of years that the person will be working. -1- UNM CS-150L Lab 8 Fall 2008 b) [5 points]: Choose a general career of the person for whom you will be calculating the annuity (i.e. hotel and restaurant management, corporate sales, nurse, education...). After you have selected a career, do a bit of research to find a reasonable 2009 starting salary in that field in a particular geographical area. There are many possible sources for this. You may draw from your own hiring experience (if you have any). You may ask professionals that you know, look in the news papers, try a job search on http://www.jobs.com/ or http://www.monster.com/ or any of many other sources. Include a sentence or two in the spreadsheet describing your source. For example, if you use http://www.monster.com/, then state so. Also state why you believe the annual salary you found is representative of a particular type of starting position in a particular location. This should just be a sentence or two, not an essay. c) [5 points]: Follow up the research in part b, with research to determine a reasonable salary that the person might expect to have at the end of his or her career. Give this number in 2008 dollars (do not include inflation). For example, if the chosen career was education in the Albuquerque area, then in part b, you might have used an Albuquerque city publication of starting salaries for teachers in the Albuquerque Public Schools system. Then, for this section, use the same publication to find the salary of a teacher with 25 years experience or the salary of a principle or some other administration position that the person might be expecting to strive for by retirement. Note: end-of-career salary is much harder to find than starting salaries because senior salaries are not so openly advertised. Do not spend too much time researching this. Get the best estimate you can in a reasonable amount of time. d) [6 points]: Money placed in an annuity does not just sit there. It is invested. It can be placed in very secure investments such as insured, bank certificates of deposits (CD), or less secure investments such as stocks. You need to determine two rates that you will be using in calculations: The average annual rate of return on the annuity account during the accrual phase and the average annual return rate during the pension phase. These two numbers could be the same, but typically, people choose a more risky investment with a higher rate of return during the accrual phase and a more conservative investment during the pension phase. For example, during the accrual phase a person might have 20% bonds and 80% stocks. During the pension phase you are trying to preserve wealth, not generate it; therefore, during the pension phase, the same person might switch to 80% municipal bonds and 20% stocks. Usually, an employer will work with a single retirement agency and that agency will have many different plans to choose from: money market accounts, bond funds, growth stock funds, and others. Research some of the long term history of these accounts and determine what you think is a reasonable expected annual return on your retirement plan. Enter these numbers, any sources you used, and a short description of your reasoning for using those numbers and sources. -2- UNM CS-150L Lab 8 2) FV Worksheet - [46 points Total]: Fall 2008 The worksheet produced in this section could be set up like the example shown in figure 1. a) [1 point]: Scenarios: During the accrual phase, create a column for each of three scenarios: contributions being made at the end of each year, at the end of each quarter and at the end of each month. b) [1 point]: Annual Salary: This is a cell reference to the starting salary you researched on the first worksheet. On this worksheet, we will be using the Excel FV function. This function is easy to use, but assumes that every payment is the same. Therefore, on this worksheet you will not make use of the terminal salary you researched. This value is entered once and used by all three scenarios. c) [1 point]: Pre-tax Employee Contribution: This is a percentage that you plan to take out of your salary to place into the annuity account. You chose the percentage and enter it as a constant. The IRS allows individuals to put pre-tax or after tax dollars into retirement accounts. When pre-tax dollars are used, the tax is deferred and paid when the money is taken out during the pension phase. Generally, this is a good idea for a number of reasons. First, most people are in a lower tax bracket during their retirement. Second, the account can be earning interest and compound interest on the deferred taxes for all the years of the accrual phase. The number you chose is entered in one cell and used by all three scenarios. Figure 1: Example layout for the main table part of worksheet 1. Note that this example does not show the heading in the first few rows of the worksheet. Also note that during the accrual phase, an unrealistically large average annual return rate was chosen. -3- UNM CS-150L Lab 8 Fall 2008 d) [1 point]: After-tax Employee Contribution: This is a percentage of after tax dollars that you plan to put into the annuity. Generally this number is zero %. You may enter zero or some other percentage. The number you chose is entered in one cell and used by all three scenarios. e) [2 points]: Employer Contribution: As part of a benefits package, many employers make contributions to your retirement annuity. Often they will match whatever pre-tax percentage you contribute up to some limit. In this part of the lab, it will be assumed that the employer matches 100% of the pre-tax employee contribution up to a maximum matching of 5%. The value in this cell must be calculated by an equation. In the example shown in figure 1, the equation would be =MIN(5%,C6). The Excel MIN() function returns the minimum of the arguments. In this case, it returns the lesser of 5% or the constant in C6. f) [4 points]: Total Dollar Amount of Annual Contributions: This combines the three different contribution categories: pre-tax employee contributions, after-tax employee contributions, and employer contributions. This is a dollar amount. To calculate this amount each of the three percentages need to be multiplied by the annual salary and added together. It is likely that your after-tax contribution will be 0%. This zero amount still needs to be included in the equation so that equation is correct, and if the after-tax contribution assumption was changed, then the value in this cell would automatically update. This value is calculated in one cell and used by all three scenarios. g) [1 point]: Annual Investment Return Rate: This must be a cell reference to the accrual phase expected annual return rate you researched on the first worksheet. h) [2 points]: Years Contributing: This must be an equation calculated from the start work date and retirement date you entered on the first worksheet. This value is calculated in one cell and used by all three scenarios. i) [2 points]: Contributions Per Year: This is the total number of contributions make each year. This value is entered as a constant and is different for each of the three scenarios. j) [2 points]: Periodic Interest Rate: Calculate this periodic rate from the annual investment return rate and the number of periods per year. Enter an equation in the first scenario and fill the equation across through the other columns. In order to receive full credit for this part, the equation entered in the first scenario must correctly fill across. k) [2 points]: Amount Contributed each Period: This is the amount contributed annually divided by the number of periods per year. In order to receive full credit for this part, the equation entered in the first scenario must correctly fill across. l) [2 points]: Periods Contributing: This is the total number of periods that occur during all years of contributing. In order to receive full credit for this part, the equation entered in the first scenario must correctly fill across. m) [2 points]: Total Contributions: This is the total amount of money contributed over all periods and all years of the accrual phase. It does not include any interest. In order to receive full credit for this part, the equation entered in the first scenario must correctly fill across. n) [4 points]: Future Value FV function: This is the total dollar value of the annuity at the end of the accrual phase. It includes all of the contributions, the interest and the compounded interest. Use the Excel FV(rate, nper, pmt) function to calculate this amount. In order to receive full credit for this part, the equation entered in the first scenario must correctly fill across. -4- UNM CS-150L Lab 8 Fall 2008 o) [4 points]: Future Value FV Equation: This is the same value as Future Value FV function, however, rather than using the Excel FV(rate, nper, pmt) function, you must use the equation given on the first page of this lab. If done correctly it will give the same result as the Excel FV function. In order to receive full credit for this part, the equation entered in the first scenario must correctly fill across. Notice that in the example, a future value is shown of over 88 million dollars. This is correct given all the assumptions shown. It is unreality large because an average annual return rate of 20% was used. This is not a realistic average long term return rate. Undoubtedly, your research will reveal with a more realistic number which will be reflected in a more realistic future value. Even so, the future value will be amazingly large. This is the power of compound interest. Also notice that all three scenarios have exactly the same value for total contributions (money you put in), but significantly different future values (money you can take out). p) [1 point]: Nest Egg: This is the first row of the pension phase. The pension phase will only include one scenario: The one in which monthly payments were made into the annuity during the accrual phase and monthly payments are taken out during the pension phase. The value of the Next Egg must be a cell reference to the last line of the accrual phase. This is the amount of money in your retirement annuity at the time of your retirement. All of the remaining parts of this section are part of the pension phase. q) [1 point]: Yearly Interest Rate: This is pension phase interest rate you researched in the first worksheet. The value must be a cell reference to that value. r) [1 point]: Years of Retirement: This is the number of years you plan to live after you retire. This is important because the Excel payment function is going to calculate the amount of money you can take out of your account each month so that at the projected end of your live your annuity balance is zero. Therefore, it is wise to choose this number conservatively with the assumption of a long life. s) [4 points]: Monthly Pension: Use the Excel PMT(rate, nper, pv) function covered in lab 7 to determine the amount you will withdraw from your pension each month. Warning: The rate and number of payments in this equation are, of course, the periodic rate and total number of payments, not just the number of years. Unlike in the accrual phase, in this phase, you are not required to place these calculations separate cells. You may choose to make such cells or you may choose to perform the calculation within the PMT function. For example, rather than using a single cell reference for nper, you could use a cell reference to the number of years and multiply that number times the number of payments per year. The most common error is to use the annual rate and the number of years in the PMT equation, to get the correct annual payment, and then to divide that correct annual payment by 12. This gives an incorrect monthly payment because it does not account for all the effects of compound interest. When a monthly payment is taken out the annuity in January, the annuity balance decreases. During the remaining eleven months of the year, the part taken out in January does not accrue interest. This is why finding the correct yearly payment for a single lump sum withdraw ant the end of the year and dividing that by 12 gives too large a monthly payment. t) [6 points]: Monthly Social Security Benefit: Every paycheck you receive throughout your life has a part taken out for Social Security. This is the part of your retirement plan administrated by the federal government. When you reach a specified age, you begin collecting monthly social security benefit checks. The amount you collect depends on the amount you paid in, the years you worked, your age and whether you are still working. Unlike the monthly pension calculation above, your social security benefit does not end after -5- UNM CS-150L Lab 8 Fall 2008 some predetermined number of years. It is an insurance: you loose it if you die young, but the payments never end if you live long. Calculate your expected social security benefit by use a Web browser to access the Social Security Administration’s benefit calculator at http://www.ssa.gov/planners/benefitcalculators.htm. This page offers three calculators of increasing levels of detail. For this lab, you need to use calculator number 2: “Online Calculator”. Fill out the information in the calculator according to the assumptions you made on the first worksheet. For example, do not necessarily enter your own age, but the age of the person for whom you designed worksheet 1. Be sure to select the option for “Today’s Dollars”. When you have finished filling out the online form, click the “Calculate Benefit” button. Copy the resulting monthly benefit amount into your worksheet and label the value. u) [2 points]: Monthly Retirement Income: This is the sum of your monthly pension and your monthly social security benefit. Figure 2: Screen capture of Social Security Online Benefit Calculator. Note: Social Security Administration offers many benefit calculators. Some require you to enter your social security number and display private information. Do not use these calculators for this lab. Do not include any private information in this lab. None of the three calculators shown above are linked to your Social Security earnings record. They use earnings amounts that you enter and can be fictional. -6- UNM CS-150L Lab 8 Fall 2008 3) Accrual table Worksheet - [18 points Total]: This worksheet will use a table rather than the FV function to calculate the accrual phase future value of the same annuity used in part 2. Aside form the instructional value of seeing the calculation develop in a table; a table has the advantage of being much more versatile then the Excel FV function. This versatility will be made use of on the forth worksheet. The table will be constructed to calculate contributions made monthly. The final future value calculated by the table should be exactly the same as what is calculated by the FV function with monthly contributions in part 2. Create a table with the following eight columns: a) [2 point]: Date: Start at Jan 1, 1009, then use the EOMONTH() function to fill down to obtain the first of every month of the accrual phase. This will be a lot of rows. b) [2 point]: Monthly Salary: Use a cell reference to the annual starting salary you researched in part 1. Obtain the monthly salary by dividing this reference by 12. Fill this same down throughout every month of the accrual phase. c) [2 point]: Pre-tax Employee Contribution (use percentage from part 2) d) [2 point]: Employer Contribution (use percentage from part 2) e) [2 point]: After-tax Employee Contribution (use percentage from part 2) f) [2 point]: Total Dollar Amount of Annual Contribution (calculate) g) [2 point]: Annual Investment Return (use percentage from part 2) h) [4 points]: Value (calculate – not by FV function. Use the equation Vthis _ month = Vlast _ monnth + (Vlast _ month × r ) + pmt where Vthis_month is the value at the end of the current period, Vlast_montht is the value at the end of the previous period, r is the periodic interest rate, pmt is the congtribution amount made this period. -7- UNM CS-150L Lab 8 4) Variations Worksheet - [16 points Total]: Fall 2008 A serious limitation to Excel’s FV function is that the contribution rate must remain constant. However, in the business world it is very common for the contribution to be a percentage of an employee’s salary. As a person advances in his or her career, the person commands a higher salary which should significantly out pace inflation. In part 1, you researched the current starting salary for a person entering a particular field in a particular geographic location. This value was used in parts 2 and 3. Now, we will use the starting salary together with the estimated salary the same a person would have attained near retirement. a) [2 points] Copy all of the rows and columns from worksheet 3 into worksheet 4. b) [2 points] Insert a few rows at the top of the worksheet. In these rows add the following labels and values: • • • • • Starting Annual Salary (cell reference from sheet 1) Annual Salary at Retirement (cell reference from sheet 1) Starting Monthly Salary (calculation) Monthly Salary at Retirement (calculation) Average Monthly Salary Raise (a constant you discover through trial and error. Start with a constant of 0.1%) c) [6 points] Throughout worksheet 3, the Monthly Salary is a constant. On this worksheet it will increase a bit every month. This, of course, is not the usual way salary is increased. Usually a salary remains constant all year and changes by a larger jump on your job anniversary or on the change of the calendar year. In the next lab, we will do this more accurate modeling using an IF() function. For now, the first month salary should be a cell reference to the starting monthly salary in part 4b. Calculate the monthly salary in the second month by adding the pervious month’s salary to the pervious month’s salary times the Average Monthly Salary Raise. Fill this equation down through every month of the accrual phase. Then examine the value for the monthly salary at the end of the accrual phase. If this value is less than the Monthly Salary at Retirement calculated in part 4b, then the Average Monthly Salary Raise is too small. Try increasing it by a bit. When you do this, all of the monthly salary values for every month except the first will increase. Continue adjusting the value of the Average Monthly Salary Raise until the monthly salary in the last month of the accrual phase is approximately the same as Monthly Salary at Retirement calculated in part 4b. d) [2 points] The first five years of this person’s career are spent living at home or in an apartment with other young interns. Wisely, this person decides to increase the employee contribution percentage during these first five years. Since the employee is making a small salary during this time the dollar amount of the total contribution does not increase by much; however, thanks the exponential growth of compounded interest, a few hundred dollars extra in first few years makes a huge difference in the final “nest egg”. e) [4 points] How big is your “nest egg” now? How much bigger is it then when you were paid a starting salary all your life and did not make some extra contributions in the first 5 years? Answer these questions with a cell label and an equation that calculates the value. Submit one document into WebCT with the attached file: CS150-Lab8-YourFirstNameYourLastName.xls -8-