excel data functions and amortization schedules

In cell D11, create a formula without using a function that references cell D7 (the total cost of the mortgage).

In cell C11, create a formula without using a function that references cell D6 (the total interest paid on the loan).

In cell B11, create a formula without using a function that references cell D5 (the monthly payments).

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.

These values are the interest rates that Kaleen will analyze in the Varying Interest Rate Schedule.

Select the range A12:A26 and fill it with a percent series based on the values in range A12:A13. She now wants to determine how different interest rates could impact the total cost of the campground.

Kaleen calculated the anticipated total cost of the campground using the mortgage interest rate she expects to qualify for.

In the function, Rate should be divided by 12 to calculate the monthly interest rate, and Term_Years should be multiplied by 12 to calculate the total number of monthly payments.

Put a negative sign before the PMT function to make the formula return a positive value.

In cell D5, create a formula using the PMT function to determine the monthly payments for the anticipated Campground mortgage, using the defined names Rate, Term_Years, and Loan_Amount as the rate, nper, and pv arguments in the formula.

Switch to the Campground Mortgage worksheet. She also wants to consider how different mortgage interest rates will impact the total cost of the campground. Kaleen wants to be ready when a campground becomes available, so she decides to start calculating how a mortgage will impact her family’s budget on a monthly basis and over the life of the loan. Justin and Kaleen Millowski have always dreamed of purchasing and running a campground.If cell B6 does not display your name, delete the file and download a new copy from the SAM website.With the file SC_EX16_4a_ FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.The program will add the file extension for you automatically. xlsx file extension in the Save As dialog box, do not type it. Save the file as SC_EX16_4a_ FirstLastName_2.xlsx by changing the “1” to a “2”.Open the file SC_EX16_4a_ FirstLastName_1.xlsx, available for download from the SAM website.

Excel data functions and amortization schedules