Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Fred Jones plans to deposit $1000 at the end of each quarter into an account that pays 8% annually, compounded quarterly. What will be the

Fred Jones plans to deposit $1000 at the end of each quarter into an account that pays 8% annually, compounded quarterly. What will be the value of the account at the end of the 4th quarter (4th payment)?

The answer to the question above is given by the future value of an ordinary annuity. A template to compute this value is constructed in steps (a)-(c). Be sure to distinguish between labels, input data, and the formulas that do the calculations.

a. Start with a clean worksheet and enter the following data. Steps (b) and (c) complete the template. Note that each of a series of deposits is called a "payment" (denoted 'R' in cell A4).

A

B

C

D

E

1

Problem #1

2

Annuity Problem

3

4

PMT

$1,000

Period

Ending Balance

5

r

0.08

1

=B4

6

m

4

2

=E5+E5*$B$10+$B$4

7

t

1

3

8

mt

=B6*B7

4

9

10

r/m

=B5/B6

11

FV

b. The formula in cell E5 copies the deposit at the end of Quarter #1 from cell B4; the formula in E6 adds to the balance (E5) the interest on the balance together with another payment. Copy the formula in E6 to cells E7..E8 in order to determine the balance at the end of the remaining quarters.

c. In cell B11 we want to compute the total amount in cell E8 directly. Enter the formula =FV(B10, B8, -B4) in cell B11 and check that it gives the same value found in cell E8. NOTE the negative sign in the third argument. This is because Excel treats payments (out) as a negative cash flow. Try the formula without the negative sign and observe the difference. Algebra practice: In cell, F8 enter the formula for the Future Value of an Ordinary Annuity

FV = PMT [(1+r/m)mt-1]/[r/m] (using appropriate cells). The result should be the same as found in cell E8. Why do you think the built-in formula FV is provided?

Discussion: In a textbox explain what information is given by the different approaches used to compute the values in cells B11, E8, and F8. SAVE the worksheet.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Principles Of Financial Management

Authors: Haim Levy, Marshall Sarnat

1st Edition

0137097751, 978-0137097753

More Books

Students also viewed these Finance questions