PROBLEM 2.113
i= 12.00%
j= 12.00% NOTICE i=j
n= 5
P= $75,000
P|A1=4.46429 USING COMPOUND INTEREST FORMULAS THIS IS
=5/(1+0.12)
A1|P= 0.2240000 =(1+0.12)/5
A1 = $75,000(A1|P 18%,10%,5)
A1 = $16,800.00 =C7*C10
USING EXCEL’S NPV FUNCTION AND SOLVER TOOL
A1 = $16,800.00 CHANGE CELL
$75,000.00 TARGET CEL
PROBLEM 2.114
i= 12.00%
j= -20.00%
n= 5
P= $75,000
P|A1=2.54395 USING COMPOUND INTEREST FORMULAS THIS IS
=(1-((1-0.20)/(1+0.12))^5)/(0.12+0.20)
A1|P= 0.39308873 =(0.12+0.20)/(1-((1-0.20)/(1+0.12))^5)
A1 = $75,000(A1|P 18%,-20%,5)
A1 = $29,481.65 =C7*C10
USING EXCEL’S NPV FUNCTION AND SOLVER TOOL
A1 = $29,481.65 CHANGE CELL
$75,000.00 TARGET CEL
1
2
3
4
5
6
7
8
17
18
19
20
21
22
A1=$10,000
A B C D E F G H
PROBLEM 2.115
FIRST, FIND THE PW OF THE GIVEN CF PROFILE
i= 6.00%
n= 8
INIT INV= $45,000.00
A1=$12,000.00
AT THIS POINT, WE KNOW THE PW OF THE CF PROFILE.
i= 6.00%
n= 8
TO SOLVE FOR THE VALUE OF j, IT IS MUCH EASIER TO USE SOLVER
RATHER THAN TO TRY INTERPOLATION OR AN ANALYTICAL SOLUTION
48
49
50
51
A B C D E F G H
PW= $9,675.95 <–SOLVER TARGET CELL
USING EXCEL’S NPV FUNCTION THIS IS
=NPV(6%,CF YR 1:CF YR 8)-45000
PROBLEM 2.116
i= 5.00%
n= 30
A1=$7,500.00
j= 5.00%
F= $926,130.53 USING INTEREST TABLES THIS IS
=$7,500*(F|A1 5%,5%,30)
**********
SEE BELOW FOR TABULATED CASH FLOWS TO CHECK ANSWER
EOY
CF
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
27
28
29
30
31
32
42
43
44
45
46
47
A B C D E F G
PROBLEM 2.117
i= 6.00%
n= 30
A1=$7,500.00
j= 6.00%
F= $1,219,137.30 USING INTEREST TABLES THIS IS
=$7,500*(F|A1 6%,6%,30)
**********
SEE BELOW FOR TABULATED CASH FLOWS TO CHECK ANSWER
EOY
CF
0
1$7,500.00 =$7,500*(1+0.06)^(1-1)
2$7,950.00 =$7,500*(1+0.06)^(2-1)
12 $14,237.24
13 $15,091.47
14 $15,996.96
15 $16,956.78
16 $17,974.19
17 $19,052.64
27 $34,120.37
28 $36,167.59
29 $38,337.65
30 $40,637.91 =$7,500*(1+0.06)^(30-1)
F= $1,219,137.28 USING EXCEL’S FV AND NPV FUNCTIONS THIS IS
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
A B C D E F G H I J
A|A1=1.22411 NOT TABULATED IN APPENDIX A
PROBLEM 2.118
P|A1FACTOR
i= 7.00%
7% FOR EXAMPLE CALC
j= 5.00%
5% FOR EXAMPLE CALC
n= 10
FOR EXAMPLE CALC
P|A1=8.59763
RIC SERIES-PRESENT WORTH TABLE
F|A1FACTOR
i= 7.00% USING 7% FOR EXAMPLE CALC
j= 5.00% USING 5% FOR EXAMPLE CALC
n= 10 USING 10 FOR EXAMPLE CALC
F|A1=16.91284 CHECKS WITH GEOMETRIC SERIES-FUTURE WORTH TABLE
A|A1FACTOR
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
PROBLEM 2.119
i= 7.00% COMPOUNDED ANNUALLY
j= -40.00% COMPOUNDED ANNUALLY
n= 4
A1=$60,000.00
PW= $115,038.00 USING INTEREST TABLES THIS IS
=$60,000*(P|A1 7%,-40%,4)
PW= $115,037.72 USING FORMULAS THIS IS
=60000*((1-(1-0.4)^4*(1+0.07)^-4)/(0.07-(-0.4)))
EOY CF
0$0.00
1$60,000.00
2$36,000.00
3$21,600.00
4$12,960.00
PW= $115,037.72 USING EXCEL’S NPV FUNCTION THIS IS
=NPV(7%,60000,36000,21600,12960)+0
SEE BELOW FOR TABULATED CASH FLOWS TO CONFIRM THE ANSWER.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
A B C D E F G H
PROBLEM 2.120
i= 10.00%
j= 15.00%
n= 4
A1=$2,000.00
a PW= $7,783.80 USING INTEREST TABLES THIS IS
=$2,000*(P|A1 10%,15%,4)
PW= $7,783.79 USING EXCEL’S NPV FUNCTION THIS IS
=NPV(10%,2000,2000*1.15,2000*1.15^2,2000*1.15^3)
bA= $2,455.56 USING INTEREST TABLES THIS IS
=$7,783.80*(A|P 10%,4)
A= $2,455.56 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(10%,4,-7783.79)
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 F
PROBLEM 2.121
P= -$90,000.00
i= 5.00%
j= -10.00%
n= 5
A1=$30,000.00
PW= $17,467.13 USING COMPOUND INTEREST FORMULAS THIS IS
=30000*((1-(1-0.1)^5*(1+0.05)^-5)/(0.05+0.1))-90000
PW= $17,467.20 USING INTEREST TABLES THIS IS
=$30,000*(P|A1 5%,-10%,5)-$90,000
EOY CF
0 -$90,000.00
1$30,000.00
2$27,000.00
3$24,300.00
4$21,870.00
5$19,683.00
PW= $17,467.13 USING EXCEL’S NPV FUNCTION THIS IS
=NPV(5%,CF YR 1:CF YR 5)-90000
SEE BELOW FOR TABULATED CASH FLOWS TO CHECK ANSWER.
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 F G H
PROBLEM 2.122
i= 12.00%
j= 15.00%
n= 6
A1=$50,000.00
PW= $286,447.62 USING COMPOUND INTEREST FORMULAS THIS IS
=50000*((1-(1+0.15)^6*(1+0.12)^-6)/(0.12-0.15))
PW= $286,447.62 USING ELECTRONIC INTEREST TABLES THIS IS
=$50,000*(P|A1 12%,15%,6)
EOY CF
0
1 $50,000.00
2 $57,500.00
3 $66,125.00
4 $76,043.75
5 $87,450.31
6 $100,567.86
PW= $286,447.62 USING EXCEL’S NPV FUNCTION THIS IS
=NPV(12%,CF YR 1:CF YR 6)+0
SEE BELOW FOR TABULATED CASH FLOWS TO CONFIRM THE ANSWER.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
30
31
32
33
34
35
A B C D E F G H
PROBLEM 2.123
i= 5.00%
j= 10.00%
n= 18
A1=$2,000.00
PW= $52,410.42 USING COMPOUND INTEREST FORMULA THIS IS
=$2,000*(P|A1 5%,10%,18)
PW= $52,410.42 USING INTEREST TABLES THIS IS
=$2,000*(P|A1 5%,10%,18)
FW= $126,131.92 USING EXCEL’S FV FUNCTION THIS IS
=FV(5%,18,,-52410.42)
EOY CF
0
10 $4,715.90
11 $5,187.48
12 $5,706.23
13 $6,276.86
14 $6,904.54
15 $7,595.00
SEE BELOW FOR TABULATED CASH FLOWS TO CONFIRM THE ANSWER.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
28
29
30
31
32
33
A B C D E
PROBLEM 2.124
i= 15.00%
j= 6.00%
n= 20
A1=$1,000.00
PW= $8,933.81 TABLES FROM APPENDIX A
=$1,000*(P|A1 15%,6%,20)
PW= $8,933.81 TABLES FROM PROBLEM 2.123
=$1,000*(P|A1 15%,6%,20)
USING TABULATED CASH FLOWS
EOY CF
0$0.00
1$1,000.00
2$1,060.00
3$1,123.60
4$1,191.02
11 $1,790.85
12 $1,898.30
13 $2,012.20
14 $2,132.93
15 $2,260.90
16 $2,396.56
1
2
3
4
5
6
7
8
9
10
11
12
A B C D E F G H
PROBLEM 2.125
i= 10.00%
j= 5.00%
n= 6
A1=$9,000.00
PW= $43,839.18 USING INTEREST TABLES THIS IS
=$9,000*(P|A1 10%,5%,6)
PW= $43,839.17 USING EXCEL’S NPV FUNCTION THIS IS
=NPV(10%,9000,9000*1.05,9000*1.05^2,9000*1.05^3
,9000*1.05^4,9000*1.05^5)
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.126
i= 15.00%
j= 10.00%
n= 5
P= $50,000.00
P= $50,000.00 USING INTEREST TABLES THIS IS
=A1*(P|A1 15%,10%,5)
A1=$12,544.41 =50000/3.98584
a SMALLEST PAYMENT $0.00
b LARGEST PAYMENT $18,366.27 =12544.41*(1+0.1)^4
A1=$12,544.41 SOLVER CHANGED CELL
EOY CF
0
BEGIN BY DEDICATING A CELL FOR A1 AND LET IT TAKE ON ANY
VALUE AS IF IT WERE CORRECT. IT WILL BE CHANGED TO THE
CORRECT VALUE WHEN SOLVER IS USED.
SEE BELOW FOR TABULATED CASH FLOWS TO CHECK ANSWER.
WILL USE SOLVER TO FIND VALUE OF A1.
PROBLEM 2.127
i= 5.00%
j= 10.00%
n= 5
A1=$1,000.00
P= $5,237.54 USING ELECTRONIC INTEREST TABLES THIS IS
=$1,000*(P|A1 5%,12%,5)
=$1,000*5.237535
P= $5,237.53 USING EXCEL’S NPV FUNCTION THIS IS
=1000*NPV(5%,1,1.1,1.1^2,1.1^3,1.1^4)
1
2
3
4
5
6
7
8
9
10
11
12
A B C D E F G H
PROBLEM 2.128
i= 5.00%
j= 15.00%
n= 7
A1=$4,000.00
F= $50,116.76 USING INTEREST TABLES THIS IS
=$4,000*(F|A1 5%,15%,7)
F= $50,116.78 USING EXCEL’S FV AND NPV FUNCTIONS THIS IS
=FV(5%,7,,-
NPV(5%,4000,4000*1.15,4000*1.15^2,4000*1.15^3,400
0*1.15^4,4000*1.15^5,4000*1.15^6))
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
A B C D E F G H
PROBLEM 2.129
i= 8.00%
j= 5.00%
n= 10
SALARY $70,000.00
% DEPOSITED 10.00%
A1=$7,000.00
FW= $123,673.76 USING INTEREST TABLES THIS IS
=$7,000*(F|A1 8%,5%,10)
FW= $123,673.76 TABLES FROM PROBLEM 2.123
=$7,000*(F|A1 8%,5%,10)
USING TABULATED CASH FLOWS
EOY CF
0$0.00
1$7,000.00
2$7,350.00
3$7,717.50
4$8,103.38
5$8,508.54
6$8,933.97
7$9,380.67
8$9,849.70
9$10,342.19
10 $10,859.30
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
32
35
36
A B C D E F
PROBLEM 2.130
i= 11.00%
a LABOR MATERIAL OVERHEAD TOTAL
j= -5.00% 4.00% 8.00%
A1=$2,000,000.00 $3,000,000.00 $1,600,000.00
EOY
0$0.00 $0.00 $0.00 $0.00
1$2,000,000.00 $3,000,000.00 $1,600,000.00 $6,600,000.00
2$1,900,000.00 $3,120,000.00 $1,728,000.00 $6,748,000.00
3$1,805,000.00 $3,244,800.00 $1,866,240.00 $6,916,040.00
4$1,714,750.00 $3,374,592.00 $2,015,539.20 $7,104,881.20
5$1,629,012.50 $3,509,575.68 $2,176,782.34 $7,315,370.52
6$1,547,561.88 $3,649,958.71 $2,350,924.92 $7,548,445.51
7$1,470,183.78 $3,795,957.06 $2,538,998.92 $7,805,139.75
bP= $8,295,499.60 $15,693,016.73 $9,307,846.73 $33,296,363.05
USING EXCEL’S NPV FUNCTION, EACH COLUMN IS
=NPV(11%,CF YR 1:CF YR 7)+0
P= $8,295,500.00 $15,693,030.00 $9,307,840.00 $33,296,370.00
USING INTEREST TABLES FROM PROBLEM 2.123
=$2,000,000.00*(P|A1 11%,-5%,7)
=$3,000,000.00*(P|A1 11%,4%,7)
=$1,600,000.00*(P|A1 11%,8%,7)
cA= $7,065,996.66 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(11%,7,-33296363.05)
NOTE! TABLES DO NOT SUPPORT THIS LEVEL OF PRECISION
IN THIS PROBLEM, IT IS MUCH EASIER TO USE A SPREADSHEET
AND TO KEEP LABOR, MATERIAL, AND OVERHEAD SEPARATE UNTIL
COMBINED.
PROBLEM 2.131
i= 10.00%
a LABOR MATERIAL ENERGY TOTAL
j= -5.00% 6.00% 3.00%
A1=$3,000,000.00 $2,000,000.00 $1,500,000.00
EOY
1 $3,000,000.00 $2,000,000.00 $1,500,000.00 $6,500,000.00
2 $2,850,000.00 $2,120,000.00 $1,545,000.00 $6,515,000.00
3 $2,707,500.00 $2,247,200.00 $1,591,350.00 $6,546,050.00
4 $2,572,125.00 $2,382,032.00 $1,639,090.50 $6,593,247.50
5 $2,443,518.75 $2,524,953.92 $1,688,263.22 $6,656,735.89
b
P= $24,848,118.69
USING EXCEL’S NPV FUNCTION, PW FOR TOTAL IS
=NPV(10%,CF YR 1:CF YR 7)
=NPV(10%,f11:f15)
EUAC= $6,554,871.11
USING EXCEL’S PMT FUNCTION, EUAC FOR TOTAL IS
=PMT(C5,5,-F18)
=$1,600,000.00*(P|A1 11%,8%,7)
EUAC= $6,554,872.00
USING ELECTRONIC INTEREST TABLES THE EUAC IS
=$1,000,000(3*A|A1 (10%,-5%,5)+2(A|A1(10%,6%,5)+1.5*A|A1(10%,3%,5))
=$1000000*(3*0.913694+2*1.114988+1.5*1.055876)
NOTE! TABLES IN BOOK DO NOT SUPPORT THIS LEVEL OF PRECISION
IN THIS PROBLEM, IT IS MUCH EASIER TO USE A SPREADSHEET AND TO
KEEP LABOR, MATERIAL, AND ENERGY SEPARATE UNTIL COMBINED.