4. 4 5. 6. 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: 7. 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: 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
icon
Related questions
Question
I need help with questions 4 to 7 and please show how to work on the Excel Table.
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.
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.
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: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)
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps with 4 images

Blurred answer
Knowledge Booster
Cost of Credit
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.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Essentials Of Investments
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
FUNDAMENTALS OF CORPORATE FINANCE
FUNDAMENTALS OF CORPORATE FINANCE
Finance
ISBN:
9781260013962
Author:
BREALEY
Publisher:
RENT MCG
Financial Management: Theory & Practice
Financial Management: Theory & Practice
Finance
ISBN:
9781337909730
Author:
Brigham
Publisher:
Cengage
Foundations Of Finance
Foundations Of Finance
Finance
ISBN:
9780134897264
Author:
KEOWN, Arthur J., Martin, John D., PETTY, J. William
Publisher:
Pearson,
Fundamentals of Financial Management (MindTap Cou…
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…
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