1
2
3
4
5
6
7
8
9
10
11
A B C D E F
PROBLEM 2.132
i= 5.00%
n= 35
A1=$7,500.00
j= 8.00%
F= $2,317,332.23 USING ELECTRONIC INTEREST TABLES THIS IS
=$7,500*(F|A1 5%,8%,35)
**********
PROBLEM 2.133
FIRST, REVIEW THE ORIGINAL PROBLEM 2.132
i= 5.00%
n= 35
A1=$7,500.00
j= 8.00%
F= $2,317,332.23 USING ELECTRONIC INTEREST TABLES THIS IS
=$7,500*(F|A1 5%,8%,35)
A1=$20,080.69 =2317332.23/115.401008511
A1=$20,080.17 SOLVER CHANGED CELL
52 27 $55,472.65 52 27 $68,793.51
53 28 $59,910.46 53 28 $74,297.00
54 29 $64,703.30 54 29 $80,240.76
55 30 $69,879.56 55 30 $86,660.02
56 31 $75,469.93 56 31 $93,592.82
57 32 $81,507.52 57 32 $101,080.24
58 33 $88,028.12 58 33 $109,166.66
59 34 $95,070.37 59 34 $117,899.99
60 35 $102,676.00 =$7,500*(1+0.08)^(35-1) 60 35 $127,331.99
F= $2,317,332.23 F= $2,317,332.23
SOLVER
TARGET CELL
USING EXCEL’S FV AND NPV
FUNCTIONS THIS IS
=FV(8%,35,,-NPV(8%,CF YR
1:CF YR 35)+0)
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
33
34
35
27 2 $8,100.00 27 2 $25,656.82
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
A B C D E F G H I J K L M N O
PROBLEM 2.134
FIRST, REVIEW THE ORIGINAL PROBLEM 2.132
i= 5.00%
j= 8.00%
n= 35
A1=$7,500.00
F= $2,317,332.23 USING ELECTRONIC INTEREST TABLES THIS IS
=$7,500*(F|A1 5%,8%,35)
F= $2,317,332.23 USING COMPOUND INTEREST FORMULAS THIS IS
=7500*(((1+0.05)^35-(1+0.08)^35))/(0.05-0.08)
A= $25,656.82 USING COMPOUND INTEREST FORMULAS THIS IS
=2317332.23*(0.05/((1+0.05)^35-1))
=$2,317,332.23*(A|F 5%,35)
A= $25,656.82 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(5%,35,,-2317332.23)
A= $25,656.82
ORIGINAL CHECK NEW
AGE
EOY CF AGE EOY CF
25 025 0
26 1 $7,500.00 26 1 $25,656.82
28 3 $8,748.00 28 3 $25,656.82
29 4 $9,447.84 29 4 $25,656.82
30 5 $10,203.67 30 5 $25,656.82
31 6 $11,019.96 31 6 $25,656.82
32 7 $11,901.56 32 7 $25,656.82
33 8 $12,853.68 33 8 $25,656.82
34 9 $13,881.98 34 9 $25,656.82
35 10 $14,992.53 35 10 $25,656.82
36 11 $16,191.94 36 11 $25,656.82
37 12 $17,487.29 37 12 $25,656.82
38 13 $18,886.28 38 13 $25,656.82
39 14 $20,397.18 39 14 $25,656.82
40 15 $22,028.95 40 15 $25,656.82
41 16 $23,791.27 41 16 $25,656.82
42 17 $25,694.57 42 17 $25,656.82 EXCEL
43 18 $27,750.14 43 18 $25,656.82
44 19 $29,970.15 44 19 $25,656.82
45 20 $32,367.76 45 20 $25,656.82
46 21 $34,957.18 46 21 $25,656.82
47 22 $37,753.75 47 22 $25,656.82
48 23 $40,774.05 48 23 $25,656.82
49 24 $44,035.98 49 24 $25,656.82
50 25 $47,558.86 50 25 $25,656.82
51 26 $51,363.56 51 26 $25,656.82
52 27 $55,472.65 52 27 $25,656.82
53 28 $59,910.46 53 28 $25,656.82
54 29 $64,703.30 54 29 $25,656.82
55 30 $69,879.56 55 30 $25,656.82
56 31 $75,469.93 56 31 $25,656.82
57 32 $81,507.52 57 32 $25,656.82
58 33 $88,028.12 58 33 $25,656.82
59 34 $95,070.37 59 34 $25,656.82
60 35 $102,676.00 60 35 $25,656.82
F= $2,317,332.23 F= $2,317,332.23
SOLVER
TARGET CELL
NOW, SOLVE FOR THE UNIFORM ANNUAL INVESTMENT TO
ACHIEVE THE SAME VALUE OF FW AT EOY 60. LET “A” BE THE
VALUE OF THE ANNUAL INVESTMENT.
SEE BELOW FOR TABULATED CASH FLOWS TO CHECK
ANSWER. NOTE THAT THE VALUE OF “A” FOUND USING
SOLVER BELOW IS ESSENTIALLY THE SAME VALUE FOUND
ABOVE.
USING EXCEL’S FV AND NPV
=FV(8%,35,,-NPV(8%,CF YR
1:CF YR 35)+CF YR 0)
USING EXCEL’S FV
=FV(8%,35,,-
NPV(8%,CF YR 1:CF
YR 35)+CF YR 0)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
A B C D E
PROBLEM 2.135
i= 6.00%
j= 4.00%
n= 5
A1=$15,000.00
FW=
$91,179.45 TABLES FROM APPENDIX A
=$15,000*(F|A1 6%,4%,5)
USING TABULATED CASH FLOWS
EOY
CF
0$0.00
1$15,000.00
2$15,600.00
3$16,224.00
4$16,872.96
5$17,547.88
FW=
$91,179.51 USING EXCEL’S FV AND NPV FUNCTIONS THIS IS
=FV(6%,5,,-NPV(6%,CF YR 1:CF YR 5)+0)
1
2
3
4
5
6
7
8
19
20
21
22
23
34
35
36
37
38
49
50
51
52
53
A B C D E F
PROBLEM 2.136
P= $12,000.00
i= 0.75% PER MONTH
A= $400.00 PER MONTH
n= 34.1129207 MONTHS USE 35 MONTHS
USING EXCEL’S NPER FUNCTION THIS IS
EOY CF PW
0
11 $400.00 $4,208.27
12 $400.00 $4,573.97
13 $400.00 $4,936.94
14 $400.00 $5,297.21
15 $400.00 $5,654.80
26 $400.00 $9,416.88
27 $400.00 $9,743.80
28 $400.00 $10,068.28
29 $400.00 $10,390.36
30 $400.00 $10,710.03
SEE BELOW FOR TABULATED CASH FLOWS TO CHECK ANSWER.
58
59
60
61
62
63
A B C D E F
35 $400.00 $12,273.06 FIRST PW OF >= $12,000
36 $400.00 $12,578.72
37 $400.00 $12,882.11
38 $400.00 $13,183.23
39 $400.00 $13,482.12
40 $400.00 $13,778.78
1
2
3
4
5
6
7
8
9
10
11
12
13
A B C D E F
PROBLEM 2.137
r= 6.00% PER YEAR
m= 12
i= 0.50% PER MONTH
n= 60 MONTHS
P= $50,000.00
A= $966.50 USING INTEREST TABLES THIS IS
=$50,000*(A|P 0.50%,60)
A= $966.64 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(0.5%,60,-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
28
29
30
31
A B C D E F G H
PROBLEM 2.138
ai= 0.50% PER MONTH
n= 30 MONTHLY PAYMENTS
P= $500.00
A= $17.99 USING INTEREST TABLES THIS IS
=$500*(A|P 0.50%,30)
A= $17.99 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(0.50%,30,-500)
bi= 0.50% PER MONTH
n= 30 MONTHLY PAYMENTS
P= $500.00
DELAY= 12 MONTHS
AMT12MO=
$530.84 USING INTEREST TABLES THIS IS
=$500.00*(F|P 0.5%,12)
A= $19.10 USING INTEREST TABLES THIS IS
=$530.84*(A|P 0.50%,30)
AMT12MO=
$530.84 USING EXCEL’S FV FUNCTION THIS IS
=FV(0.5%,12,,-C16)
A= $19.10 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(0.5%,30,-530.84)
FIRST, DETERMINE THE FUTURE WORTH OF THE ORIGINAL $500 AFTER
12 MONTHS. THEN, DETERMINE THE MONTHLY PAYMENT
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
PROBLEM 2.139
BUILDING THE ACCOUNT
r= 8.000% COMPOUNDED MONTHLY
m= 12 MONTHS PER YEAR
YRS= 20
A= $400.00 PER MONTH
DETERMINE AMOUNT IN ACCOUNT AT END OF 20 YEARS, END OF MONTH 240
F(240)= $235,608.00 USING INTEREST TABLES THIS IS
$400*(F|A 8/12%,240)
F(240)= $235,608.17 USING EXCEL’S FV FUNCTION THIS IS
=FV(0.08/12,12*20,-400)
DETERMINE AMOUNT IN ACCOUNT AT END OF MONTH 251,
ONE MONTH BEFORE 1ST WITHDRAWAL AT END OF MONTH 252
1ST WDL 252 MONTH 252 IS FIRST WITHDRAWAL
r= 8.000% COMPOUNDED MONTHLY
m= 12 MONTHS PER YEAR
YRS= 15
F(251)= $253,474.15 USING INTEREST TABLES THIS IS
=$235608.00*(F|P 8/12%,11)
F(251)= $253,473.70 USING EXCEL’S FV FUNCTION THIS IS
=FV(0.08/12,11,,-235608.17)
aA= $2,423.21 USING INTEREST TABLES THIS IS
=$253,474.15*(A|P 8/12%,12*15)
A= $2,422.33 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(0.08/12,12*15,-253473.7)
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
A B C D E F G
PROBLEM 2.140
a BUILDING THE ACCOUNT FOR 30 YEARS
r= 8.000%
m= 12 MONTHS PER YEAR
YRS= 30
A= $300.00 PER MONTH
AMT360= $447,120.00 USING INTEREST TABLES THIS IS
=$300*(F|A 8/12%,360)
AMT360= $447,107.83 USING EXCEL’S FV FUNCTION THIS IS
=FV(8%/12,12*30,-300)
b LETTING THE ACCOUNT BUILD FOR 7 MORE QUARTERS
r= 12.000%
m= 4 QTRS/YEAR
YRS= 15
AMT7QTR=
$549,899.47 USING INTEREST TABLES THIS IS
=$447,120*(F|P 3%,7)
AMT7QTR=
$549,886.24 USING EXCEL’S FV FUNCTION THIS IS
=FV(12%/4,7,,-C13)
c WITHDRAWING FROM THE ACCOUNT
STARTING 2 YEARS AFTER LAST DEPOSIT
A= $19,867.87 USING INTEREST TABLES THIS IS
=$549,899.47*(A|P 3%,60)
A= $19,869.02 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(12%/4,4*15,-549886.24)
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
A B C D E F G H
PROBLEM 2.141
PLAN 1 PLAN 2
r= 7.000% r= 6.625%
m= 12 m= 12
YRS= 30 YRS= 15
P= $600,000.00 P= $600,000.00
aA= $3,990.00 USING INTEREST TABLES THIS IS
=$600,000*(A|P 7/12%,360)
A= $3,991.81 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(7%/12,12*30,-600000)
bA= $5,268.00 USING INTEREST TABLES THIS IS
=$600,000*(A|P 6.625/12%,180)
A= $5,267.96 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(6.625%/12,12*15,-600000)
c
ieff=7.229% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.07/12)^12-1
ieff=7.229% USING EXCEL’S EFFECT FUNCTION
=EFFECT(7%,12)
d
ieff=6.830% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.06625/12)^12-1
ieff=6.830% USING EXCEL’S EFFECT FUNCTION
=EFFECT(6.625%,12)
e INT PAID= $837,053.39 =3991.81*12*30-600000
f INT PAID= $348,233.27 =5267.96*12*15-600000
PROBLEM 2.142
r= 15.000%
m= 4
YRS= 3
F= $1,000.00
A= $7,500.00
P= $8,473.13 USING COMPOUND INTEREST FORMULAS THIS IS
=1000*((1+0.15/4)^4-1)/((0.15/4)*(1+0.15/4)^4)+7500*(1+0.15/4)^(-4*3)
P= $8,473.13 USING ELECTRONIC INTEREST TABLES THIS IS
= $1,000(P|A,3.75%,4) + $7,500(F|P,3.75%,12)
= $1,000*3.65138412713759 + $7,500*0.642898978022477
P= $8,473.13 USING EXCEL’S PV FUNCTION THIS IS
=PV(0.15/4,4,-1000)+PV(0.15/4,12,,-7500)
USING EXCEL’S PV FUNCTION THIS IS
EOQ CASH FLOW
1$1,000.00
2$1,000.00
3$1,000.00
4$1,000.00
5$0.00
6$0.00
7$0.00
8$0.00
9$0.00
10 $0.00
11 $0.00
12 $7,500.00
P= $8,473.13 =NPV(C3/C4,C20:C31)
1
2
3
4
5
6
7
8
9
10
11
12
23
24
25
26
27
35
36
37
38
39
40
48
49
50
51
52
53
A B C D E F G H
PROBLEM 2.143
a ANNUALLY
r= 7.000%
m= 1
YRS= 5
P= $10,000.00
F= $14,025.52 USING COMPOUND INTEREST FORMULAS THIS IS
=10000*(1+0.07/1)^(1*5)
F= $14,025.50 USING INTEREST TABLES THIS IS
=$10,000*(F|P 7%,5)
=10000*(1+0.07/4)^(4*5)
F= $14,147.80 USING INTEREST TABLES THIS IS
=$10,000*(F|P 1.75%,20)
F= $14,147.78 USING EXCEL’S FV FUNCTION THIS IS
=FV(0.07/4,4*5,,-10000)
F= $14,176.25 USING COMPOUND INTEREST FORMULAS THIS IS
=10000*(1+0.07/12)^(12*5)
F= $14,176.25 USING ELECTRONIC INTEREST TABLES THIS IS
=$10,000*(F|P 7%/12,60)
F= $14,176.25 USING EXCEL’S FV FUNCTION THIS IS
=FV(0.07/12,12*5,,-10000)
F= $14,190.20 USING COMPOUND INTEREST FORMULAS THIS IS
=10000*(1+0.07/365)^(365*5)
F= $14,190.20 USING ELECTRONIC INTEREST TABLES THIS IS
=$10,000*(F|P 7%/365,1825)
F= $14,190.20 USING EXCEL’S FV FUNCTION THIS IS
=FV(0.07/365,365*5,,-10000)
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 I
PROBLEM 2.144
r= 5.000%
m= 4
YRS= 4
F= $10,000.00
P= $8,197.50 USING INTEREST TABLES THIS IS
=$10,000*(P|F 1.25%,16)
P= $8,197.46 USING EXCEL’S PV FUNCTION THIS IS
=PV(0.05/4,4*4,,-10000)
OR, CAN USE THE EFFECTIVE RATE AS FOLLOWS
ieff=5.09453% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.05/4)^4-1
P= $8,197.46 USING COMPOUND INTEREST FORMULAS THIS IS
=10000*(1+0.0509453)^-4
P= $8,197.46 USING EXCEL’S PV FUNCTION THIS IS
=PV(5.09453%,4,,-10000)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
A B C D E F G H I
PROBLEM 2.145
r= 6.000%
m= 2
YRS= 10
P= $4,000.00
FW= $7,224.44 USING COMPOUND INTEREST FORMULAS THIS IS
=4000*(1+0.06/2)^(2*10)
FW= $7,224.44 USING EXCEL’S FV FUNCTION THIS IS
=FV(0.06/2,2*10,,-4000)
OR, CAN USE THE EFFECTIVE RATE AS FOLLOWS
ieff=6.090% USING EXCEL’S EFFECT FUNCTION THIS IS
=EFFECT(6%,2)
FW= $7,224.44 USING COMPOUND INTEREST FORMULAS THIS IS
=4000*(1+0.0609)^10
FW= $7,224.44 USING EXCEL’S FV FUNCTION THIS IS
=FV(6.09%,10,,-4000)
PROBLEM 2.146
i= 1.25%
j= 10.00%
n= 12
P= $5,000.00
$5,000 = A1*(P|A1 1.25%,10%,12)
A1*= $5,000/(P|A1 1.25%,10%,12)
$256.78 USING ELECTRONIC INTEREST TABLES THIS IS
=$5,000/(P|A1 1.25%,10%,12)
=$5,000/19.471806
USING EXCEL’S SOLVER TOOL THIS IS
EOM CF
1$256.78 SOLVER CHANGE CELL
2$282.46
3$310.71
4$341.78
5$375.95
6$413.55
7$454.90
8$500.39
9$550.43
10 $605.48
11 $666.03
12 $732.63
PW= $5,000.00 SOLVER TARGET CELL
USING EXCEL’S NPV WORKSHEET FUNCTION THIS IS
256.78 =5000/NPV(1.25%,1,1.1,1.1^2,1.1^3,1.1^4,1.1^5,1.1^6,1.1^7,1.1^8,1.1^9,1.1^10,1.1^11)