James,opened an annuity to save for a down payment on a home. The annuity was created with an initial deposit of $3,500 (end of year). At the end of each of the following ten years, a payment of $4000 is made into the annuity. The interest rate is 2% compounded annually.
a) Compute the balance at the end of 10 years by tabulating the deposits, interest and balance for each year in a spreadsheet. That is, create a spreadsheet with three columns containing 1) the annual deposit, 2) interest earned for the preceding year, and 3) the balance for each year. Use a year 0 for the initial deposit which would be in the balance column. Use equations which refer to cell labels. The balance in the last row should be the future value of this annuity at the end of ten years.
b) Use the Excel FV function to calculate the future value in ten years for this situation. You should get the same answer as the tabulation of part a.
c) Use an Excel function (e.g. PMT) to determine the payment required each year (instead of $4000) to achieve $50,000 in ten years and the rate of 2% annually.
d) Use an Excel function (e.g. NPER) to determine how many periods would be needed, using the $3,500 up front and the annual payment of $4000 at 2% annually, to achieve $50,000.
e) Use an Excel function (e.g. RATE) to determine the interest rate you would need to achieve $50,000 in ten years with a annual payment of $4000 and $3,500 up front.
f) Use an Excel function (e.g. PV) to determine the upfront deposit that you would need to achieve $50,000 in ten years with an annual payment of $4000 and an annual interest rate of 2%.