Purchase Solution

Calculating the Value of an Annuity

Not what you're looking for?

Ask Custom Question

Ling 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%.

Purchase this Solution

Solution Summary

This solution provides assistance calculating the value of an annuity.

Purchase this Solution


Free BrainMass Quizzes
MS Word 2010-Tricky Features

These questions are based on features of the previous word versions that were easy to figure out, but now seem more hidden to me.

Income Streams

In our ever changing world, developing secondary income streams is becoming more important. This quiz provides a brief overview of income sources.

Cost Concepts: Analyzing Costs in Managerial Accounting

This quiz gives students the opportunity to assess their knowledge of cost concepts used in managerial accounting such as opportunity costs, marginal costs, relevant costs and the benefits and relationships that derive from them.

Balance Sheet

The Fundamental Classified Balance Sheet. What to know to make it easy.

Business Processes

This quiz is intended to help business students better understand business processes, including those related to manufacturing and marketing. The questions focus on terms used to describe business processes and marketing activities.