5. 6. 7. Miranda wants to calculate the monthly payment for a loan to purchase the Beecher Street house at the most favorable interest rate she found online. Calculate the payment as follows: a. b. C. d. e. In cell D5, start to enter a formula using the PMT function. For the rate argument, divide the Rate (cell D3) by 12 to use the monthly interest rate. In cell D6, enter a formula without using a function that multiples the Monthly Payment (cell D5) by the Term_in_Months (cell D4), and then subtracts the Loan_Amt (cell B7) from the result to determine the total interest on the mortgage. For the nper argument, use the Term_in_Months (cell D4) to specify the number of periods. For the pv argument, use the Loan_Amt (cell B7) to include the present value. Insert a negative sign (-) after the equal sign in the formula to display the result as a positive amount. In cell D7, enter a formula without using a function that adds the Price (cell B5) to the Total Interest (cell D6) to determine the total cost of the house. Miranda wants to compare monthly payments for interest rates that vary from 4.00 to 4.96 percent and for terms of 180, 240, and 360 months. She has already set up the structure for a data table in the range A11: D24. a. Create a two-variable data table as follows to provide the comparison that Miranda requests: b. In A11, enter a formula without using a function that references the Monthly Payment amount (cell D5) because Miranda wants to compare the monthly payments. Based on the range A11:D24, create a two-variable data table that uses the term in months (cell D4) as the row input cell and the rate (cell D3) as the column input cell.
5. 6. 7. Miranda wants to calculate the monthly payment for a loan to purchase the Beecher Street house at the most favorable interest rate she found online. Calculate the payment as follows: a. b. C. d. e. In cell D5, start to enter a formula using the PMT function. For the rate argument, divide the Rate (cell D3) by 12 to use the monthly interest rate. In cell D6, enter a formula without using a function that multiples the Monthly Payment (cell D5) by the Term_in_Months (cell D4), and then subtracts the Loan_Amt (cell B7) from the result to determine the total interest on the mortgage. For the nper argument, use the Term_in_Months (cell D4) to specify the number of periods. For the pv argument, use the Loan_Amt (cell B7) to include the present value. Insert a negative sign (-) after the equal sign in the formula to display the result as a positive amount. In cell D7, enter a formula without using a function that adds the Price (cell B5) to the Total Interest (cell D6) to determine the total cost of the house. Miranda wants to compare monthly payments for interest rates that vary from 4.00 to 4.96 percent and for terms of 180, 240, and 360 months. She has already set up the structure for a data table in the range A11: D24. a. Create a two-variable data table as follows to provide the comparison that Miranda requests: b. In A11, enter a formula without using a function that references the Monthly Payment amount (cell D5) because Miranda wants to compare the monthly payments. Based on the range A11:D24, create a two-variable data table that uses the term in months (cell D4) as the row input cell and the rate (cell D3) as the column input cell.
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
I need help to do question 4 to 7 and show how to find those steps in Excel.
Transcribed Image Text:2
3
4
6
A
B
New House Mortgage Calculator
Date
Property
Price
Down Payment
Loan Amount
8
9
10
Rate
11 P*(1/N))/(1-(1/N))^-T)
12
4.00%
13
4.08%
14
4.16%
15
4.24%
4.32%
4.40%
4.48%
4.56%
4.64%
4.72%
4.80%
4.88%
4.96%
16
17
18
19
20
21
22
23
24
4
>
Beecher Street
$
10/1/2021 Rate
Ş
$
350,000
35,000
315,000
C
180
$2,330.02
$2,342.67
$2,355.35
$2,368.08
$2,380.85
$2,393.66
$2,406.51
$2,419.40
$2,432.33
$2,445.30
$2,458.31
$2,471.35
$2,484.44
Term in Months
Varying Interest Rates and Terms
Number of Months
240
Monthly Paymen
Total Interest
Total Cost
$1,908.84
$1,922.14
$1,935.50
$1,948.91
$1,962.37
$1,975.88
$1,989.45
$2,003.06
$2,016.73
$2,030.45
$2,044.22
$2,058.04
$2,071.91
Documentation Sheet1 Mortgage Calculator
$
$
D
4.32%
360
$1,562.55
247,516.76
597,517
360
$1,503.86
$1,518.42
$1,533.06
$1,547.77
$1,562.55
$1,577.40
$1,592.32
$1,607.31
$1,622.37
$1,637.50
$1,652.70
$1,667.96
$1,683.30
Listings Car Loan
E
Scenarios
Loan Amount
Annual Interest Rate
Monthly Interest Rate
Loan Period in Years
Loan Period in Months
Start Date
Monthly Payment
Future Value
$
G
Current Rent
$
Monthly House Payment $
Bank 1
315,000 $
4.40%
0.37%
30
360
1/5/2022
$
1,500
1,563
H
Bank 2
4.56%
0.38%
30
360
1/5/2022
($1,176,334.51) $1,151,573.10
$
(1,500) $
Bank 3
337,500
4.56%
0.38%
20
240
1/5/2022
(1,500)
Transcribed Image Text:4.
5.
6.
7.
Miranda wants to calculate the monthly payment for a loan to purchase the Beecher
Street house at the most favorable interest rate she found online. Calculate the payment
as follows:
a.
b.
C.
d.
e.
In cell D5, start to enter a formula using the PMT function.
For the rate argument, divide the Rate (cell D3) by 12 to use the monthly interest
rate.
For the nper argument, use the Term_in_Months (cell D4) to specify the number
of periods.
In cell D6, enter a formula without using a function that multiples the
Monthly Payment (cell D5) by the Term_in_Months (cell D4), and then subtracts
the Loan_Amt (cell B7) from the result to determine the total interest on the mortgage.
For the pv argument, use the Loan_Amt (cell B7) to include the present value.
Insert a negative sign (-) after the equal sign in the formula to display the result
as a positive amount.
In cell D7, enter a formula without using a function that adds the Price (cell B5) to the
Total Interest (cell D6) to determine the total cost of the house.
a.
Miranda wants to compare monthly payments for interest rates that vary from 4.00 to
4.96 percent and for terms of 180, 240, and 360 months. She has already set up the
structure for a data table in the range A11:D24.
Create a two-variable data table as follows to provide the comparison that Miranda
requests:
In
A11, enter a formula without using a function that references the
Monthly Payment amount (cell D5) because Miranda wants to compare the
monthly payments.
b.
Based on the range A11:D24, create a two-variable data table that uses the term
in months (cell D4) as the row input cell and the rate (cell D3) as the column
input cell.
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 2 steps with 3 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