50.1K

Verified Solution

Link Copied!

My Scenario Summary isn't changing the monthly payment with the change in Down Payment Percentage.

Problem:

My inputs:

2. Do a quick research and find out the median home price in my town area. Assuming you will put down 10% down payment and you will take up a mortgage loan for the remaining amount. Find out the 30-year fixed interest rate and 15-year fixed interest rate from the bank you have an account. Prepare the table below and additionally, set up a scenario analysis that shows your monthly payment in the case if you were to put down 5%, 10%, and 15% of down payment. Median Home Price Down Payment (%) 10 Mortgage Loan Amount Years to Maturity Fixed Interest Rate Monthly Payment 30 Scenario Summary 15% down 10% Down 5% Down 20% Down 15% 10% 5% 20% Changing Cells: Downpayment Result Cells: MonthlyPayment_30 Yr Monthly Payment 15 Year 30 1 Down Payment Mortgage Loan Amount Years to Maturity Fixed Interest Rate Monthly Payment 20% 97,875.20 3.66% $448.29 20% 97,875.20 15 3.32% $691.07 Scenario Manager ? X Edit Scenario ? x Scenario name: 20% Down Scenarios: 20% Down 15% Down 10% Down 5% Down Add... Changing cells: Delete 8 9 10 Edit... Ctrl+click cells to select non-adjacent changing cells. Comment: 11 Merge... Summary... 12 13 14 Changing cells: Down Payment Comment: 15 Protection Prevent changes Hide 16 17 OK Cancel 18 19 20 Show ? Close x Scenario Values 21 22 Enter values for each of the changing cells. 1: Downpayment 0.2 Scenario Summary ? X Report type O Scenario summary Scenario PivotTable report Result cells: G2,63 OK Cancel 24 OK Cancel G2 - x fc --PMT(F2/12,E2*12,D2) D E F G 1 Down Payment Mortgage Loan Amount Years to Maturity Fixed Interest Rate Monthly Payment 2 20% 97,875.20 30 3.66% $448.29 20% 97,875.20 15 3.32% $691.07 3 2. Do a quick research and find out the median home price in my town area. Assuming you will put down 10% down payment and you will take up a mortgage loan for the remaining amount. Find out the 30-year fixed interest rate and 15-year fixed interest rate from the bank you have an account. Prepare the table below and additionally, set up a scenario analysis that shows your monthly payment in the case if you were to put down 5%, 10%, and 15% of down payment. Median Home Price Down Payment (%) 10 Mortgage Loan Amount Years to Maturity Fixed Interest Rate Monthly Payment 30 Scenario Summary 15% down 10% Down 5% Down 20% Down 15% 10% 5% 20% Changing Cells: Downpayment Result Cells: MonthlyPayment_30 Yr Monthly Payment 15 Year 30 1 Down Payment Mortgage Loan Amount Years to Maturity Fixed Interest Rate Monthly Payment 20% 97,875.20 3.66% $448.29 20% 97,875.20 15 3.32% $691.07 Scenario Manager ? X Edit Scenario ? x Scenario name: 20% Down Scenarios: 20% Down 15% Down 10% Down 5% Down Add... Changing cells: Delete 8 9 10 Edit... Ctrl+click cells to select non-adjacent changing cells. Comment: 11 Merge... Summary... 12 13 14 Changing cells: Down Payment Comment: 15 Protection Prevent changes Hide 16 17 OK Cancel 18 19 20 Show ? Close x Scenario Values 21 22 Enter values for each of the changing cells. 1: Downpayment 0.2 Scenario Summary ? X Report type O Scenario summary Scenario PivotTable report Result cells: G2,63 OK Cancel 24 OK Cancel G2 - x fc --PMT(F2/12,E2*12,D2) D E F G 1 Down Payment Mortgage Loan Amount Years to Maturity Fixed Interest Rate Monthly Payment 2 20% 97,875.20 30 3.66% $448.29 20% 97,875.20 15 3.32% $691.07 3

Answer & Explanation Solved by verified expert

See Answer