Ling opened an annuity to save for a down payment on a home. The annuity was created with an initial deposit of $1,000 (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.5% compounded annually. Submit the following in a spreadsheet.
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 the Excel PMT function to determine the payment required each year (instead of $4000) to achieve $50,000 in ten years (same initial deposit, interest rate and years).
d) Use the Excel NPER function to determine how many years it will take Ling to achieve $50,000 but using a rate of 2.0% annually, using the initial deposit and payment as initially stated. Show two decimal places
e) Use the Excel RATE function to determine the interest rate Ling would need to achieve $50,000 using the same number of periods, initial deposit and payment as initially stated.
f) Use the Excel PV function to determine the upfront deposit that Ling would need to achieve $50,000 using the same number of periods, interest rate and payment as initially stated.