3. In the Loan Analysis worksheet, in the range B3:B5, enter the conditions for a $250,000 loan at an annual interest rae of 3.6 percent, with 12 monthly payments per year.
4. In the range B6:B8, calculate the interest rate per month, enter 30 for the total number of years on the loan, and calculate the total number of payments over those 30 years.
5. In the range B10:B12, use the PMT function to calculate the total monthly payment, calculate the total monthly payments over the 30 year loan, and calculate the cost of the loan (equal to the loam amount plus the total payments)
6. In the range D3:G25, complete the following steps to create a one-variable data table that shows the financial values for different annual interest rates:
A. in row 4, enter references to the loan’s annual interest rate monthly payment, total payments and cost of the loan.
B. In the range D5:D25 enter interest rates from 2% up to 4.5% in increments of 0.125%
C. In the range E5:G25 create a one variable data table to calculate the financial values in the table.
7. In the range I45:M25 complete the following steps to create a two variable data table that shows the total cost of the loan for different combinations of interest rates and year repayment.
A. In the range I5:I25 enter interest rates from 2% up to 4.5% in increments of -.125%
B. In the range J4:M4 enter the years values 10, 15, 20, and 30
C. In cell I4 enter a reference to cell B12 containing the cost of the loan.
D. In the range J5:M25 create a two variable data table to display the total loan cost in the table.
8. Apply a custom format to cell I4 to display Interest Rate instead of the value in cell I4
9. Create a scatter with straight lines chart of the data in the two variable data table in the range I5:M25. Use the select data command to name the four data series based on the Year values in the range J4:M4.
10. Move and resize the chart to cover the range A14:B25. Format the chart to make the content easily visible and understandable.
11. The more time give to repay the loan the more the loan costs. But shorter loans usually have lower interest rates. Use the scenario manager to create the scenarios outlined in Figure 10-48 based on the current interests rate for 10 years, 15 years, 20 years and 30 years mortgages. Add appropriate comments to each scenario.
Scenario Interest Rate Years Loan
30 Year Fixed 3.625% 30 $250,000
20 Year Fixed 3.250% 20 $250,000
15 Year Fixed 2.625% 15 $250,000
10 Year Fixed 2.375% 10 $250,000
12. Generate a scenario summary report that shows the values of the monthly payment total payments and cost of the loan under each scenario. Move the worksheet to the end of the workbook.
13. Create a scenario pivottable report showing only the cost of the loan under the four scenarios. Form the values using the currency format with negative values displayed in ted and enclosed in parenthesis’s. Remove any filters from the PivotTable. In cell A1 of the scenario PivotTable worksheet, enter Scenario PivotTable and then format the cell with the Title cell style.
14. Add a PivotChart to the report displaying the costs of the loan as a clustered column chart. Format the chart appropriately to make the information clear and legible.
15. Move the scenario pivotTable worksheet to the end of the workbook.