CFIN6
Spreadsheet Problem Solution
Chapter 4
a. Following are the input data and the results for the initial scenario:
INPUT DATA:
KEY OUTPUT:
Loan amount
30,000
3,523.79
Interest rate
10.00%
Number of years
20
MODEL-GENERATED DATA:
Amortization schedule:
Principal
Remaining
PV of
Year
Payment
Interest
Repayment
Balance
Payments
1
3,523.79
3,000.00
523.79
29,476.21
3,203.44
2
3,523.79
2,947.62
576.17
28,900.04
2,912.22
3
3,523.79
2,890.00
633.78
28,266.26
2,647.47
4
3,523.79
2,826.63
697.16
27,569.10
2,406.80
5
3,523.79
2,756.91
766.88
26,802.22
2,188.00
6
3,523.79
2,680.22
843.57
25,958.65
1,989.09
7
3,523.79
2,595.87
927.92
25,030.73
1,808.26
8
3,523.79
2,503.07
1,020.72
24,010.01
1,643.87
9
3,523.79
2,401.00
1,122.79
22,887.22
1,494.43
10
3,523.79
2,288.72
1,235.07
21,652.16
1,358.57
11
3,523.79
2,165.22
1,358.57
20,293.58
1,235.07
12
3,523.79
2,029.36
1,494.43
18,799.15
1,122.79
13
3,523.79
1,879.92
1,643.87
17,155.28
1,020.72
14
3,523.79
1,715.53
1,808.26
15,347.02
927.92
15
3,523.79
1,534.70
1,989.09
13,357.93
843.57
16
3,523.79
1,335.79
2,188.00
11,169.94
766.88
17
3,523.79
1,116.99
2,406.80
8,763.14
697.16
18
3,523.79
876.31
2,647.47
6,115.67
633.78
19
3,523.79
611.57
2,912.22
3,203.44
576.17
20
3,523.79
320.34
3,203.44
523.79
CFIN6
b.
INPUT DATA:
KEY OUTPUT:
Loan amount
60,000
Payment
7,047.58
Interest rate
10.00%
Number of years
20
MODEL-GENERATED
DATA:
Amortization schedule:
Principal
Remaining
PV of
Year
Payment
Interest
Repayment
Balance
Payments
1
7,047.58
6,000.00
1,047.58
58,952.42
6,406.89
2
7,047.58
5,895.24
1,152.34
57,800.09
5,824.44
3
7,047.58
5,780.01
1,267.57
56,532.52
5,294.95
4
7,047.58
5,653.25
1,394.33
55,138.19
4,813.59
5
7,047.58
5,513.82
1,533.76
53,604.43
4,375.99
6
7,047.58
5,360.44
1,687.13
51,917.30
3,978.17
7
7,047.58
5,191.73
1,855.85
50,061.45
3,616.52
8
7,047.58
5,006.15
2,041.43
48,020.02
3,287.75
9
7,047.58
4,802.00
2,245.58
45,774.45
2,988.86
10
7,047.58
4,577.44
2,470.13
43,304.31
2,717.15
11
7,047.58
4,330.43
2,717.15
40,587.17
2,470.13
12
7,047.58
4,058.72
2,988.86
37,598.31
2,245.58
13
7,047.58
3,759.83
3,287.75
34,310.56
2,041.43
14
7,047.58
3,431.06
3,616.52
30,694.04
1,855.85
15
7,047.58
3,069.40
3,978.17
26,715.86
1,687.13
16
7,047.58
2,671.59
4,375.99
22,339.87
1,533.76
17
7,047.58
2,233.99
4,813.59
17,526.28
1,394.33
18
7,047.58
1,752.63
5,294.95
12,231.33
1,267.57
19
7,047.58
1,223.13
5,824.44
1,152.34
20
7,047.58
6,406.89
1,047.58
80,951.55
60,000.00
60,000.00
CFIN6
c.
INPUT DATA:
KEY OUTPUT:
Loan amount
60,000
Payment
12,321.39
Interest rate
20.00%
Number of years
20
MODEL-GENERATED DATA:
Amortization schedule:
Principal
Remaining
PV of
Year
Payment
Interest
Repayment
Balance
Payments
1
12,321.39
12,000.00
321.39
59,678.61
10,267.83
2
12,321.39
11,935.72
385.67
59,292.94
8,556.52
3
12,321.39
11,858.59
462.80
58,830.13
7,130.44
4
12,321.39
11,766.03
555.37
58,274.77
5,942.03
5
12,321.39
11,654.95
666.44
57,608.33
4,951.69
6
12,321.39
11,521.67
799.73
56,808.60
4,126.41
7
12,321.39
11,361.72
959.67
55,848.93
3,438.67
8
12,321.39
11,169.79
1,151.61
54,697.33
2,865.56
9
12,321.39
10,939.47
1,381.93
53,315.40
2,387.97
10
12,321.39
10,663.08
1,658.31
51,657.09
1,989.97
11
12,321.39
10,331.42
1,989.97
49,667.12
1,658.31
12
12,321.39
9,933.42
2,387.97
47,279.15
1,381.93
13
12,321.39
9,455.83
2,865.56
44,413.59
1,151.61
14
12,321.39
8,882.72
3,438.67
40,974.91
959.67
15
12,321.39
8,194.98
4,126.41
36,848.50
799.73
16
12,321.39
7,369.70
4,951.69
31,896.81
666.44
17
12,321.39
6,379.36
5,942.03
25,954.78
555.37
18
12,321.39
5,190.96
7,130.44
18,824.35
462.80
19
12,321.39
3,764.87
8,556.52
10,267.83
385.67
20
12,321.39
2,053.57
10,267.83
321.39
60,000.00
60,000.00