Create the spreadsheet models shown in below images and answer the following questions. a. What is the effect of a change in the interest rate from 8% to 10% in the spreadsheet model shown in Figure A? b. For the original model in Figure A, what interest rate is required to decrease the monthly payments by 20%? What change in the loan amount would have the same effect? c. In the spreadsheet shown in Figure B, what is the effect of a prepayment of $200 per month? What prepayment would be necessary to pay off the loan in 25 years instead of 30 years?
Create the spreadsheet models shown in below images and answer the following questions. a. What is the effect of a change in the interest rate from 8% to 10% in the spreadsheet model shown in Figure A? b. For the original model in Figure A, what interest rate is required to decrease the monthly payments by 20%? What change in the loan amount would have the same effect? c. In the spreadsheet shown in Figure B, what is the effect of a prepayment of $200 per month? What prepayment would be necessary to pay off the loan in 25 years instead of 30 years?
Essentials Of Investments
11th Edition
ISBN:9781260013924
Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Chapter1: Investments: Background And Issues
Section: Chapter Questions
Problem 1PS
Related questions
Question
Create the spreadsheet models shown in below images and answer the following questions. a. What is the effect of a change in the interest rate from 8% to 10% in the spreadsheet model shown in Figure A? b. For the original model in Figure A, what interest rate is required to decrease the monthly payments by 20%? What change in the loan amount would have the same effect? c. In the spreadsheet shown in Figure B, what is the effect of a prepayment of $200 per month? What prepayment would be necessary to pay off the loan in 25 years instead of 30 years?
Transcribed Image Text:1
2
3
4
5
6
7
8
9
10
11
BAHASA272
12
13
14
15
16
17
18
19
20
21
A
с
Loan Amount
Interest Rate
Number of Years
Simple Loan Calculation Model in Excel
E
Number of Months
Interest Rate/Month
Monthly Loan Payment
$150,000
8.00%
30
360
0.67%
$1,100.68
Excel Spreadsheet Static Model Example of a Simple Loan
F
-E8*12
-E7/12
G
H
=PMT (E11, E10, E6, 0)
Transcribed Image Text: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
30
Dynamic Loan Calculation Model with Prepayment in Excel
Month
Loan Amount
Interest Rate
Number of Years
Number of Months
Interest Rate/Month
Monthly Loan Payment
-$E$13
$100.00
Normal Prepay
Payment Amount
$150,000
8.00%
30
Excel Spreadsheet Dynamic Model Example of a Simple Loan
-$C$20
Total
Payment
-824+C24
24
1 $1,100.65 $100.00 $1,200.
2 $1,100.65 $100.00 $1,200.65
3 $1,100.65 $100.00 $1,200.65
4 $1,100.65 $100.00 $1,200.65
S $1,100.65 $100.00 $1,200.65
360
0.67%
$1,100.64
Principle
Owed
$150,000
$149,795
$149,597
-E8*12
$149,394
$149,189
$148,983
17/12
H
PMT (E11, E10, E6, 0)
-E23*(1+$E$11)-024
I
J
A $100 Prepayment every Month-Loan is
paid off in Month 270
Copy the Cells in Row 24 into Rows 25 through
Row 383 to get 360 Months of Results
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 8 images
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, finance and related others by exploring similar questions and additional content below.Recommended textbooks for you
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
Foundations Of Finance
Finance
ISBN:
9780134897264
Author:
KEOWN, Arthur J., Martin, John D., PETTY, J. William
Publisher:
Pearson,
Fundamentals of Financial Management (MindTap Cou…
Finance
ISBN:
9781337395250
Author:
Eugene F. Brigham, Joel F. Houston
Publisher:
Cengage Learning
Corporate Finance (The Mcgraw-hill/Irwin Series i…
Finance
ISBN:
9780077861759
Author:
Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan Professor
Publisher:
McGraw-Hill Education