Finance Excel Question Problem 2

Please See Attachments – thank you!

>Ch

0 4

3 5

Build a Model

cept for charts and answers that must be written, only Excel formulas that use cell references or functions will be accepted for credit.

of $1,000 invested to earn

annually 5 years from now. Answer this question by using a math formula and also by using the Excel function wizard.

, and

for 0, 1, 2, 3, 4, and 5 years. Then create a graph with years on the horizontal axis and FV on the vertical axis to display your results.

7 Excel tutorial (Tab 4 – Data Tables Graphs). We used the data table procedure. Note that the Row Input Cell is D

and the Column Input Cell is D10, and we set Cell B

equal to Cell E

. Then, we selected (highlighted) the range B32:E3

, then clicked Data, Table, and filled in the menu items to complete the table.

s (D10):

Rate (D9)

0% 5% 20%

$0.00 $0.00

1 $0.00 $0.00 $0.00
2 $0.00 $0.00 $0.00
3 $0.00 $0.00 $0.00
4 $0.00 $0.00 $0.00
5 $0.00 $0.00 $0.00

. Then click the chart wizard. Then follow the menu. It is easy to make a chart, but a lot of detailed steps are involved to format it so that it’s “pretty.” Pretty charts are generally not necessary to get the picture, though. Note that as the last item in the chart menu you are asked if you want to put the chart on the worksheet or on a separate tab. This is a matter of taste. We put the chart below on the spreadsheet so we could see how changes in the data lead to changes in the graph.

%), then to .4, then to .5, etc., to see how the table and the chart changes.

Inputs:

10%

N = 5
Formula:

because there are no periodic payments. Also, set the FV with a negative sign so that the PV will appear as a positive number.

Inputs:

I/YR = ?

N = 5

per year. How long would it take for the population to double?

Inputs: PV =

FV =

N = ?

. Then find the FV of that same annuity.

Inputs:

N = 5

15%

PV =

FV =

x =

Inputs:

1000 1000

I/YR = 10% 5%
N = 5 10
Formula:

Wizard (FV):

Orig. Inputs New Inputs

Inputs: FV = 1000 1000

I/YR = 10% 5%
N = 5 10

Formula: PV = FV/(1+I)^N =
Wizard (PV):

Year

1 100
2 200
3

PV =
Year Payment x

= FV

1 100

2 200

6.00

3 400

PV =

,000. The interest rate is 8.5%, and you must amortize the loan over 10 years with equal end-of-year payments. Set up an amortization schedule that shows the annual payments and the amount of each payment that repays the principal and the amount that constitutes interest expense to the borrower and interest income to the lender.

Year

Pmt Interest

1 $0.00 $0.00 $0.00 $0.00
2 $0.00 $0.00 $0.00 $0.00 $0.00
3 $0.00 $0.00 $0.00 $0.00 $0.00
4 $0.00 $0.00 $0.00 $0.00 $0.00
5 $0.00 $0.00 $0.00 $0.00 $0.00
6 $0.00 $0.00 $0.00 $0.00 $0.00
7 $0.00 $0.00 $0.00 $0.00 $0.00
8 $0.00 $0.00 $0.00 $0.00 $0.00
9 $0.00 $0.00 $0.00 $0.00 $0.00
10 $0.00 $0.00 $0.00 $0.00 $0.00

4 and D185, and change the interest rate and the term to maturity to see how the payments would change.

payments in all, with

333%.

Beg. Amt. Pmt Interest Principal End. Bal.

$0.00 $0.00 $0.00 $0.00 $0.00

15 $0.00 $0.00 $0.00 $0.00 $0.00
16 $0.00 $0.00 $0.00 $0.00 $0.00
17 $0.00 $0.00 $0.00 $0.00 $0.00
18 $0.00 $0.00 $0.00 $0.00 $0.00

$0.00 $0.00 $0.00 $0.00 $0.00

20 $0.00 $0.00 $0.00 $0.00 $0.00
21 $0.00 $0.00 $0.00 $0.00 $0.00
22 $0.00 $0.00 $0.00 $0.00 $0.00

$0.00 $0.00 $0.00 $0.00 $0.00

30 $0.00 $0.00 $0.00 $0.00 $0.00

$0.00 $0.00 $0.00 $0.00 $0.00

32 $0.00 $0.00 $0.00 $0.00 $0.00
33 $0.00 $0.00 $0.00 $0.00 $0.00

$0.00 $0.00 $0.00 $0.00 $0.00

35 $0.00 $0.00 $0.00 $0.00 $0.00

$0.00 $0.00 $0.00 $0.00 $0.00

38 $0.00 $0.00 $0.00 $0.00 $0.00

$0.00 $0.00 $0.00 $0.00 $0.00

40 $0.00 $0.00 $0.00 $0.00 $0.00

$0.00 $0.00 $0.00 $0.00 $0.00

50 $0.00 $0.00 $0.00 $0.00 $0.00

$0.00 $0.00 $0.00 $0.00 $0.00

64 $0.00 $0.00 $0.00 $0.00 $0.00

$0.00 $0.00 $0.00 $0.00 $0.00

70 $0.00 $0.00 $0.00 $0.00 $0.00

$0.00 $0.00 $0.00 $0.00 $0.00

83 $0.00 $0.00 $0.00 $0.00 $0.00

$0.00 $0.00 $0.00 $0.00 $0.00

85 $0.00 $0.00 $0.00 $0.00 $0.00

$0.00 $0.00 $0.00 $0.00 $0.00

100 $0.00 $0.00 $0.00 $0.00 $0.00

$0.00 $0.00 $0.00 $0.00 $0.00

120 $0.00 $0.00 $0.00 $0.00 $0.00

											Spring	1	20	13
		7	22	12
Chapter 4. Ch 04 P	35
E			x
Numeric answers in cells will not be accepted.
a. Find the	FV	1	0%	To get the dialog box, click on fx, then Financial, then FV, then OK.
						Inputs:	PV		=	10
I/YR =			10%
					N =
			Formula:	FV = PV(1+I)^	N =
	Wizard (FV):
Note: When you use the wizard and fill in the menu items, the result is the formula you see on the formula line if you click on cell E12. Put the pointer on E12 and then click the function wizard (fx) to see the completed menu. Also, it is generally easiest to fill in the wizard menus by clicking on one of the menu slots to activate the cursor and then clicking on the cell where the item is given. Then, hit the tab key to move down to the next menu slot to continue filling out the dialog box.
Experiment by changing the input values to see how quickly the output values change.
b. Now create a table that shows the FV at 0%,			5%	20%
Begin by typing in the row and column labels as shown below. We could fill in the table by inserting formulas in all the cells, but a better way is to use an Excel data table as described in the		200		9	32	11		8
			Year			Interest
$ – 0
															$0.00
To create the graph, first select the range C	33	38
Note that the inputs to the data table, hence to the graph, are now in the row and column heads. Change the 20% in Cell E32 to .3 (or	30
c. Find the PV of $1,000 due in 5 years if the discount rate is 10% per year. Again, work the problem with a formula and also by using the function wizard.
	FV =					100
			I/YR =
	PV = FV/(1+I)^N =
	Wizard (PV):
Note: In the wizard’s menu, use zero for		Pmt
d. A security has a cost of $1,000 and will return $2,000 after 5 years. What rate of return does the
security provide		?
		PV =	-1000
		FV =	2000
Wizard (Rate):
Note: Use zero for Pmt since there are no periodic payments. Note that the PV is given a negative sign because it is an outflow (cost to buy the security). Also, note that you must scroll down the menu to complete the inputs.
e. Suppose California’s population is 30 million people, and its population is expected to grow by	2%
-30
		6
I/YR = growth rate
Wizard (NPER):	= Years to double.
f. Find the PV of an ordinary annuity that pays $1,000 at the end of each of the next 5 years if the interest rate is		15
PMT =	$ 1,000
I/YR =
PV: Use function wizard (PV)
FV: Use function wizard (FV)
g. How would the PV and FV of the above annuity change if it were an annuity due rather than an
ordinary annuity?
For the PV, each payment would be received one period sooner, hence would be discounted back one less year. This would make the PV larger. We can find the PV of the annuity due by finding the PV of an ordinary annuity and then multiplying it by (1 + I).
PV annuity due =
Exactly the same adjustment is made to find the FV of the annuity due.
FV annuity due =
h. What would the FV and the PV for parts a and c be if the interest rate were 10% with
semiannual compounding rather than 10% with annual compounding?
Part a. FV with semiannual compounding:		Orig. Inputs		New Inputs
		PV =
FV = PV(1+I)^N =
Part c. PV with semiannual compounding:
i. Find the PV and FV of an investment that makes the following end-of-year payments. The
interest rate is	8%
	Payment
		40
Rate =
To find the PV, use the NPV function:
Excel does not have a function for the sum of the future values for a set of uneven payments. Therefore, we must find this FV by some other method. Probably the easiest procedure is to simply compound each payment, then sum them, as is done below. Note that since the payments are received at the end of each year, the first payment is compounded for 2 years, the second for 1 year, and the third for 0 years.
(1 + I )^(N-t)
1.	17	1	16	64
1.08		21
1.00	400.00
Sum =
An alternative procedure for finding the FV would be to find the PV of the series using the NPV
function, then compound that amount, as is done below:
FV of PV =
j. Suppose you bought a house and took out a mortgage for $	50
Original amount of mortgage:	50000
Term of mortgage:
Interest rate:	0.0	85
Annual payment (use PMT function):
	Beg. Amt.		Principal		End. Bal.
(1) Create a graph that shows how the payments are divided between interest and
principal repayment over time.
Go back to cells D	18
(2) Suppose the loan called for 10 years of monthly payments,	120
the same original amount and the same nominal interest rate. What would the
amortization schedule show now?
Now we would have a 12 × 10 = 120-payment loan at a monthly rate of .085/12 = 0.00	70	83
The monthly payment would be:
Month
14
19
23
24
25
26
27
28
29
31
34
36
37
39
41
42
43
44
45
46
47
48
49
51
52
53
54
55
56
57
58
59
60
61
62
63
65
66
67
68
69
71
72
73
74
75
76
77
78
79
80
81
82
84
86
87
88
89
90
91
92
93
94
95
96
97
98
99
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119

Sheet2

7/22/12

>Chapter 4

1/1

Chapter

4. The Time Value of Money

The worksheet shown below performs most of the calculations required for Chapter 4, and it was used to create many of the chapter’s tables and figures. We pasted in a few dialog boxes for specific Excel functions and features; they are shown off to the right of where they were used. However, in general we encourage students to become familiar with our Excel Tutorial and to refer to it if they encounter something they don’t understand.

t is also useful to learn how Excel models can be used to create tables and graphs that can then be copied into Word documents, which is the way we prepared the text manuscript for submission to the publisher. That procedure is used often in business (and in business courses) to prepare reports.

Although answers to the Self

–

Test questions within the chapter are generally quite easy and can be worked with a calculator, we also solved them with Excel as a check and also to provide some information on the solutions for students who might have questions. The tabs at the lower part of this screen take you to the solutions for self-tests in the various sections of the chapter. Even students who are not familiar with Excel should still be able to see the solution setup and then work the problem with a calculator.

While we did not create the model specifically for use in lectures, we like to use it in our lectures if we are in a classroom where a projector is attached to a computer. We scroll through the model and lecture on points and questions as they come up. This is most useful if students have some familiarity with Excel, but that is not really necessary because everything the model does can also be done with a financial calculator.

FUTURE VALUES (Section

4.2

)

A dollar in hand today is worth more than a dollar to be received in the future because, if you had it now, you could invest it, earn interest, and end up with more than one dollar in the future. The process of going to future values (

s) from present values (

s) is called compounding. To illustrate, refer to our

-year time line and assume that you plan to deposit

in a bank that pays a guaranteed

% interest each year. How much would you have at the end of

Year

3? See Columns o the right for a picture of the filled in dialog box for the FV function. Figure 4-1. Alternative Procedures for Calculating Future Values

PUTS:

Investment

CF0

= PV = –

100

.00 Interest rate

= I = 5.0

0% Notice that in the following dialog box we entered the variables as cell No. of periods = N =

references. We could have used the numbers themselves, but using cell references makes the model more useful, as we demonstrate later. Setup of the problem as a Periods

: 0

1 2 3

You can see our Excel Tutorial for a thorough discussion of how to Time Line |

| | |

enter and use formulas. Cash Flow:

–

0 0

FV = ? =FV(C

,0,C3

) 1.

Step-by-Step

Multiply

$100

$105.00 $

25 $1

7 6 2. Formula: FV

N =

PV(1+I)N FV3 = $100(1.05)3

$115.

76 3 5

–

0.00 $0 3. Financial

Calculator:

I/YR

PMT

$115.76
4. Excel Spreadsheet: FV Function:

FVN =

=FV(I,N,0,PV) Fixed inputs:

FVN =

=FV(0.05,3,0,

-100

) =

$115.76
Cell references

FVN =

=FV(C40,C41,0,C

) =

$115.76
In the Excel formula, the terms are entered in this sequence: interest, periods, 0 to indicate no periodic cash flows, and then the PV. The data can be entered as fixed numbers or, better yet, as cell references. The Compounding Process: A Graphic View Figure 4-2 (just below) shows how a $1 investment grows over time at different interest rates. The curves were created by solving for FV at different values for N and I. The graph shows, simultaneously, the effects of time and interest rates. The data table used to create this figure is shown to the right. For instruction on data tables and graphing, refer to our Excel Tutorial, Tab 4. Data Used to Create Text Figure 4-2. Future Value of $1 Periods (N) Interest Rate

(I) 115.7

5 –

0% 5%

10% 20% 0 $

100.00 $100.00

$100.00 $100.00 $100.00
1

0.00

$100.00 $105.00

10.0

0 $

0.00 2

.00

$100.00

$110.25 $1

.00 $

4.00 3

.20

$100.00 $115.76

3.10 $

80 4

$40.

$100.00

$121.

55 $1

.41 $207.

36 5

$100.00

63 $

1.05 $

83 6

.21

$100.00

.01 $177.16 $

$20.

$100.00

$140.

4.8

8.32

$16.

$100.00

$21

4.3

98 9

$13.42

$100.00

$155.13 $

79 $515.98 10

$10.

$100.00

$162.

89 $25

37 $

9.17 PRESENT VALUES (Section 4.3) Mathematically, the present value is the opposite of the future value. Instead of compounding a present value forward to find the FV, you discount the FV back to find the PV. Thus, if you know the PV, you can compound to find the FV, while if you know the FV, you can discount to find the PV. To illustrate, refer to the time line on Row

below and assume that $115.76 is due in 3 years. If a bank pays a guaranteed 5% interest rate each year, how much must you deposit now to have $115.76 in 3 years? The amount of the required deposit is the PV of $115.76 due in 3 years when the discount rate is 5%, and it can be found by any one of four methods. Figure 4-3. Alternative Procedures for Calculating Present Values INPUTS: Future payment = CFN = FV =

$115.76
Interest rate = I = 5.00% No. of periods = N =

3
Problem as a Time Line

Periods: 0 1 2 3

=PV(C111,C112,0,C110) | | | |
Cash Flow Time Line: PV =

$115.76
1. Step-by-Step: $100.00 $105.00 $110.25 $115.76
2. Formula: PVN = FV/(1+I)N

PV =

$115.76/(1.05)3

= $100.00
3 5 $0 $115.76
3. Financial Calculator: N I/YR PV PMT FV
-$100

.00 4. Excel Spreadsheet: PV Function:

PV =

=PV(I,N,0,FV) Fixed inputs: PV =

=PV(0.05,3,0,115.76) =

-$100.00
Cell references:

PV =

=PV(C111,C112,0,C110) =

-$100.00
In the Excel formula, the terms are entered in this sequence: interest, periods, 0 to indicate no periodic cash flows, and then the FV. The data can be entered as fixed numbers or, better yet, as cell references. The Discounting Process: A Graphic View Figure 4-4 shows how the present value of $1 due in the future declines as either the interest rate or the time until receipt increases. The Data Table to the right provides the data used to draw the figure. At 0%, the PV of $1 always remains at $1, but at higher rates the value at the end of N years is lower the higher the rate, and at a given rate, the value declines the larger the value of N. Data for Figure 4-4. Present Value of $1

Periods (N) Interest Rate (I)

86 38

0% 5% 10% 20%
0

$1.00

$1.0000 $1.0000 $1.0000
5 $1.0000

$0.7835 $0.6209 $0.4019 10 $1.0000

$0.6139 $0.3

5 $0.1615 15 $1.0000

$0.

10 $0.23

94 $0.06

49 20 $1.0000

$0.37

69 $0.1486 $0.0261 25 $1.0000

$0.29

53 $0.0

3 $0.0105 30

$1.0000

$0.2

4 $0.0

3 $0.00

42 35 $1.0000

$0.

13 $0.03

56 $0.0017 40 $1.0000

$0.1420 $0.0

1 $0.0007 45

$1.0000

$0.1113 $0.0137 $0.0003 50

$1.0000

$0.08

72 $0.0085 $0.0001

FINDING THE INTEREST RATE (Section

4.4

)

Previously, we solved the basic equation to find FV and PV. However, we could just as easily solve for I or N. For example, suppose we know that a given bond has a cost of $100 and that it will return

$150

after 10 years. Thus, we know PV, FV, and N, and we want to find the rate of return we would earn if we bought the bond. =RATE(C1

,0,C171,C172)

INPUTS:

Present value (PV)

-$100.00
Future value (FV) $150.00 No. of years (N)

10
OUTPUT: Interest rate (I) = RATE(N,0,PV,FV)

Interest rate (I)

4.14

FINDING THE NUMBER OF YEARS (Section

4.5

)

Sometimes we need to know how long it will take to accumulate a given sum of money, given our beginning funds and the rate we will earn on those funds. For example, suppose we believe that we could retire comfortably if we had $1 million, and we want to find how long it will take us to reach that goal, assuming that we now have

$500

,000 invested at 4.5%. =NPER(C196,0,C194,C1

)

INPUTS:

Present value (PV)

-$500,000 Future value (FV)

$1,000

,000 Interest rate (I)

4.50%

OUTPUT:

No. of years (N)

=NPER(I,0,PV,FV) No. of years (N)

15.7473

FUTURE VALUE OF AN ORDINARY ANNUITY (Section

4.7

)

An ordinary annuity has regular, periodic payments that occur at the end of each period. Methods for solving the future value of an ordinary annuity are shown below. Figure 4-5. Summary: Future Value of an Ordinary Annuity

INPUTS:

Payment

amount =

PMT =

-$100.00
Interest rate = I =

5.00%
No. of periods = N =

3
1. Step-by-Step: 0 1 2 3

| | | |

-$100 -$100 -$100
$100.00
Multiply each payment by

$105.00
(1+I)N-t and sum these FVs to

$110.25
find FVAN: $315.25 2. Formula:
FVAN =

= $315.25
3. Financial Calculator: 3 5 $0 -$100.00
N I/YR PV PMT FV
$315.25
4. Excel Spreadsheet: FV Function:

FVAN = =FV(I,N,PMT,PV) Fixed inputs: FVAN =

=FV(0.05,3,-100,0) =

$315.25
Cell references:

FVAN =

=FV(C216,C217,C215,0) =

$315.25
FUTURE VALUE OF AN ANNUITY DUE (Section 4.8) An annuity due also has regular, periodic payments, but unlike an ordinary annuity, the payments occur at the beginning of each period. Summary: Future Value of an Annuity Due (This table is not in text)

INPUTS:
Payment amount = PMT = -$100.00
Interest rate = I = 5.00%

Number of periods = N =

3
Periods: 0 1 2 3

| | | |

Cash Flow Time Line: -$100 -$100 -$100
1. Step-By-Step:

$105.00
Multiply each payment by $110.25
(1+I)N-t and sum these FVs to $115.76
find FVAN:

1.01 2. Formula:
FVAN(due) =

= $331.01
BEG MODE

3 5 0 -100
3. Financial Calculator: N I PV PMT FV
331.01
4. Excel Spreadsheet: FV Function: FVAN =

=FV(I,N,PMT,PV,Type) Fixed inputs: FVAN =

=FV(0.05,3,-100,0,1) =

331.01
Cell references: FVAN =

=FV(C249,C250,C248,0,1) =

331.01
In the Excel formula, the 1 at the end of the formula indicates that cash flows occur at the beginning of each period. A 0 or nothing would indicate end-of-period payments.

PRESENT VALUE OF AN ORDINARY ANNUITY (Section

4.9

)

The present value of an ordinary annuity is the sum of the PVs of the individual cash flows. Methods for solving the present value of an ordinary annuity are shown below. Figure 4-6. Summary: Present Value of an Ordinary Annuity

INPUTS:
Payment amount = PMT = -$100.00
Interest rate = I = 5.00%
Number of periods = N = 3

Periods: 0 1 2 3
| | | |

Cash Flow Time Line: -$100 -$100 -$100
1. Step-By-Step:

$95.24 Divide each payment by $

.70 (1+I)t and sum these PVs to $86.38 find PVAN: $

272.32

2. Formula:

PVAN =

$272.32 3 5 -100 0

3. Financial Calculator: N I PV PMT FV

272.32
4. Excel Spreadsheet: PV Function:

PVAN = =PV(I,N,PMT,FV) Fixed inputs: PVAN =

=PV(0.05,3,-100,0) =

$272.32
Cell references: PVAN =

=PV(C

5,C286,C2

,0) =

$272.32
PRESENT VALUE OF AN ANNUITY DUE (this table is not in text) The difference between the present value of an ordinary annuity and an annuity due is that payments are received earlier in an annuity due. Summary: Present Value of an Annuity Due (Not in text)

Payment amount = PMT = -$100.00
Interest rate = I = 5.00%
Number of periods = N = 3

Periods: 0 1 2 3
| | | |
Cash Flow Time Line: -$100 -$100 -$100

Step-By-Step Approach.

$100.00
Divide each payment by $95.24
(1+I)t and sum these PVs to $90.70
find PVAN:

285.94 Formula Approach: PVAN = = $285.94
BEG MODE 3 5 -100 0
Calculator Approach:

N I PV PMT FV
285.94
Excel Function Approach:

PV Function: PVAN =

=PV(I,N,PMT,FV,Type) Fixed inputs: PVAN =

=PV(0.05,3,-100,0,1) =

285.94
Cell references: PVAN =

=PV(C319,C320,C318,0,1) =

285.94

FINDING ANNUITY PAYMENTS, PERIODS, AND INTEREST RATES (Section

4.10

)

Fundamentally, this section is no different than previous TVM exercises. When solving for PMT, N, or I, you must be given values for the other variables, and then you solve the problem. FINDING PMT Suppose we need to accumulate

$10,000

and have it available 5 years from now. Suppose further that we can earn a return of

on our savings, which are cu

ently zero. How much must we save in each of the 5 years, assuming (a) end-of-year payments and (b) beginning-of-year payments? No. of years (N) 5
Interest rate (I) 6%
Present value (PV) $0
Future value (FV) $10,000
a. END MODE b.

BEGIN MODE Payment (PMT) -$1,773.96

Payment (PMT)

-$1,

3.55 =PMT(I,N,PV,FV) =PMT(I,N,PV,FV,Type=1) FINDING N Suppose you decide to make end-of-year deposits, but you can only save $1,200 per year. Again assume that you would earn 6%. How long would it take you to reach your $10,000 goal? BEGIN MODE
Interest rate (I) 6% 6%
Present value (PV) $0 $0
Payment (PMT)

-$1,200

-$1,200
Future value (FV) $10,000 $10,000
No. of years (N)

6.96 6.63 =NPER(I,PMT,PV,FV,0) =NPER(I,PMT,PV,FV,1) FINDING I Now suppose you can only save $1,200 annually, but you still want to have the $10,000 in 5 years. What rate of return would enable you to achieve your goal?

BEGIN MODE

No. of years (N) 5 5

Present value (PV) $0 $0
Payment (PMT) -$1,200 -$1,200
Future value (FV) $10,000 $10,000

Interest rate (I)

25.7

8% 17.

% =RATE(N,PMT,PV,FV,0) =RATE(N,PMT,PV,FV,1)

PERPETUITIES (Section

4.11

)

Perpetuities are securities that promise to make payments forever. The present value of a perpetuity can be found with a simple formula: Value = PMT / I . Note that we cannot calculate the future value of a perpetuity because, since payments go on forever, this value would be infinitely large and thus meaningless. Consider a British consol that pays a $25 annual payment. If interest rates are currently

5.2%

, what is the value of the consol? Payment (PMT) $25
Interest rate (I) 5.2%
Value (PV): $25 / 0.0

= $480.77 The value of an annuity is the sum of the PVs of each of its payments, PVt = PMTt/(1+I)t. Note that the value of each additional payment (or year) adds less value than the previous payment because it is discounted more heavily. This helps explain why perpetuities’ values are finite, even though the number of payments is infinite. As N and thus t increases without limit, the PV of the distant payments approaches zero. To see this better, consider the figure below (which is not in the text). The data used to construct the graph are shown to the right. We look at 100 payments, showing the PV of each payment and the total value of the

annuity at

each value of N. The value of the payments as a 100-year annuity is $

. The value of a perpetuity would be 100/0.1 = $1,000. Thus, the value of the payments from year 101 to infinity is only $0.07. Annuity PMT

$100.00
Interest Rate 10%
Value of a 100 year, 10%, $100 annuity: $999.93

$0.00

Value of the PV of this

annuity at
Pmt

#, N

Payment Pmt

this value of N 0 100.00

100.0000

0.00
1 100.00

90.90

91 90.91 2 100.00

6 173.55 3 100.00

75.1315 248.69 4 100.00

.3013 316.99 5 100.00

62.0921 379.08 6 100.00

56.4474 43

5.53 7 100.00

51.31

58 486.84 8 100.00

46.

07 533.49 9 100.00

42.4098 575.90 10 100.00

38.5543 614.46 11 100.00

35.0494 649.51 12 100.00

31.8631 6

.37 13 100.00

28.9

4 710.34 14 100.00

26.3331 736.67 15 100.00

23.9392 760.61 16 100.00

21.7629 782.37 17 100.00

19.7845 802.16 18 100.00

17.98

59 820.14 19 100.00

16.3508 836.49 20 100.00

14.8644 851.36 21 100.00

13.5131 864.

87 22 100.00

12.2846 877.15 23 100.00

11.1678 88

8.32 24 100.00

10.1526 898.47 25 100.00

9.2296 907.70 26 100.00

8.3905 916.09 27 100.00

7.6278 923.72 28 100.00

6.9343 930.66

UNEVEN, OR

IRR

EGULAR, CASH FLOWS (Section

4.12

) 29 100.00

6.3039 936.96 30 100.00

5.7309 942.69 First, consider a security that pays $100 for 5 years plus a lump sum of $1,000 at the end of the 5th year. We can find the PV in several ways: (1) With a financial calculator using the step-by-step approach, or by finding the PV of the annuity plus the PV of the final $1,000 and then summing these two values, or by using the calculator’s cash flow register, or (2) with Excel, using either the PV or the

NPV

function. We illustrate the step-by-step and the two Excel approaches below.

31 100.00

5.2099 947.90 32 100.00

4.7362 952.64 33 100.00

4.3057 956.94 34 100.00

3.9143 960.86 35 100.00

3.5584 964.42 36 100.00

3.2349 967.65 PV of an Annuity Plus a

Lump Sum

(this figure is not in the text)

37 100.00

2.9408 970.59 Step-by-step:

38 100.00

2.6735 973.27 Interest rate = I =

12%

39 100.00

2.4304 975.70 40 100.00

2.2095 977.91 Periods: 0 1 2 3 4 5 41 100.00

2.0086 979.91 | | | | | | 42 100.00

1.8260 981.74 Constant PMTS

$0 $100 $100 $100 $100 $100 43 100.00

1.6600 983.40 Final CF

$1,000 44 100.00

1.5091 984.91 CF time line

$0 $100 $100 $100 $100

$1,100

45 100.00

1.3719 986.28 PV of

CFs

46 100.00

1.2472 987.53 $89.29

47 100.00

1.1338 988.66 79.72

48 100.00

1.0307 989.69 71.18

49 100.00

0.9370 990.63 63.55

50 100.00

0.8519 991.48

4.17

51 100.00

0.7744 992.26 $927.90 = PV of cash flow stream = value of the asset

52 100.00

0.7040 992.96 Calculator:

Enter N = 5, I/YR = 12, PMT = 100, and FV = $1,000. Press PV to get the answer, $927.90 (with a minus sign). You could also find the PV of the annuity and the PV of the final $1,000 and then sum them.

$927.90 53 100.00

0.6400 993.60 Excel Functions: You can use either the PV or the NPV function applied to the net CFs on Row 452.

54 100.00

0.5818

4.18

Using the PV function:

Fixed inputs: PV =

=PV(0.12,5,-100,-1000)

$927.90 55 100.00

0.5289 994.71 Cell references PV =

=PV(C446,G448,-C450,-G451)

$927.90 56 100.00

0.4809 995.19 Using the NPV function:

Fixed inputs:

NPV = =NPV(0.12,100,100,100,100,1100)

$927.90 57 100.00

0.4371 995.63 Cell references:

NPV = =NPV(C446,C452:G452)

$927.90 58 100.00

0.3974 996.03 59 100.00

0.3613 996.39 Now consider an irregular cash flow stream, where the CFs can take on any value.

60 100.00

0.3284 996.72 61 100.00

0.2986 997.01 Figure 4-7. PV of an Irregular Cash Flow Stream

62 100.00

0.2714 997.29 Step-by-step: 63 100.00

0.2468 997.53 Interest rate = I = 12% 64 100.00

0.2243 997.76 Periods: 0 1 2 3 4 5
| | | | | | 65 100.00

0.2039 997.96 CF Time Line:

$0.00 $100.00

$300

.00 $300.00

$300.00

$500.00

66 100.00

0.1854 998.15 PVs of the CFs:

$89.29

$239.16 $213.53 $190.66 $283.71

67 100.00

0.1685 998.31 68 100.00

0.1532 998.47 Σ C475:G475 = $1,016.35 = Sum of the individual PVs = PV of the irregular CF stream.

69 100.00

0.1393 998.61 70 100.00

0.1266 998.73 Here we put the PVs of each individual CF under the CF itself and then summed them to find the PV of

71 100.00

0.1151 998.85 the entire stream, rather than show them all in Column C as was done in Figure 4-6. This setup takes

72 100.00

0.1046 998.95 up less space and also makes the calculations quite transparent, which is useful, especially when the

73 100.00

0.0951 999.05 table must be explained to people who did not develop it. People appreciate transparency and clarity.

74 100.00

0.0865 999.14 75 100.00

0.0786 999.21 Calculator:

You could enter the cash flows into the cash flow register of a financial calculator, enter I/YR, and then press the NPV key to find the answer.

$1,016.35 76 100.00

0.0715 999.29 Excel Function:

Fixed inputs: NPV =

=NPV(0.12,100,300,300,300,500)

$1,016.35 77 100.00

0.0650 999.35 Cell references: NPV =

=NPV(C471,C474:G474)

$1,016.35 78 100.00

0.0591 999.41 79 100.00

0.0537 999.46 Our Excel formula ignores the initial cash flow (in Year 0). When entering a cash flow range, Excel assumes that the first value occurs at the end of the first year. As we will see later, if there is an initial cash flow, it must be added separately to complete the NPV formula result. Notice too that you can enter cash flows one-by-one, but if the cash flows appear in consecutive cells, you can enter the cell range, as we did here.

80 100.00

0.0488 999.51 81 100.00

0.0444 999.56 82 100.00

0.0403 999.60 83 100.00

0.0367 999.63 84 100.00

0.0333 999.67 85 100.00

0.0303 999.70

FUTURE VALUE OF AN UNEVEN, OR IRREGULAR, CASH FLOW STREAM (Section

4.13

) 86 100.00

0.0276 999.72 87 100.00

0.0251 999.75 We find the future value of uneven cash flow streams by compounding rather than discounting. The step-by-step approach works the same, but unfortunately, Excel does not have a net future value (

NFV

) function, although financial calculators do have this function. One way around this is to solve for the NPV and then find the FV of this amount at the end of the cash flow stream.

88 100.00

0.0228 999.77 89 100.00

0.0207 999.79 90 100.00

0.0188 999.81 91 100.00

0.0171 999.83 92 100.00

0.0156 999.84 Figure 4-8. FV of an Irregular Cash Flow Stream

93 100.00

0.0141 999.86 94 100.00

0.0129 999.87 Step-by-Step 95 100.00

0.0117 999.88 Periods: 0 1 2 3 4 5 96 100.00

0.0106 999.89 Interest rate = I = 12% 97 100.00

0.0097 999.90 | | | | | | 98 100.00

0.0088 999.91 CF Time Line: $0 $100 $300 $300 $300 $500 99 100.00

0.0080 999.92 FV of each CF:

$0.00

$157.35 $421.48 $376.32 $336.00

$500.00 100 100.00

0.0073

0.00
Sum of the Cash Flows’ FVs = FV of the stream = $1,791.15 Calculator:

You could enter the cash flows into the cash flow register of a financial calculator, enter I/YR, and then press the NFV key to find the answer.

$1,791.15
Excel: Step 1. Find NPV: =NPV(C505,C507:G507)

$1,016.35
Step 2. Compound NPV to find NFV: =FV(C507,G504,0,-G513)

$1,791.15
SOLVING FOR I WITH UNEVEN CASH FLOWS (Section 4.14) Assume that a bond will pay $100 at the end of each of the next 5 years, plus an additional $1,000 at the end of the 5th year. The cost of the bond is $927.90. What rate of return would you earn if you bought the bond? You could find the rate of return using Excel’s IRR (for “internal rate of return”) function or its RATE function, as shown below. The RATE function deals with situations where we have an annuity plus a final lump sum. The IRR function deals with any cash flow pattern, and it is easier to use. You could enter a guess as to the IRR, but this is not necessary. Finding the Interest Rate, Annuity Plus Lump Sum Annuity pmts

$100
Future lump sum

$1,000

Periods: 0 1 2 3 4 5

-$927.90

$100 $100 $100 $100 $1,100
Excel Function Approach: Cell references:

IRR = =IRR(B534:G534) 1

2.00% Excel Function Approach: Cell references:

RATE = =RATE(G532,B529,B534,B530)

12.00%

The IRR function is used to find the rate of return on capital budgeting projects, where the firm makes a capital expenditure and then expects to receive a series of cash inflows. Figure 4-9 illustrates this calculation. Note that the IRR function can be used even if one of the post-investment cash flows is negative. Change the 4th year CF from $300 to

–

$100 and see the IRR drop to 2.90%. Then change it back to $300.

Figure 4-9. IRR of an Uneven Cash Flow Stream

CF Time Line:

-$1,000

$100 $300 $300 $300 $500
Calculator:

You could enter the cash flows into the cash flow register of a financial calculator and then press the IRR key to find the answer. 12.55% Excel IRR Function:

Cell references: IRR =

=IRR(B549:G549)

12.55%

SEMIANNUAL AND OTHER COMPOUNDING PERIODS (Section

4.15

)

If $100 is invested in an account at an annual nominal interest rate of 12% for 1 year, what are the effective interest rates and the future values based on annual, semiannual, quarterly, monthly and daily compounding? When you work this problem, recognize that with more compounding periods, you receive interest sooner than with annual compounding, so you will earn more “interest on interest.” Therefore, you will end up with more money, and the effective interest rate will be higher, than with annual compounding. Nominal annual rate =

12%
Amount invested =

$100
Number of years =

1
Figure 4-10. Effect on $100 of Compounding More Frequently than Once a Year Frequency of Compounding Nominal

Annual

Rate Number of periods
per year (M)a Periodic I

nterest Rate Effective Annual Rateb Future Valuec Percentage increase in FV Annual 12% 1

12.0000%

$112.00 Semiannual

12% 2

6.0000% 12.

360

0% $112.36 0.32% Quarterly

12% 4

3.0000% 12.550

9% $112.55 0.1

7% Monthly

12% 12

1.0000% 12.6825% $112.68 0.12% Daily

12%

365 0.0329% 12.7475% $112.75 0.06% a We used 365 days per year in the calculations. bThe EFF% is calculated using text Equation 4-14. cThe Future Value is calculated using text Equation 4-1. Cost of Credit based on “Add-On” Interest. This table is not in the text, but the procedure is discussed in the “Truth in Lending Box”. This procedure is commonly used by retailers, auto dealers, and many other lenders. The calculator solution is explained in the text and also below. The Excel solution is explained just below. Amount borrowed = Cost of TV. Disregards the advanced payment, handled separately. $3,000.00 Nominal rate 8.00% Amount of interest = interest rate x

Amt borrowed $240.00 Stated loan size = Amt borrowed + Interest $3,240.00 Number of payments

12
Payment/month -$270.00 0 1 2 3 4 5 6 7 8 9 10 11 12
Amt borrowed $3,000.00
Monthly Pmts

-$270.00 -$270.00 -$270.00 -$270.00 -$270.00 -$270.00 -$270.00 -$270.00 -$270.00 -$270.00 -$270.00 -$270.00
CF time line

$2,730.00

-$270.00 -$270.00 -$270.00 -$270.00 -$270.00 -$270.00 -$270.00 -$270.00 -$270.00 -$270.00 -$270.00 $0.00
IRR = periodic rate: =IRR(B593:M593) = 1.431

3% APR rate: =E595*G587 = 17.1758% EFF%: =(1+E595)^G587-1 = 18.5945% Before the ruth in Lending Act, auto dealers, TV dealers, and even student loan officers would make add-on loans and just tell customers about the 8% stated rate. After 1968, such lenders were required to also report the much higher APR rate. But lenders are still not required to report the even higher EFF%, which is the “true” rate that borrowers should base decisions on. We showed the cash flows above as a”horizontal” time line, but it’s easier to fit the analysis on the screen using a vertical time line, as shown below. The calculations are identical, but the vertical setup is better from a presentation standpoint if we have more cash flows than can be shown on the screen. Periods

Borrowed Payments Monthly CFs 0 $3,000.00 -$270.00 $2,730.00
1 -$270.00 -$270.00
2 -$270.00 -$270.00
3 -$270.00 -$270.00
4 -$270.00 -$270.00
5 -$270.00 -$270.00
6 -$270.00 -$270.00
7 -$270.00 -$270.00
8 -$270.00 -$270.00
9 -$270.00 -$270.00
10 -$270.00 -$270.00
11 -$270.00 -$270.00
12 $0.00 $0.00
IRR = periodic rate:

1.431313% APR rate:

17.175758% EFF%:

18.594507% To solve the problem with a calculator, first set the machine to BEGIN mode, then enter N = 12, PV = 3000, and PMT = -270. When you press the I/YR key to get the periodic rate, 1.431313, which you can use to find the APR and EFF% as we did above. AMORTIZED LOANS (Section 4.17) If a loan is to be repaid in equal amounts on a monthly, quarterly, or annual basis it is said to be an amortized loan. Figure 4-11 below illustrates the amortization process. A company borrows

$100,000

, with the loan to be repaid in 5 equal payments at the end of each of the next 5 years. The lender charges 6% on the balance at the beginning of each year. With a calculator, we solve for the required payment, then we construct an amortization table as shown in Figure 4-11. It is far easier, and less prone to errors, to make Figure 4-11 with Excel, as we do here.

Figure 4-11.

Loan

Amortization Schedule, $100,000 at 6% for 5

Years Amount borrowed:

$100,000
Years:

5
Rate:

6%
PMT: $23,739.64 =PMT(C646,C645,-C644) Year

Beginning

Amount
(1) Payment
(2) Interesta
(3) Repayment of Principalb
(2) – (3) = (4) End

ing Balance

(1) – (4) = (5) 1

$100,000.00

$23,739.64

$6,000.00 $17,739.64 $82,260.36 2 $82,260.36 $23,739.64

$4,935.62 $18,804.02 $63,456.34 3 $63,456.34 $23,739.64

$3,807.38 $19,932.26 $43,524.08 4 $43,524.08 $23,739.64

$2,611.44 $21,128.20 $22,395.89 5 $22,395.89 $23,739.64

$1,343.75

$22,395.89 $0.00
a Interest in each period is calculated by multiplying the loan balance at the beginning of the year by the interest rate. Therefore, interest in Year 1 is $100,000(0.06) = $6,000; in Year 2 it is $82,260.36(0.06) = $4,935.62; and so on. b Repayment of principal is the $23,739.64 annual payment minus the interest charge for the year, $17,739.64 for Year 1. Growing Annuities (Section 4.18) Example 1. A 65-year-old retiree expects to live for 20 more years, currently has

1,000,000

of savings, expects to earn a 6% rate on his or her money, and expects inflation to average 3%. How much can he or she withdraw at the beginning of each year and keep the withdrawals constant in real terms, i.e., growing at the same rate as inflation and thus enabling him or her to maintain a constant standard of living? Inputs Number of years = 20
Nominal interest rate,

rNOM

6%
Available to invest = Portfolio = $1,000,000 Inflation

rate =

3%
Initial withdrawal (guess) = $50,000 Withdrawal

at beginning or end?

Beginning
Step 1: Set up an “Amortization Table” to show exactly what’s happening. We begin with $1 million. But we immediately make the first withdrawal, hence have less than $1 million to invest. We don’t know how much we can withdraw initially, so we make a “guess” of $50,000. We subtract the $50,000 from the initial portfolio and get $950,000, which is invested at 6% and thus earns $57,000. The earnings are added to the beginning balance, less the withdrawal, to produce the ending balance, which is carried forward to create the next beginning balance. This process is continued for 20 years. We want to end up with a $0.00 ending balance. With the $50,000 initial withdrawal, we see that the ending balance is greater than zero. Therefore, we should make a larger initial withdrawal. We could just go through a series of trials and errors until we found an initial withdrawal that produced the zero ending balance. The amount that does the trick is $64,786.87708. Replace the $50,000 with 64786.87708 to prove that this value “works.” As you might guess, there are two much easier ways to find the initial withdrawal amount: (1) Use Excel’s Goal Seek function, or (2) use an equation. We explain those procedures below, and we also graph the results. We see that the withdrawals rise every year with inflation, earnings decline, and the balance declines faster and faster, as the withdrawals increase and the earnings decline. Withdrawal

Beginning Balance BOY: Amount Withdrawn Investable Funds Earnings Ending Balance 1

$1,000,000.00 $64,786.88 $935,213.12 $56,112.79 $991,325.91 2 $991,325.91

$66,730.48 $924,595.43 $55,475.73 $980,071.15 3 $980,071.15

$68,732.40 $911,338.75 $54,680.33 $966,019.08 4 $966,019.08

$70,794.37 $895,224.71 $53,713.48 $948,938.19 5 $948,938.19

$72,918.20 $876,019.99 $52,561.20 $928,581.19 6 $928,581.19

$75,105.75 $853,475.44 $51,208.53 $904,683.97 7 $904,683.97

$77,358.92 $827,325.05 $49,639.50 $876,964.55 8 $876,964.55

$79,679.69 $797,284.87 $47,837.09 $845,121.96 9 $845,121.96

$82,070.08 $763,051.88 $45,783.11 $808,835.00 10 $808,835.00

$84,532.18 $724,302.82 $43,458.17 $767,760.98 11 $767,760.98

$87,068.15 $680,692.84 $40,841.57 $721,534.41 12 $721,534.41

$89,680.19 $631,854.22 $37,911.25 $669,765.47 13 $669,765.47

$92,370.60 $577,394.88 $34,643.69 $612,038.57 14 $612,038.57

$95,141.71 $516,896.86 $31,013.81 $547,910.67 15 $547,910.67

$97,995.96 $449,914.70 $26,994.88 $476,909.59 16 $476,909.59

$100,935.84 $375,973.74 $22,558.42 $398,532.17 17 $398,532.17

$103,963.92 $294,568.25 $17,674.09 $312,242.34 18 $312,242.34

$107,082.84 $205,159.51 $12,309.57 $217,469.08 19 $217,469.08

$110,295.32 $107,173.76 $6,430.43 $113,604.18 20 $113,604.18 $113,604.18

-$0.00

-$0.00 -$0.00
Using Goal Seek: 1. Put the pointer on the cell for the Ending Balance after the 20th withdrawal, F714. 2. Click Data, What-If-Analysis, Goal Seek to get a dialog box, which you then fill out as shown to the right. 3. You will be at the “Set cell” because you put the pointer there initially. 4. Go down to the “To value to” cell. You want to get 0 as the ending balance, so enter 0 here. 5. Now move down to the “By changing cell” box, then click on the cell with the Year 1 withdrawal, C695, to select it. 6. Now click OK, and the initial withdrawal will change to $64,786.88, and the final balance will go to $0.00. You could increase the decimals shown to see the extra digits Excel calculated. Calculator:

Step 1: Find the real rate of return, rr. rr = (1+rNOM)/(1 + inflation) – 1 Here is a formula for the present value of a growing annuity: =

(1.06)/(1.03) – 1 = 0.0291262136 PVIFGADue =

[1 – [(1 + g)/(1 + rNOM)]N] [(1 + rNOM)/(rNOM − g)] rr =

2.912621

PVIFGADue =

15.4352246178 PMT = PV / PVIFGADue =

$64,786.88
Step 2: Use the PMT function in Excel or a calculator to find the initial amount to be withdrawn. Be sure to set the calculator to BEGIN mode, and make a similar adjustment to the Excel function. N=

20
I= rr =

2.9126214%
PV =

-1,000,000 PMT = $64,786.88

This is consistent with the value found using Goal Seek. If the first withdrawal occurs at the end rather than the beginning of the first year, then the amount of investable funds during each year will be somewhat larger, and the initial withdrawal to leave a zero final balance will also be somewhat larger. We can modify the table by making the first withdrawal at the end of the year and then using Goal Seek to find the initial withdrawal, which is slightly higher than the case of the annuity due because the original funds earned interest for a year prior to the first withdrawal. Inputs

Number of years = 20
Nominal interest rate, rNOM = 6%
Available to invest = Portfolio = $1,000,000
Inflation rate = 3%
Initial withdrawal (guess) = $50,000

Withdrawal at beginning or end?

End
Beginning Balance

EOY: Amount Withdrawn

Investable Funds Earnings Ending Balance
1 $1,000,000.00

$68,674.09

$1,000,000.00

$60,000

.00

$991,325.91
2 $991,325.91

$70,734.31

$991,325.91

$59,479.55

$980,071.15
3 $980,071.15

$72,856.34

$980,071.15

$58,804.27

$966,019.08
4 $966,019.08

$75,042.03

$966,019.08

$57,961.14

$948,938.19
5 $948,938.19

$77,293.29

$948,938.19

$56,936.29

$928,581.19
6 $928,581.19

$79,612.09

$928,581.19

$55,714.87

$904,683.97
7 $904,683.97

$82,000.45

$904,683.97

$54,281.04

$876,964.55
8 $876,964.55

$84,460.47

$876,964.55

$52,6

17.8

$845,121.96
9 $845,121.96

$86,994.28

$845,121.96

$50,707.32

$808,835.00
10 $808,835.00

$89,604.11

$808,835.00

$48,530.10

$767,760.98
11 $767,760.98

$92,292.23

$767,760.98

$46,065.66

$721,534.41
12 $721,534.41

$95,061.00

$721,534.41

$43,292.06

$669,765.47
13 $669,765.47

$97,912.83

$669,765.47

$40,185.93

$612,038.57
14 $612,038.57

$100,850.22

$612,038.57

$36,722.31

$547,910.67
15 $547,910.67

$103,875.72

$547,910.67

$32,874.64

$476,909.59
16 $476,909.59

$106,991.99

$476,909.59

$28,614.58

$398,532.17
17 $398,532.17

$110,201.75

$398,532.17

$23,911.93

$312,242.34
18 $312,242.34

$113,507.81

$312,242.34

$18,734.54

$217,469.08
19 $217,469.08

$116,913.04

$217,469.08

$13,048.14

$113,604.18
20 $113,604.18

$120,420.43

$113,604.18

$6,816.25

$0.00
A modified version of the formula could also be used to determine the initial withdrawal: rr = (1+rNOM)/(1 + inflation) – 1
rr = 2.9126214%
Now use the PMT function in Excel or a calculator to find the initial amount to be withdrawn, assuming payments at the end of the year.

N= 20
I= rr = 2.9126214%

PV = 1,000,000
PMT =

$66,673.87 Adjusted PMT =

$68,674.09

= PMT(1+ Inflation). The adjustment accounts for Year 1 inflation. Example 2, Growing Annuities: Initial deposit to accumulate a given sum. You need to accumulate $100,000 in 10 years. You plan to make an initial deposit today, then make 9 more deposits at the beginning of the next 9 years, but with the deposits increasing at the inflation rate. You expect to earn 6% on your funds, and you expect a 2% inflation rate. How large must your initial deposit be to enable you to reach your $100,000 target? We can set up a table with an arbitrary initial deposit that grows at the inflation rate and is then compounded at the nominal rate for (N – t) years. The sum of the compounded amounts should total to $100,000. With an arbitrary initial amount the ending amount is not likely to be $100,000, so we use goal seek as shown in the completed dialog box to find the correct initial deposit. Inputs: Years 10
Amount Needed (FV)

$100,000
Nominal rate earned on account 6.00% Inflation 2.00%
Beginning or End?

Beginning
Use Goal Seek in the following table to determine the initial deposit. Start with any value for BOY payment at time zero, then use Goal Seek to set the final balance to the target by changing the BOY t=0 payment. BOY Payment Period (t) Initial(1+I)^t Compounded value 0

$6,598.87 $11,817.57 1

$6,730.85 $11,371.62 2

$6,865.46 $10,942.51 3

$7,002.77 $10,529.58 4

$7,142.83 $10,132.24 5

$7,285.68 $9,749.89 6

$7,431.40 $9,381.97 7

$7,580.03 $9,027.93 8

$7,731.63 $8,687.26 9

$7,886.26 $8,359.43 N = 10 $0.00 $100,000.00
Calculator approach: Find the real rate: rr = (1+rNOM)/(1 + inflation) – 1 = 3.921569% Find the real required future amount, discounted at the inflation rate. This is our constant dollar future target: Target real FV = (Nominal FV)/(1 + Inflation)N = $82,034.83 Use a calculator or the Excel PMT function to find the initial payment. The PV=0, FV=82034.83, rate=3.921569, and set to Beginning mode.

$6,598.87
This is consistent with the Goal Seek solution.

&P of &N

20% 0.0 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 100.0 120.0 144.0 172.8 207.36 248.832 298.5984 358.31808 429.9816959999999 515.9780352 619.17364224 10% 0.0 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 100.0 110.0 121.0 133.1000000000001 146.4100000000001 161.051 177.1561000000001 194.8717100000001 214.3588810000001 235.7947691000001 259.3742460100002 5% 0.0 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 100.0 105.0 110.25 115.7625 121.550625 127.62815625 134.0095640625 140.710042265625 147.7455443789063 155.1328215978516 162.8894626777441 0% 0.0 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 100.0 100.0 100.0 100.0 100.0 100.0 100.0 100.0 100.0 100.0 100.0 -5% 0.0 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 100.0 80.0 64.00000000000001 51.20000000000001 40.96000000000002 32.76800000000002 26.21440000000002 20.97152000000002 16.77721600000001 13.42177280000001 10.73741824000001 Years

0% 0.0 5.0 10.0 15.0 20.0 25.0 30.0 35.0 40.0 45.0 50.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 5% 0.0 5.0 10.0 15.0 20.0 25.0 30.0 35.0 40.0 45.0 50.0 1.0 0.783526166468459 0.613913253540759 0.48101709809097 0.376889482873001 0.295302771697762 0.231377448655858 0.181290285352577 0.142045682300278 0.111296508916133 0.0872037269723806 10% 0.0 5.0 10.0 15.0 20.0 25.0 30.0 35.0 40.0 45.0 50.0 1.0 0.620921323059155 0.385543289429531 0.239392049369163 0.148643628024143 0.092295998177064 0.057308553301168 0.0355841027383673 0.0220949281521799 0.0137192120211485 0.00851855127950061 20% 0.0 5.0 10.0 15.0 20.0 25.0 30.0 35.0 40.0 45.0 50.0 1.0 0.401877572016461 0.161505582889846 0.0649054715188745 0.0260840533045888 0.0104825960103961 0.00421272023308743 0.00169299777885779 0.000680377836796632 0.000273428593105642 0.000109884819117172

Years

Present Value
of $1

0.0 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0 12.0 13.0 14.0 15.0 16.0 17.0 18.0 19.0 20.0 21.0 22.0 23.0 24.0 25.0 26.0 27.0 28.0 29.0 30.0 31.0 32.0 33.0 34.0 35.0 36.0 37.0 38.0 39.0 40.0 41.0 42.0 43.0 44.0 45.0 46.0 47.0 48.0 49.0 50.0 51.0 52.0 53.0 54.0 55.0 56.0 57.0 58.0 59.0 60.0 61.0 62.0 63.0 64.0 65.0 66.0 67.0 68.0 69.0 70.0 71.0 72.0 73.0 74.0 75.0 76.0 77.0 78.0 79.0 80.0 81.0 82.0 83.0 84.0 85.0 86.0 87.0 88.0 89.0 90.0 91.0 92.0 93.0 94.0 95.0 96.0 97.0 98.0 99.0 100.0 100.0 90.9090909090909 82.64462809917354 75.13148009015775 68.30134553650706 62.0921323059155 56.44739300537772 51.31581182307065 46.65073802097331 42.40976183724847 38.55432894295314 35.04938994813922 31.86308177103566 28.96643797366878 26.33312543060797 23.93920493691634 21.76291357901485 19.7844668900135 17.98587899092136 16.35079908265578 14.86436280241435 13.51305709310395 12.28459735736723 11.16781577942475 10.15255979947705 9.229599817706406 8.390545288824004 7.627768444385457 6.934334949441325 6.303940863128477 5.730855330116796 5.20986848192436 4.73624407447669 4.305676431342446 3.914251301220405 3.558410273836731 3.234918430760665 2.94083493705515 2.673486306413772 2.430442096739792 2.209492815217993 2.008629832016357 1.82602712001487 1.660024654558973 1.509113322326339 1.371921202114853 1.247201092831685 1.133819175301532 1.030744704819574 0.937040640745067 0.851855127950061 0.774413752681874 0.704012502438067 0.640011365852788 0.581828514411626 0.528935013101478 0.480850011910434 0.437136374464031 0.39739670405821 0.361269730962009 0.328427028147281 0.298570025588437 0.271427295989488 0.246752087263171 0.224320079330156 0.203927344845596 0.185388495314178 0.168534995740162 0.153213632491056 0.139285120446415 0.126622836769468 0.115111669790425 0.10464697253675 0.0951336113970458 0.0864851012700416 0.0786228193364015 0.0714752903058196 0.0649775366416541 0.0590704878560492 0.0537004435054993 0.0488185850049993 0.0443805318227267 0.0403459380206606 0.0366781254733278 0.033343750430298 0.03031250039118 0.0275568185374364 0.0250516532158512 0.0227742301962284 0.0207038456329349 0.0188216778481226 0.017110616225566 0.0155551056596055 0.0141410051450959 0.0128554592228144 0.0116867811116495 0.0106243464651359 0.00965849678648719 0.00878045162407926 0.00798222874916296 0.00725657159014815

Years (N)

PV of Additional Payments in an Annuity

Earnings 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0 12.0 13.0 14.0 15.0 16.0 17.0 18.0 19.0 20.0 56112.7873752 55475.725614168 54680.32527736776 53713.48260415 52561.19950484336 51208.52665791167 49639.50309564741 47837.09206479512 45783.11293559396 43458.16891904806 40841.57033772897 37911.25318003687 34643.69265154458 31013.81141976391 26994.88223035019 22558.42455333367 17674.09489737103 12309.57040817575 6430.42534413763 -2.39863962633535E-6 Withdrawals 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0 12.0 13.0 14.0 15.0 16.0 17.0 18.0 19.0 20.0 64786.87708 66730.4833924 68732.397894172 70794.36983099716 72918.20092592709 75105.7469537049 77358.91936231605 79679.68694318553 82070.0775514811 84532.17987802553 87068.1452743663 89680.1896325973 92370.5953215752 95141.71318122247 97995.96457665914 100935.8435139589 103963.9188193777 107082.836383959 110295.3214754778 113604.1811197421 Balance 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0 12.0 13.0 14.0 15.0 16.0 17.0 18.0 19.0 20.0 991325.9102952 980071.152516968 966019.0799001637 948938.1926733165 928581.1912522326 904683.9709564395 876964.554689771 845121.9598113805 808834.9951954933 767760.9842365158 721534.4092998785 669765.4728473181 612038.5701772876 547910.668415829 476909.5860695201 398532.1671088948 312242.3431868882 217469.0772111049 113604.1810797648 -4.23759667319246E-5

I = -20%

4.2

N 5
I 10%
PV $100
PMT $0 FV

and plan to purchase a 3-year certificate of deposit (CD) that pays 4% interest compounded annually. How much will you have when the CD matures?

N 3
I 4%
PV $2,000
PMT $0 FV

Interest rate $2,249.73
5%

20%

N 10
I 8%

$100

PMT $0

N 10

PV ($M) $100

PMT $0 FV ($M)

N 100
I 5%
PV $1
PMT $0 FV

N 100
I 10%
PV $1

PMT $0 FV

SECTION 4.2
					SOLUTIONS TO SELF-TEST
What would the future value of $100 be after 5 years at 10% compound interest?
$161.05
Suppose you currently have			$2,000
				$2,249.73
How would your answer change if the interest rate were 5%, or 6%, or 20%?
$2,315.25
$2,382.03
$3,456.00
A company’s sales in 2009 were $100 million. If sales grow at 8%, what will they be 10 years later?
	PV ($M)
	FV ($M)	$215.89
What would they be if they decline by 8% per year for 10 years?
-8%
$43.44
How much would $1, growing at 5% per year, be worth after 100 years?
$131.50
What would FV be if the growth rate were 10%?
$13,780.61

4.3

SOLUTIONS TO SELF-TEST

N 3
I 4%

PMT $0
FV $2,249.73 PV

N 5
I 4%
PMT $0

FV $2,249.73 PV

N 5

I 6%

PMT $0

FV $2,249.73 PV

N 100
I 5%
PMT $0

FV $1,000,000 PV

N 100

I 20%

PMT $0

FV $1,000,000 PV

SECTION 4.3
Suppose a risk-free bond promises to pay $2,249.73 in 3 years. If the going risk-free interest rate is 4%, how much is the bond worth today?
$2,000.00
How would your answer change if the bond matured in 5 rather than 3 years?
$1,849.11
If the risk-free interest rate is 6% rather than 4%, how much is the 5-year bond worth today?
$1,681.13
How much would $1,000,000 due in 100 years be worth today if the discount rate were 5%?
$7,604.49
What if the discount rate were 20%?
$0.0121

4.4

SOLUTIONS TO SELF-TEST

N 10
PMT $0

PV $585.43
FV $1,000 I

N 10
PMT $0

FV $1,000 I

N 10
PMT $0

FV $1,000 I

per share in 1997. Ten years later, in 2007, it earned

. What was the growth rate in Microsoft’s earnings per share (EPS) over the 10-year period?

N 10
PMT $0

PV $0.33
FV $1.42

N 10
PMT $0
PV $0.33

FV $1.00 I

SECTION 4.4
Suppose you can buy a U.S. Treasury bond which makes no payments until the bond matures 10 years from now, at which time it will pay you $1,000. What interest rate would you earn if you bought this bond for	$585.43
5.50%
What rate would you earn if you could buy the bond for $550?
$550.00
6.16%
For		$600
$600.00
5.24%
Microsoft earned		$0.33	$1.42
	15.71%
If EPS in 2007 had been $1.00 rather than $1.42, what would the growth rate have been?
11.72%

4.5

SOLUTIONS TO SELF-TEST

I 6%
PMT $0

PV $1,000
FV $2,000 N

I 10%
PMT $0
PV $1,000

FV $2,000 N

I 15.71%
PMT $0

PV $1.42
FV

SECTION 4.5
How long would it take $1,000 to double if it were invested in a bank that pays 6% per year?
11.90
How long would it take if the rate were 10%?
7.27
Microsoft’s 2007 earnings per share were $1.42, and its growth rate during the prior 10 years was 15.71% per year. If that growth rate were maintained, how long would it take for Microsoft’s EPS to double?
$2.84	4.75

4.7

SOLUTIONS TO SELF-TEST

N 5 4
I 10% 10%

PMT -$100 $0
PV $0 -$100

Annuity Data

N 5 0
I 10% 10% Years of int 0
PMT -$100 0
PV $0 -$100 Payment FV $100.00

N 5
I 4%

PMT

PV $0 FV

N 5
I 6%
PMT -$2,500

PV $0 FV

N 5

I 3%

PMT -$2,500

PV $0 FV

SECTION 4.7
For an ordinary annuity with 5 annual payments of $100 and a 10% interest rate, for how many years will the 1st payment earn interest, and what is the compounded value of this payment at the end?
	Annuity Data	1st Payment Data
	Years of int
	Payment FV	$146.41
Answer this same question for the 5th payment.
5th Payment Data
Assume that you plan to buy a condo 5 years from now, and you estimate that you can save $2,500 per year to get a down payment. You plan to deposit the money in a bank that pays 4% interest, and you will make the first deposit at the end of this year. How much will you have after 5 years?
			-$2,500
	$13,540.81
How would your answer change if the bank’s interest rate were increased to 6%, or decreased to 3%?
$14,092.73
$13,272.84

4.8

SOLUTIONS TO SELF-TEST

BEGIN MODE
N 5
I 4%

PV $0
PMT -$2,500 FV

N 5
I 4%
PV $0

PMT -$2,500 FV $13,540.81

SECTION 4.8

Assume that you plan to buy a condo 5 years from now, and you need to save for a down payment. You plan to save $2,500 per year, with the first payment being made immediately and deposited in a bank that pays 4%. How much will you have after 5 years?

$14,082.44

How much would you have if you made the deposits at the end of each year?

4.9

SOLUTIONS TO SELF-TEST

N 10
I 10%

PMT -$100
FV $0 PV

N 10
I 4%
PMT -$100

FV $0 PV

N 10

I 0%

PMT -$100

FV $0 PV

BEGIN MODE BEGIN MODE BEGIN MODE
N 10 N 10 N 10
I 10% I 4% I 0%
PMT -$100 PMT -$100 PMT -$100
FV $0 FV $0

FV $0
PV

PV $1,000.00

N 10
I 8%
PMT -$100

FV $0 PV

BEGIN MODE
N 10
I 8%
PMT -$100

FV $0 PV

SECTION 4.9
What is the PVA of an ordinary annuity with 10 payments of $100 if the appropriate interest rate is 10%?
$614.46
What would the PVA be if the interest rate were 4%?
$811.09
What if the interest rate were 0%?
	$1,000.00
What would the PVAs be if we were dealing with annuities due?
Part a	Part b	Part c
$675.90	$843.53
Assume that you are offered an annuity that pays $100 at the end of each year for 10 years. You could earn 8% on your money in other equally risky investments. What is the most you should pay for the annuity?
$671.01
If the payments began immediately, then how much would the annuity be worth?
$724.69

4.10

SOLUTIONS TO SELF-TEST

N 10

I 7%
PV $100,000
FV $0 PMT

BEGIN MODE
N 10
I 7%
PV $100,000

FV $0 PMT

PV $100,000

PMT

FV $0 N 17.8
I

PV $100,000
PMT -$10,000

FV $0 N 10.0

I 7.0%

PV $100,000

PMT

FV $0 N

N 12
PMT

PV $100,000

FV $0 I

BEGIN MODE
N 10
PMT -$10,000

PV $60,000
FV $0 I

BEGIN MODE
N 10
I 6%
PMT -$10,000

FV $0 PV

SECTION 4.10
Suppose you inherited $100,000 and invested it at 7% per year. How large of a withdrawal could you make at the end of each of the next 10 years and end up with zero?
-$14,237.75
How would your answer change if you made withdrawals at the beginning of each year?
-$13,306.31
If you had $100,000 that was invested at 7% and you wanted to withdraw $10,000 at the end of each year, how long would your funds last?
	7.0%
		-$10,000
How long would they last if you earned 0%?
0.0%
How long would they last if you earned the 7% but limited your withdrawals to $7,000 per year?
* This result means that with $7,000 withdrawals, you would never exhaust the funds.
-$7,000
ERROR:#NUM!
Your rich uncle named you as the beneficiary of his life insurance policy. The insurance company gives you a choice of $100,000 today or a 12-year annuity of $12,000 at the end of each year. What rate of return is the insurance company offering?
-$12,000
6.11%
Assume that you just inherited an annuity that will pay you $10,000 per year for 10 years, with the first payment being made today. A friend of your mother offers to give you $60,000 for the annuity. If you sell it to him, what rate of return will your mother’s friend earn on the investment?
13.70%
If you think a “fair” rate of return would be 6%, how much should you ask for the annuity?
$78,016.92

4.11

SOLUTIONS TO SELF-TEST

PMT $1,000
I 5% PV

PMT $1,000

I 5% PV

SECTION 4.11

What is the present value of a perpetuity that pays $1,000 per year, beginning one year from now, if the appropriate interest rate is 5%?

$20,000

What would the value be if the annuity began its payments immediately?

**The perpetuity formula values payments 1 through infinity. If a payment is to be received immediately, it must be added to the formula result.

$21,000

4.12

SOLUTIONS TO SELF-TEST

Interest rate 6%
Year 0 1 2 3 4 5

$0 $100 $100 $100 $100 $100

Lump Sum $500

$0 $100 $100 $100 $100 $600

NPV

Interest rate 6%

Year 0 1 2 3 4 5 6 7 8 9 10
Ann Pmt $0 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100
Lump Sum $500
Total CFs $0 $100 $100 $100 $100 $100 $100 $100 $100 $100 $600
NPV

at the end of Year 2, $0 at the end of Year 3, and

at the end of Year 4, assuming the interest rate is 8%?

Interest rate 8%
Year 0 1 2 3 4
CFs $0 $100 $200 $0 $400
NPV

SECTION 4.12
What is the present value of a 5-year ordinary annuity of $100 plus an additional $500 at the end of Year 5 if the interest rate is 6%?
	Ann Pmt
	Total CFs
$794.87
How would the PV change if the $100 payments occurred in Years 1 through 10 and the $500 came at the end of Year 10?
$1,015.21
What is the present value of the following uneven cash flow stream: $0 at Time 0, $100 at the end of Year 1 (or at Time 1),		$200	$400
$558.07

4.13

SOLUTIONS TO SELF-TEST

Interest rate 15%
Year 0 1 2 3
CFs $0 $100 $150 $300

$0.00

$300.00

NFV

SECTION 4.13
What is the future value of this cash flow stream: $100 at the end of 1 year, $150 after 2 years, and $300 after 3 years, assuming the appropriate interest rate is	15%
FV of CFs	$132.25	$172.50
$604.75

4.14

SOLUTIONS TO SELF-TEST

Year 0 1 2 3 4

Ann Pmt

$100 $100 $100 $100

Lump Sum $200
Total CFs -$465 $100 $100 $100 $300
IRR

Year 0 1 2 3

CFs -$465 $100 $200 $300
IRR

SECTION 4.14
An investment costs $465 now and is expected to produce cash flows of $100 at the end of each of the next 4 years, plus an extra lump sum payment of $200 at the end of the 4th year. What is the expected rate of return on this investment?
		-$465
9.05%
An investment costs $465 and is expected to produce cash flows of $100 at the end Year 1, $200 at the end of Year 2, and $300 at the end of Year 3. What is the expected rate of return on this investment?
11.71%

4.15

SOLUTIONS TO SELF-TEST

N 3
I 8%

PV -$100
PMT $0 FV

N 36
I

PV -$100

PMT $0 FV

N 3
I 8%
PMT $0

FV $100 PV

Compounded monthly?

N 36
I 0.67%
PMT $0

FV $100 PV

, with interest paid monthly. What is the EFF% on such a loan?

Nominal rate 18%

19.56%

SECTION 4.15
What is the future value of $100 after 3 years if the appropriate interest rate is 8%, compounded annually?
$125.97
	Compounded monthly?
	0.67%
$127.02
What is the present value of $100 due in 3 years if the appropriate interest rate is 8%, compounded annually?
$79.38
$78.73
Credit card issuers must by law print their annual percentage rate (APR) on their monthly statements. A common APR is	18%
	Comp/year
	Effective rate	19.56%	=(1+B35/B36)^B36-1
=EFFECT(B35,B36)

4.16

SOLUTIONS TO SELF-TEST

Loan $1,000,000
Interest rate 9%

360

Loan $1,000,000
Interest rate 9%

Days/year 365

Interest pd (days) 30

Interest paid

Loan $1,000
Interest rate 7%
Comp/year 365

Effective rate

SECTION 4.16
Suppose a company borrowed $1 million at a rate of 9%, simple interest, with interest paid at the end of each month. The bank uses a 360-day year. How much interest would the firm have to pay in a 30-day month?
	Days/year
	Interest pd (days)
	Interest paid	$7,500
What would the interest be if the bank used a 365-day year?
$7,397.26
Suppose you deposited $1,000 in a credit union that pays 7% with daily compounding and a 365-day year. What is the EFF%, and how much could you withdraw after 7/12 of a year?
Time period (months)
7.250098%	Account value	$1,041.67

4.17

SOLUTIONS TO SELF-TEST

Years 5

Periodic I

PV $100,000
FV $0

PMT

$500.00

on a student loan at a rate of 8% and now must repay it in 3 equal installments at the end of each of the next 3 years. How large would your payments be, how much of the first payment would represent interest and how much would be principal, and what would your ending balance be after the first year?

N 3
I 8%

PV $30,000

FV $0

PMT

Amount borrowed: $30,000
Years: 3
Rate: 8%
PMT: -$11,641.01
Year
1

$11,641.01

2 $20,758.99 $11,641.01

3 $10,778.71 $11,641.01

$10,778.71 $0.00

SECTION 4.17
Consider again the example in Figure 4-11. If the loan were amortized over 5 years with 60 monthly payments, how much would each payment be, and how would the first payment be divided between interest and principal?
Months = N
Nom. I
0.5000%
$1,933.28
First payment interest:	=B10*B11
First payment principal:	$1,433.28	=B13-C15
Suppose you borrowed		$30,000
	–			$11,641.01
Loan Amortization Schedule, $30,000 at 8% for 3 Years
Beginning Amount (1)	Payment (2)	Interest (3)	Repayment of Principal (4)	Ending Balance (5)
$30,000.00	$2,400.00	$9,241.01		$20,758.99
$1,660.72	$9,980.29			$10,778.71
$862.30
Rather than focus on Year 1 data, we just constructed the full amortization schedule.

4.18

SOLUTIONS TO SELF-TEST

rNOM 10%
Inflation 5%
rr

SECTION 4.18
If the nominal interest rate is 10% and the expected inflation rate is 5%, what is the expected real rate of return?
=((1+B6)/(1+B7))-1 =	4.7619%

–
+

I
1
I
I)
(1
PMT
N



















I
1
I

I)(1

PMT
N
I)
(1
I
1
I
I)
(1
PMT
N
+

–

I)(1
I
1
I
I)(1
PMT
N

+ –

+
×
÷
÷
ø
ö
ç
ç
è
æ
+
´
N
I)
(1

I
1

–
I
1
PMT










N

I)(1 I

1
–
I
1
PMT
I)
(1
N
I)
(1

I
1

–
I
1
PMT
+
÷
÷
ø
ö
ç
ç
è
æ
+
´
I)(1
N
I)(1 I
1
–
I
1

PMT 












Turn in your highest-quality paper
Get a qualified writer to help you with

“ Finance Excel Question Problem 2 ”

Get high-quality paper

Guarantee! All work is written by expert writers!

Still stressed from student homework?

Get quality assistance from academic writers!

Order now