In Lab 1, we used Excel's FV function to find the future value of a single investment. This function can also be used to find the future value of an ordinary annuity:
=FV(rate, nper, pmt, [pv], [type])
where
The type argument indicates whether the annuity is an ordinary annuity with payments made at the end of the period (type = 0) or an annuity due with payments made at the beginning of the period (type = 1). We will only be working with ordinary annuities so type is 0.
1. Download and open the file Lab02.xlsx. Remind me how. Enter your name in cell B1 of the Sinking Fund sheet.
2. A married couple decides to save $50 at the end of each month in an account earning 3% compounded monthly. Your goal is to determine how much they will have in their account after four years and how much interest they will have earned. Begin by entering the initial data in the previous two sentences on the worksheet.
3. Enter appropriate formulas for the periodic rate and the number of periods.
4. Calculate the future value and the total interest using formulas.
a. In cell B9, use the FV function to find the future value. Try to do this on your own. You should get a value of $2,546.56. Help
b. In cell B10, enter an Excel formula to calculate the total interest earned. Remember, the total interest is the difference between what you get from the investment and what you put into the investment. You should get a result of $146.56. Help
c. Select cells B9:B10. Make the background light gray, change the font to bold, and outline the cells.
5. Following the steps below, prepare a savings account schedule that shows the status of the account after each payment. This is done pretty much the same way you did the table in the previous lab. The biggest difference is that there is an additional column (the Payment column) which contains the payment deposited into the account at the end of each period.
a. Highlight the two cells below the heading "End of Period" and drag the fill handle down until the last number in the column is the number of periods (the value in cell B8). Release the mouse button.
b. Recall that fictitious period zero is included to make graphs look nicer. The new balance at the end of period zero is zero dollars because no money has yet been deposited into the account. The first deposit will be made at the end of the first period.
c. The initial balance for each period starting with period one is the new balance at the end of the previous period. For period one, enter an appropriate formula for the initial balance.
d. The interest earned each period is always the initial balance times the periodic rate and should always be rounded to the nearest cent. Enter the formula for the interest for period one. Be careful about relative and absolute cell references.
e. Starting with period one, the payment for each period is given in cell B3. In the row corresponding to period one, enter the appropriate Excel formula. Be careful about relative and absolute cell references.
f. The new balance is the sum of the initial balance, the periodic interest, and the periodic payment. Enter the corresponding Excel formula in the new balance column.
g. Copy the formulas you just entered down one row and verify that you get the correct results. If you do not, then correct the formulas in the row for period one and repeat this step until you do.
h. Using the fill handle, copy the formulas for period one down to the bottom of the table. Was the final balance in the account $2,546.58? If not, correct your work until you get the correct value.
i. How do you explain that fact the future value given by Excel's FV function and the new balance in the last row of the table are not the same?
j. Format the financial values in the body of the table (every column but the first) using the comma format.
6. Create a line chart showing how the account balance grows during the term of the account. (Remember that charts illustrating how the balance changes always use the new balance column.) Use appropriate titles. Format the y-axis as currency to zero decimal places. Remember that there should be no legend and that the y-axis should be on the tick marks. If you have forgotten how to create a chart, you'll need to go back to either of the first two labs and review the instructions.
You can check your work by looking at my workbook: Lab02Done.xlsx
Do the lab exercise on the worksheet named "Lab Exercise".
A man wants to save $15,000 in five years in order to buy a new car. His savings account pays 4.7% interest compounded quarterly. How much should he invest at the end of each quarter in order to reach his goal? How much interest will he earn? Create a table showing how the account balance grows over time and generate a line chart on the lab exercise worksheet itself illustrating this growth.
In this exercise, you need to find the periodic payment that will generate the indicated future value. Here are some tips:
1. Use Excel's PMT(rate, nper, pv, [fv], [type]) function. Its arguments are similar to those for the FV function. When the future value is know (as it is in this case), enter a value of zero for the present value.
2. Like the TVM Solver, the periodic payment and the future value will have opposite signs. Since you want the periodic payment to show up as a positive value on the worksheet, you'll need to negate the future value argument in the PMT function just as you negated the pmt argument in the FV function above.
3. You will need to use the ROUND function to round the value of the periodic payment to two decimal places:
ROUND(PMT(rate, nper, 0, -fv), 2)
where rate, nper, and fv are replaced by appropriate cell references.
Rounding to two decimal places is necessary because you will be using the value of the periodic payment in the payment column of the table that shows the growth of the account balance and all of the values in this table must be rounded to the nearest penny.