1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
A B C D E
PROBLEM 2.95
i= 10.00%
n= 8
INITIAL CF= -$50,000.00
A1=$13,000.00
G= -$1,000.00
A= $623.52 USING INTEREST TABLES THIS IS
=-$50,000*(A|P 10%,8)+$13,000-$1,000*(A|G 10%,8)
SEE BELOW FOR TABULATED CASH FLOWS TO CHECK ANSWER
EOY CF
0 -$50,000.00
1 $13,000.00
2 $12,000.00
3 $11,000.00
4 $10,000.00
5 $9,000.00
6 $8,000.00
7 $7,000.00
8 $6,000.00
A= $623.32 USING EXCEL’S PMT AND NPV FUNCTIONS THIS IS
=PMT(10%,8,-(NPV(10%,CF YR 1:CF YR 8)-50000))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
A B C D E F G H I
PROBLEM 2.96
i= 8.00%
n= 8
INITIAL CF= -$50,000.00
A1=$13,000.00
G= -$1,000.00
FW= $12,771.82 USING INTEREST TABLES THIS IS
=(-$50,000+$13,000*(P|A 8%,8)-$1,000*(P|G 8%,8))*(F|P 8%,8)
SEE BELOW FOR TABULATED CASH FLOWS TO CHECK ANSWER
EOY CF
0 -$50,000.00
1$13,000.00
2$12,000.00
3$11,000.00
4$10,000.00
5$9,000.00
6$8,000.00
7$7,000.00
8$6,000.00
FW= $12,771.80
USING EXCEL’S FV AND NPV FUNCTIONS THIS IS
=FV(8%,8,,-(NPV(8%,CF YR 1:CF YR 8)-50000))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
28
29
30
31
32
33
34
35
36
A B C D E F G H I
FIRST, DETERMINE THE PW OF THE ORIGINAL CASH FLOW SERIES. THEN,
LET X BE ANY VALUE AND SET UP A TABLE OVER 8 YEARS ASSUMING THE
THE CASH FLOWS EQUAL TO THE PW VALUE OF THE ORIGINAL CASH
FLOW SERIES
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
A B C D E
PROBLEM 2.98
i= 12.00%
n= 25
A1=$1,000.00
G= $200.00
aP= $18,464.07 USING INTEREST TABLES THIS IS
=$1,000*(P|A 12%,25)+$200*(P|G 12%,25)
F= $313,890.26 USING INTEREST TABLES THIS IS
=$18,464.07*(F|P 12%,25)
F= $313,890.35 USING EXCEL’S FV FUNCTION THIS IS
=FV(12%,25,,-18464.07)
bA= $2,354.17 USING INTEREST TABLES THIS IS
=$1,000+$200*(A|G 12%,25)
A= $2,354.18 USING INTEREST TABLES THIS IS
=$313,890.35*(A|F 12%,25)
A= $2,354.17 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(12%,25,,-313890.35)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
27
28
29
30
31
32
USING INTEREST TABLES THIS IS
USING INTEREST TABLES THIS IS
41
A B C D E F G
PROBLEM 2.99
aP= INR 20,000,000.00
i= 5.00%
n= 10
A1=INR 2,000,000.00
X= G
P= INR 20,000,000.00
USING INTEREST TABLES THIS IS
INR 20,000,000.00 =INR 2,000,000*(P|A 5%,10)+G*(P|G 5%,10)
INR 20,000,000.00 =INR 2,000,000*7.72173+G*31.65205
INR 20,000,000.00 =INR 15,443,460+G*31.65205
G=X= INR 143,957.18 =(20000000-15443460)/31.65205
7 INR 2,863,741.30
8 INR 3,007,698.18
9 INR 3,151,655.06
10 INR 3,295,611.94
P= INR 20,000,000.00 <–SOLVER TARGET CELL
=NPV(5%,CF YR 6:CF YR 10)
SEE BELOW FOR TABULATED CASH FLOWS TO CHECK ANSWER.
NOTE THAT THE VALUE OF G=X FOUND USING SOLVER BELOW IS
ESSENTIALLY THE SAME VALUE FOUND ABOVE.
1
2
3
4
5
6
7
8
16
17
18
19
20
25
26
27
28
29
30
40
41
42
A B C D E F G
PROBLEM 2.100
i= 8.00%
n= 6
P= $140,000.00
A1=$11,000.00
X= G
G=X $8,471.54 <–SOLVER CHANGED CELL
3$27,943.08
4$36,414.62
5$44,886.16
6$53,357.69
P= $140,000.00 <–SOLVER TARGET CELL
PW= $140,000.00 USING INTEREST TABLES THIS IS
$140,000.00
=A1*(P|A 8%,6)-$7,500*(P|G 8%,6)
THIS MAY ALSO BE APPROACHED USING SOLVER. BEGIN BY
SETTING THE VALUE OF “G” TO ANY VALUE. IT WILL LATER BE
CHANGED TO THE CORRECT VALUE. THEN USE THE EXCEL CELL
WITH THAT VALUE OF G TO DEVELOP A CASH FLOW SERIES. WRITE
AN EQUATION FOR THE PW OF THE SERIES. THEN, USE SOLVER TO
TARGET THE PW TO THE COST OF THE MACHINERY BY CHANGING
THE VALUE OF G.
THE PAYMENTS MUST HAVE A PW EQUAL TO THE COST OF THE
MACHINERY. SINCE THE GRADIENT IS KNOWN, SOLVE FOR THE
FIRST PAYMENT A1.
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
A B C D E F G
A1=$47,356.75 <–SOLVER CHANGED CELL
EOY CF
0
1$47,356.75
2$39,856.75
3$32,356.75
4$24,856.75
5$17,356.75
6$9,856.75
P= $140,000.00 <–SOLVER TARGET CELL
=NPV(8%,CF YR 1:CF YR 6)+0
THIS MAY ALSO BE APPROACHED USING SOLVER. BEGIN BY
SETTING THE VALUE OF “A1” TO ANY VALUE. IT WILL LATER BE
CHANGED TO THE CORRECT VALUE. THEN USE THE EXCEL CELL
WITH THAT VALUE OF A1 TO DEVELOP A CASH FLOW SERIES. WRITE
AN EQUATION FOR THE PW OF THE SERIES. THEN, USE SOLVER TO
TARGET THE PW TO THE COST OF THE MACHINERY BY CHANGING
THE VALUE OF A1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
A B C D E F
PROBLEM 2.101
EOY CF1 CF2 CF3 CF
0 -$7,000.00 -$7,000.00
1$0.00
2$0.00
3$0.00
4$0.00
5$1,850.00 $0.00 $1,850.00
6$1,850.00 $200.00 $2,050.00
7$1,850.00 $400.00 $2,250.00
8$1,850.00 $600.00 $2,450.00
9$1,850.00 $800.00 $2,650.00
10 $1,850.00 $1,000.00 $2,850.00
1
2
3
4
5
6
7
8
17
18
19
20
30
31
32
33
CHANGING THE VALUE OF X.
41
42
43
A B C D E F G H I
G2=-$1,000.00
n2=20
NOW, LET X EQUAL THE FIRST OF 40 AMOUNTS IN A GRADIENT SERIES
WITH GRADIENT G=$500. SOLVE FOR THE PW OF THIS GRADIENT
PROBLEM 2.102
i= 8.00%
n= 40
A11=$2,000.00
G1=$1,000.00
n1=20
A12=$21,000.00
G= $500.00
X= $4,622.20 <–SOLVER CHANGED CELL
**********
BEGIN BY SETTING A CELL TO HOUSE THE VALUE OF THE FIRST
AMOUNT, X. SELECT ANY AMOUNT FOR THE VALUE IN THE CELL. IT
SERIES.
BEGIN BY SETTING A CELL TO HOUSE THE VALUE OF THE FIRST
AMOUNT, X. SELECT ANY AMOUNT FOR THE VALUE IN THE CELL. IT
WILL LATER BE SET TO THE CORRECT VALUE.
SEE BELOW FOR TABULATED CASH FLOWS TO CHECK ANSWER. NOTE
THAT THE VALUE OF X FOUND USING SOLVER BELOW IS ESSENTIALLY
THE SAME VALUE FOUND ABOVE.
45
46
47
53
54
55
56
57
58
62
63
64
65
66
67
78
79
80
81
82
93
94
95
96
A B C D E F G H I
X= $4,622.18 <–SOLVER CHANGED CELL
1$2,000.00 $4,622.18
2$3,000.00 $5,122.18
3$4,000.00 $5,622.18
4$5,000.00 $6,122.18
5$6,000.00 $6,622.18
6$7,000.00 $7,122.18
10 $11,000.00 $9,122.18
11 $12,000.00 $9,622.18
12 $13,000.00 $10,122.18
13 $14,000.00 $10,622.18
14 $15,000.00 $11,122.18
15 $16,000.00 $11,622.18
26 $16,000.00 $17,122.18
27 $15,000.00 $17,622.18
28 $14,000.00 $18,122.18
29 $13,000.00 $18,622.18
30 $12,000.00 $19,122.18
P= $118,138.78 $118,138.78 <–SOLVER TARGET CELL
USING EXCEL’S NPV FUNCTION THIS IS
=NPV(8%,NEW CF YR 1: NEW CF YR 40)+CF YR 0
FIRST SOLVE FOR THE PW OF THE ORIGINAL GRADIENT SERIES. THEN,
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
A B C D E F G H I J K L M N
PROBLEM 2.103
EOY 0 1 2 3 4 5 6 7 8 9 10
CF $100 $80 $60 $40 $20 $0 -$20 -$40 -$60 -$80 -$100
i= 18.00%
n= 11
A1=100.00$
G= -$20.00
PW= 172.48$ USING INTEREST TABLES THIS IS
=($100*(P|A 18%,11)-$20*(P|G 18%,11))*(F|P18%,1)
PW= $172.48 USING EXCEL’S FV FUNCTION THIS IS
=FV(18%,1,,-NPV(18%,100,80,60,40,20,0,-20,-40,-60,-80,-100))
1
2
3
4
5
6
7
8
9
10
11
12
A B C D E F G
PROBLEM 2.104
TOTAL
EOY CF1 CF2 CF3 CF4 CF
0$800.00 $800.00
1$950.00 $0.00 $950.00
2$950.00 -$450.00 $500.00
3$950.00 -$900.00 $50.00
4$950.00 -$1,350.00 -$400.00
5 -$600.00 -$600.00
6 -$600.00 -$600.00
7 -$600.00 -$600.00
PROBLEM 2.105
i= 12.00%
n= 5
A|G= 1.77459 FROM 18% INTEREST TABLES
=(1/0.12)-(5/0.12)*(0.12/((1+0.12)^5-1))
P= $75,000
G= $3,000
USING INTEREST TABLES THIS IS
A1=$15,481.97 =$75,000(A|P 12%,5)-$3,000(A|G 12%,5)
=C9*0.27741-C10*C6
A5=$27,481.97 =C13+4*C10
USING EXCEL’S PMT AND NPV FUNCTIONS THIS IS
A1=$15,481.95 =-PMT(C4,5,C9-NPV(C4,0,C10,2*C10,3*C10,4*C10))
A5=$27,481.95 =C18+4*C10
PROBLEM 2.106
i= 12.00%
n= 5
A|G= 1.77459 FROM 18% INTEREST TABLES
=(1/0.12)-(5/0.12)*(0.12/((1+0.12)^5-1))
P= $75,000
G= ($3,000)
USING INTEREST TABLES THIS IS
A1=$26,129.53 =$75,000(A|P 12%,5)+$3,000(A|G 12%,5)
=C9*0.27741-C10*C6
A5=$14,129.53 =C13+4*C10
USING EXCEL’S PMT AND NPV FUNCTIONS THIS IS
A1=$26,129.51 =-PMT(C4,5,C9-NPV(C4,0,C10,2*C10,3*C10,4*C10))
A5=$14,129.51 =C18+4*C10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
A B C D E F G H
PROBLEM 2.107
i= 10.00%
n= 5
A1=$10,000.00
G= -$1,000.00
P= $31,046.10 USING INTEREST TABLES THIS IS
=$10,000*(P|A 10%,5)-$1,000*(P|G 10%,5)
AMT3= $41,322.36 USING INTEREST TABLES THIS IS
$31,046.10*(F|P 10%,3)
SEE BELOW FOR TABULATED CASH FLOWS TO CHECK ANSWER
EOY CF
0$0.00
1$10,000.00
2$9,000.00
3$8,000.00
4$7,000.00
5$6,000.00
AMT3= $41,322.31 USING EXCEL’S NPV AND FV FUNCTIONS THIS IS
=NPV(10%,7000,6000)+FV(10%,2,,-
10000)+FV(10%,1,,-9000)+8000
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
A B C D E F G
PROBLEM 2.108
a (F|G i%,n)=(P|G i%,n)*(F|P i%,n)
b (F|G i%,n)= ((1+i)^n-(1+n*i))/(i^2)
ci= 10.00%
n= 5
A1=$0.00
G= $1,000.00
USING THE METHOD OF PART a
(F|G i%,n)=(P|G i%,n)*(F|P i%,n)
(P|G 10%,5)= 6.8618
(F|P 10%,5)= 1.61051
F= $11,051.00 USING INTEREST TABLES THIS IS
$1,000*(P|G 10%,5)*(F|P 10%,5)
USING THE METHOD OF PART b
(F|G i%,n)= 11.05100 =((1+0.1)^5-(1+5*0.1))/(0.1^2)
F= $11,051.00
=$1,000.00*11.05100
PROBLEM 2.108
i= 8%
USING THE INTEREST TABLES THE WITHDRAWALS ARE:
BALANCE AT t=4
F = $1,000(F|P 8%,4) = $1,360.49
THUS $680.25 IS WITHDRAWN AT t=4
AT t=15 THE FUND BALANCE IS
F = $680.24(F|P 8%,11) + $2,000(F|A 8%,8)(F|P 8%,2)
= $680.24(2.33164) + $2000(10.63663)(1.16640)
= $26,399.21
THUS $26,399.21 IS WITHDRAWN AT t=15.
USING EXCEL’S FV FUNCTION, THE FIRST WITHDRAWALS IS
$680.24 =0.5*FV(C4,4,,-1000)
USING EXCEL’S FV FUNCTION, THE SECOND WITHDRAWAL IS
$26,399.21 =FV(C4,11,,-C18)+FV(C4,2,,FV(C4,8,2000))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
A B C D E F G H I
PROBLEM 2.110
i= 10.00%
n= 4
A1=$2,000.00
G= $500.00
aP= $8,528.80 USING INTEREST TABLES THIS IS
=$2,000*(P|A 10%,4)+$500*(P|G 10%,4)
P= $8,528.79 USING EXCEL’S NPV FUNCTION
=NPV(10%,2000,2500,3000,3500)
bA= $2,690.58 USING INTEREST TABLES THIS IS
=$8,528.80*(A|P 10%,4)
A= $2,690.59 USING INTEREST TABLES THIS IS
=$2,000+$500*(A|G 10%,4)
A= $2,690.58 USING EXCEL’S PMT FUNCTION
=PMT(10%,4,-8528.79)
SEE BELOW FOR TABULATED CASH FLOWS TO CHECK ANSWER
EOY CF
0 $0.00
1 $2,000.00
2 $2,500.00
3 $3,000.00
4 $3,500.00
P= $8,528.79 =NPV(10%,2000,2500,3000,3500)+0
A= $2,690.58 =PMT(10%,4,-8528.79)
PROBLEM 2.111
i= 18.00%
j= 10.00%
n= 5
P= $10,000
P|A1=3.70039 USING COMPOUND INTEREST FORMULAS THIS IS
=(1-((1+0.10)/(1+0.18))^5)/(0.18-0.10)
A1|P= 0.27024175 =(0.18-0.1)/(1-((1+0.1)/(1+0.18))^5)
A1 = $10,000(A1|P 18%,10%,5)
A1 = $2,702.42 =C7*C10
USING EXCEL’S NPV FUNCTION AND SOLVER TOOL
A1 = $2,702.42 CHANGE CELL
$10,000.00 TARGET CEL
PROBLEM 2.112
i= 18.00%
j= -10.00%
n= 5
P= $10,000
P|A1=2.64961 USING COMPOUND INTEREST FORMULAS THIS IS
=(1-((1-0.10)/(1+0.18))^5)/(0.18+0.10)
A1|P= 0.37741374 =(0.18+0.1)/(1-((1-0.10)/(1+0.18))^5)
A1 = $10,000(A1|P 18%,-10%,5)
A1 = $3,774.14 =C7*C10
USING EXCEL’S NPV FUNCTION AND SOLVER TOOL
A1 = $3,774.14 CHANGE CELL
$10,000.00 TARGET CEL