PROBLEM 2.147
i= 1.25%
G= $60.00
n= 12
P= $5,000.00
$5,000 = A*(P|A 1.25%,12)+G*(P|G 1.25%,12))
$130.17 USING ELECTRONIC INTEREST TABLES THIS IS
=($5,000-$60*(P|G 1.25%,12))/(P|A 1.25%,12))
=($5,000-60*59.296701)/11.079312
USING EXCEL’S SOLVER TOOL THIS IS
EOM CF
1 $130.17 SOLVER CHANGE CELL
2 $190.17
3 $250.17
4 $310.17
5 $370.17
6 $430.17
7 $490.17
8 $550.17
9 $610.17
10 $670.17
11 $730.17
12 $790.17
PW= $5,000.00 SOLVER TARGET CELL
PROBLEM 2.148
ai= 0.50% PER MONTH
n= 120 MONTHS
P= $410,000.00
A= $4,551.84 USING COMPOUND INTEREST FORMULAS THIS IS
=C5*(C3*(1+C3)^C4)/((1+C3)^C4-1)
A= $4,551.00 USING INTEREST TABLES THIS IS
=$410,000*(A|F 0.5%,120)
A= $4,551.84 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(0.5%,120,-410000)
bi= 0.50% PER MONTH
n= 240 MONTHS
P= $410,000.00
A= $2,937.37 USING COMPOUND INTEREST FORMULAS THIS IS
=C5*(C3*(1+C3)^C4)/((1+C3)^C4-1)
A= $2,937.36 USING INTEREST TABLES THIS IS
=$410,000*(A|F 0.5%,240)
A= $2,937.37 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(0.5%,2400,-410000)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
A B C D E F G H
PROBLEM 2.149
i= 0.75% PER MONTH
n= 480 MONTHS
F= $2,000,000.00
A= $427.23 USING COMPOUND INTEREST FORMULAS THIS IS
=2000000*(0.0075/((1+0.0075)^480-1))
A= $427.22 USING INTEREST TABLES THIS IS
=$2,000,000*(A|F 0.75%,480)
A= $427.23 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(0.75%,480,,-2000000)
PROBLEM 2.150
TO GO FROM NOMINAL ANNUAL TO EFFECTIVE ANNUAL
NOMINAL ANNUAL INTEREST RATE r= 10.000% USED r=10% FOR EXAMPLE
(E.G., 10.000%)
COMPOUNDING PERIODS PER YEAR m= 4 USED m=4 FOR EXAMPLE
(E.G., 4 FOR QUARTERLY)
EFFECTIVE ANNUAL INTEREST RATE
ieff=10.381%
=(1+0.1/4)^4-1
EFFECTIVE ANNUAL INTEREST RATE
ieff=10.381%
=EFFECT(10%,4)
TO GO FROM EFFECTIVE ANNUAL TO NOMINAL ANNUAL
EFFECTIVE ANNUAL INTEREST RATE
ieff=10.381%
(E.G., 10.381%)
COMPOUNDING PERIODS PER YEAR m= 4 USED m=4 FOR EXAMPLE
(E.G., 4 FOR QUARTERLY)
NOMINAL ANNUAL INTEREST RATE r= 10.000% USED r=10% FOR EXAMPLE
=4*((1+0.10381)^(1/4)-1)
USING COMPOUND
INTEREST FORMULAS THIS
IS
USING EXCEL’S EFFECT
FUNCTION THIS IS
USING COMPOUND
INTEREST FORMULAS THIS
IS
PROBLEM 2.151
ar= 10.00%
m= 2
ieff= 10.2500% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.1/2)^2-1
ieff= 10.2500% USING EXCEL’S EFFECT FUNCTION THIS IS
=EFFECT(10%,2)
br= 10.00%
m= 3
ieff= 10.3370% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.1/3)^3-1
ieff= 10.3370% USING EXCEL’S EFFECT FUNCTION THIS IS
=EFFECT(10%,3)
cr= 10.00%
m= 4
ieff= 10.3813% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.1/4)^4-1
ieff= 10.4260% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.1/6)^6-1
ieff= 10.4713% USING COMPOUND INTEREST FORMULAS THIS IS
10.4713%
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
35
36
37
38
39
A B C D E F G H
PROBLEM 2.152
ar= 5.00%
m= 2
ieff= 5.0625% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.05/2)^2-1
ieff= 5.0625% USING EXCEL’S EFFECT FUNCTION THIS IS
=EFFECT(5%,2)
br= 5.00%
m= 3
ieff= 5.0838% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.05/3)^3-1
ieff= 5.0838% USING EXCEL’S EFFECT FUNCTION THIS IS
=EFFECT(5%,3)
cr= 5.00%
m= 4
ieff= 5.0945% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.05/4)^4-1
ieff= 5.0945% USING EXCEL’S EFFECT FUNCTION THIS IS
er= 5.00%
m= 12
ieff= 5.1162% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.05/12)^12-1
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.153
MONTHLY RATE= 2.50%
m= 12
P= $2,000.00
YRS= 2
a NOM ANN RATE= 30.000% =2.5%*12
b EFF ANN RATE= 34.4889% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+30%/12)^12-1
EFF ANN RATE= 34.4889% USING EXCEL’S EFFECT FUNCTION THIS IS
=EFFECT(30%,12)
cF= $3,617.45 USING ELECTRONIC INTEREST TABLES THIS IS
=$2,000*(F|P 2.5%,24)
F= $3,617.45 USING EXCEL’S FV FUNCTION THIS IS
=FV(2.5%,12*2,,-2000)
dF= $3,617.45 USING ELECTRONIC INTEREST TABLES THIS IS
=$2,000*(F|P 34.4889%,2)
F= $3,617.45 USING EXCEL’S FV FUNCTION THIS IS
=FV(34.4889%,2,,-2000)
e INTEREST= $1,617.45 =3617.45-2000.00
fA= $111.82 USING INTEREST TABLES THIS IS
=$2,000*(A|P 2.5%,24)
A= $111.83 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(2.5%,12*2,-2000)
1
2
3
4
5
6
7
8
18
19
20
21
22
23
24
31
32
33
34
35
36
44
45
46
47
48
49
50
57
A B C D E F G H
PROBLEM 2.154
a ANNUALLY
r= 6.000%
m= 1
YRS= 8
F= $20,000.00
m= 2
YRS= 8
F= $20,000.00
P= $12,463.34 USING COMPOUND INTEREST FORMULAS THIS IS
=20000*(1+0.06/2)^(-2*8)
P= $12,463.40 USING INTEREST TABLES THIS IS
m= 4
YRS= 8
F= $20,000.00
P= $12,419.86 USING COMPOUND INTEREST FORMULAS THIS IS
=20000*(1+0.06/4)^(-4*8)
m= 12
YRS= 8
F= $20,000.00
P= $12,390.48 USING COMPOUND INTEREST FORMULAS THIS IS
=20000*(1+0.06/12)^(-12*8)
P= $12,390.40 USING INTEREST TABLES THIS IS
m= 52
58
59
60
61
62
63
64
65
66
67
74
75
76
77
78
85
86
97
98
99
107
108
109
110
A B C D E F G H
YRS= 8
F= $20,000.00
P= $12,379.09 USING COMPOUND INTEREST FORMULAS THIS IS
=20000*(1+0.06/52)^(-52*8)
P= $12,379.00 USING INTEREST TABLES THIS IS
=$20,000*(P|F 0.6/52%,416)
P= $12,379.09 USING EXCEL’S FV FUNCTION THIS IS
=PV(0.06/52,52*8,,-20000)
P= $12,376.16 USING COMPOUND INTEREST FORMULAS THIS IS
=20000*(1+0.06/365)^(-365*8)
P= $12,376.16 USING EXCEL’S FV FUNCTION THIS IS
=PV(0.06/365,365*8,,-20000)
P= $12,375.69 USING COMPOUND INTEREST FORMULAS THIS IS
=20000*(1+0.06/8760)^(-8760*8)
=20000*(1+0.06/525600)^(-525600*8)
P= $12,375.67 USING EXCEL’S FV FUNCTION THIS IS
=PV(0.06/525600,525600*8,,-20000)
P= $12,375.67 USING COMPOUND INTEREST FORMULAS THIS IS
=20000*(1+0.06/31536000)^(-31536000*8)
P= $12,375.67 USING EXCEL’S FV FUNCTION THIS IS
=PV(0.06/31536000,31536000*8,,-20000)
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
PROBLEM 2.155
m= 12
YRS= 5
A= $501.49
P= $20,000.00
r= 17.412% <–SOLVER CHANGED CELL
P= $20,000.00 <–SOLVER TARGET CELL
USING EXCEL’S PV FUNCTION THIS IS
=PV(r/12,12*5,-20000)
NOW, KNOWING r, PROCEED WITH THE PROBLEM
a
imonthly=1.451% =17.412%/12
br= 17.412% FOUND USING SOLVER ABOVE
c
ieff=18.871% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.17412/12)^12-1
ieff=18.871% USING EXCEL’S EFFECT FUNCTION THIS IS
=EFFECT(17.412%,12)
SINCE THIS PROBLEM SEEKS TO DETERMINE AN INTEREST RATE, IT IS
BEST TO USE SOLVER TO AVOID A DIFFICULT ANALYTICAL SOLUTION OR
INTERPOLATION.
BEGIN BY ESTABLISHING A CELL FOR THE VALUE OF THE NOMINAL RATE
r AND USE ANY VALUE, ASSUMING IT IS CORRECT. IT WILL LATER BE
CHANGED BY SOLVER TO THE CORRECT VALUE.
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
PROBLEM 2.156
m= 12 MONTHS PER YEAR
n= 40 MONTHS
A= $250.00 PER MONTH
F= $12,000.00
r= 10.932% <–SOLVER CHANGED CELL
F= $12,000.00 <–SOLVER TARGET CELL
USING EXCEL’S FV FUNCTION THIS IS
=FV(r%/12,40,-250)
NOW, KNOWING r, PROCEED WITH THE PROBLEM
a
imonthly=0.911% =10.932%/12
br= 10.932% FOUND USING SOLVER ABOVE
c
ieff=11.497%
USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.10932/12)^12-1
ieff=11.497% USING EXCEL’S EFFECT FUNCTION THIS IS
=EFFECT(10.932%,12)
SINCE THIS PROBLEM SEEKS TO DETERMINE AN INTEREST RATE, IT
IS BEST TO USE SOLVER TO AVOID A DIFFICULT ANALYTICAL
SOLUTION OR INTERPOLATION.
BEGIN BY ESTABLISHING A CELL FOR THE VALUE OF THE NOMINAL
RATE r AND USE ANY VALUE, ASSUMING IT IS CORRECT. IT WILL
LATER BE CHANGED BY SOLVER TO THE CORRECT VALUE.
PROBLEM 2.157
r= 10%
m= 2
ieff=10.250% =(1+C4/C5)^C5-1
10.250% =EFFECT(C4,C5)
n= 4
A= $2,000.00
USING THE ELECTRONIC NTEREST TABLES,
F= $2,000(F|A 10.25%,4)(F|P 10.25%,2)
$2,000(4.65810)(1.21551)
$11,323.93
USING EXCEL’S EFFECT AND FV FUNCTIONS,
F= $11,323.90 =FV(EFFECT(10%,2),2,,FV(EFFECT(10%,2),4,2000))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
A B C D E F G H
PROBLEM 2.158
a REFUND= $1,000.00
INT RATE= 3.000% PER WEEK
INT PAID= $30.00 =1000*0.03
m= 52 WEEKS/YR
CASH RCVD= $970.00 =1000-30
bWEEKLY INT= 3.093% =30/970
cr= 160.825% =3.093%*52
d
ieff=387.382% USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.03093)^52-1
ieff=387.382% USING EXCEL’S EFFECT FUNCTION
387.384%
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 I
PROBLEM 2.159
r= 6.000% PER YEAR
m= 4 QUARTERS
k= 12 MONTHS
A= $400.00 PER MONTH
P= $20,000.00
FIRST, CALCULATE THE INTEREST RATE PER CASH FLOW PERIOD (MONTH)
i= 0.4975206% PER MONTH
USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.06/4)^(4/12)-1
n= 57.6340855 USING EXCEL’S NPER FUNCTION THIS IS
=NPER(0.004975206,400,-20000)
OR 57 WHOLE MONTHS
OR, COULD USE SOLVER TO FIND n
n= 57.6340855 <–SOLVER CHANGED CELL
MONTHS
P= $20,000.00 <–SOLVER TARGET CELL
USING EXCEL’S PV FUNCTION THIS IS
=PV(0.004975206,n,-400)
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.160
r= 6.000% PER YEAR
m= 12 MONTHS PER YEAR
k= 4 WITHDRAWALS PER QTR
A= $1,200.00 PER QUARTER
P= $20,000.00
FIRST, CALCULATE THE INTEREST RATE PER CASH FLOW PERIOD (QUARTER)
i= 1.508% PER QUARTER
USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.06/12)^(12/4)-1
NOW, PROCEED WITH THE REST OF THE PROBLEM
n= 19.33837953 QUARTERS
USING EXCEL’S NPER FUNCTION THIS IS
=NPER(1.508%,1200,-20000)
OR COULD USE SOLVER AS FOLLOWS
n= 19.33837953 <–SOLVER CHANGED CELL QUARTERS
P= $20,000.00 <–SOLVER TARGET CELL
USING EXCEL’S PV FUNCTION THIS IS
=FV(1.508%,n,-1200)
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
PROBLEM 2.161
r= 7.000%
m= 4 QUARTERS PER YEAR
k= 12 DEPOSITS PER YEAR
A= $100.00 PER MONTH
F= $15,000.00
FIRST, CALCULATE THE INTEREST RATE PER CASH FLOW PERIOD (MONTH)
i= 0.5799633% PER MONTH
USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.07/4)^(4/12)-1
NOW, PROCEED WITH THE REST OF THE PROBLEM
n= 108.2348272 MONTHS
USING EXCEL’S NPER FUNCTION THIS IS
=NPER(0.57996326%,100,,-15000)
OR COULD USE SOLVER AS FOLLOWS
n= 108.2348272 <–SOLVER CHANGED CELL
F= $15,000.00 <–SOLVER TARGET CELL
USING EXCEL’S FV FUNCTION THIS IS
=FV(0.57996326,n,-100)
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 F G H I J
PROBLEM 2.162
r= 6.00% PER YEAR
m= 4 CMPDGS PER YEAR
n= 60 MONTHS
k= 12 PAYMENTS PER YEAR
P= $50,000.00
FIRST, CALCULATE THE INTEREST RATE PER CASH FLOW PERIOD (MONTH)
i= 0.49752063% PER MONTH
USING COMPOUND INTEREST FORMULAS THIS IS
=(1+0.06/4)^(4/12)-1
NOW, PROCEED WITH THE REST OF THE PROBLEM
A= $965.95
USING COMPOUND INTEREST FORMULAS THIS IS
=50000*(0.0049752063*(1+0.0049752063)^60)/((1+0.0049752063)^60-1)
A= $966.00 USING INTEREST TABLES THIS IS
=$50,000*(A|P 0.49752063%,60)
A= $965.95 USING EXCEL’S PMT FUNCTION THIS IS
=PMT(0.49752063%,60,-50000)