1. Create a single variable data table to determine the impact that the variable interest rates (in the range A12:A22) will have on the total cost of the campground.
   1. In cell B11, create a formula without using a function that references cell D5 (the monthly payments).
   2. In cell C11, create a formula without using a function that references cell D6 (the total interest paid on the loan).
   3. In cell D11, create a formula without using a function that references cell D7 (the total cost of the mortgage).
   4. Select the range A11:D26 and create a single-variable data table, using an absolute reference to cell D3 (the mortgage interest rate) as the Column input cell.

Show step by step

D5 X fx --PMT(Rate/12, Term_Years *12,Loan_Amount) D E F G H I J K L Camp Millowski 5.750% Year Interest Paid 3 Date 4 Item 5 Price 6 Down Payment 7 Loan Amount Mortgage Loan Payment Calculator 15-Aug-18 Rate Campground Term (Years) 486,000.00 Monthly Payment 97,200.00 Total Interest 388,800.00 Total Cost $ $ $ 3,228,63 192,354.20 678,354.20 $ 4 $ 10 Rate Varying Interest Rate Schedule Monthly Payment Total Interest Total Cost Amortization Schedule Beginning Balance Ending Balance Paid on Principal 1 $ 388,800.00 $ 371,973.53 2 $ 371,973.53 $ 354,153.63 3 $ 354,153.63 $ 335,281.65 335,281.65 $ 315,295.47 5 $ 315,295.47 $ 294,129.30 6 $ 294,129.30 $ 271,713.49 7 $ 271,713.49 $ 247,974.26 8 $ 247,974.26 $ 222,833,46 9 $ 222,833.46 $ 196,208.36 10 $ 196,208.36 $ 168,011.31 11 $ 168,011.31 $ 138,149.52 12 $ 138,149.52 $ 106,524.69 13 $ 106,524.69 $ 73,032.73 14 $ 73,032.73 $ 37,563.42 15 $ 37,563.42 $ Subtotal - Down Payment Total Cost 3.500% 3.750% 4.000% 4.250% 4.500% 4.750% 5.000% 5.250% 5.500% 5.750% 6.000% 6.250% 6.500% 6.750% 7.000% $ $ D5 X fx --PMT(Rate/12, Term_Years *12,Loan_Amount) D E F G H I J K L Camp Millowski 5.750% Year Interest Paid 3 Date 4 Item 5 Price 6 Down Payment 7 Loan Amount Mortgage Loan Payment Calculator 15-Aug-18 Rate Campground Term (Years) 486,000.00 Monthly Payment 97,200.00 Total Interest 388,800.00 Total Cost $ $ $ 3,228,63 192,354.20 678,354.20 $ 4 $ 10 Rate Varying Interest Rate Schedule Monthly Payment Total Interest Total Cost Amortization Schedule Beginning Balance Ending Balance Paid on Principal 1 $ 388,800.00 $ 371,973.53 2 $ 371,973.53 $ 354,153.63 3 $ 354,153.63 $ 335,281.65 335,281.65 $ 315,295.47 5 $ 315,295.47 $ 294,129.30 6 $ 294,129.30 $ 271,713.49 7 $ 271,713.49 $ 247,974.26 8 $ 247,974.26 $ 222,833,46 9 $ 222,833.46 $ 196,208.36 10 $ 196,208.36 $ 168,011.31 11 $ 168,011.31 $ 138,149.52 12 $ 138,149.52 $ 106,524.69 13 $ 106,524.69 $ 73,032.73 14 $ 73,032.73 $ 37,563.42 15 $ 37,563.42 $ Subtotal - Down Payment Total Cost 3.500% 3.750% 4.000% 4.250% 4.500% 4.750% 5.000% 5.250% 5.500% 5.750% 6.000% 6.250% 6.500% 6.750% 7.000% $ $