# Question: Ling opened an annuity to save for a down payment

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.

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.

**View Solution:**## Answer to relevant Questions

Analyze how the U.S. Constitution implements separation of powers and checks and balances. Briefly explain why the constitutional framers based the new government on these ideas. Evaluate how separation of powers and checks ...Compare the advantages of competitive bidding for a general contractor with negotiated cost plus fee. What is the argument for using a maximum cost with sharing of overruns or savings between developer and general ...Time Value Of Money 38. To buy a new house you take out a 25 year mortgage for $300,000. What will your monthly interest rate payments be if the interest rate on your mortgage is 8 percent?Rate (i) =Number of periods (n) ...Determine the appropriate balance between unfettered free speech and the duties of public employees. Provide specific examples to support your response.The Robinson Company has the following current assets and current liabilities for these two years: If sales in 2010 were $1.2 million, sales in 2011 were $1.3 million, and cost of goods sold was 70 percent of sales, how ...Post your question