5.2 Sensitivity Analysis

Problem. Examine the same 30 year mortgage for $300,000 as in the previous section. Consider what

would happen if the interest rate / year dropped from 8.00% to 7.00%. How much of each year's payment

goes to paying interest vs. how much goes to reducing the principal under the two interest rates?

Solution Strategy. Construct a data table for the interest component under the two interest rates.

Construct another data table for the principal component under the two interest rates. Create a graph of

the two interest components and two principal components.

FIGURE 5.3 Spreadsheet for Loan Amortization - Sensitivity Analysis.

How To Build Your Own Spreadsheet Model.

1. Start with the Basics Spreadsheet. Open the spreadsheet that you created for Loan Amortization

- Basics and immediately save the spreadsheet under a new name using the File | Save As

command.

2. Interest Component Data Table. Create a list of input values for the Interest Rate / Year (7.0%

and 8.0%) in the range A18:A19. Create an output formula that references the Interest

Component row by entering the formula =B12 in cell B17 and copy it to the range

C17:AE17. Select the range A17:AE19 for the One-Variable Data Table. This range includes

both the input values on the left side of the range and the output formula on the top of the range.

Then choose Data | Table from the main menu and a Table dialog box pops up. Enter the cell

address B5 (Interest Rate / Year) in the Column Input Cell and click on OK.

3. Principal Component Data Table. Create a list of input values for the Interest Rate / Year (7.0%

and 8.0%) in the range A24:A25. Create an output formula that references the Principal

Component row by entering the formula =B13 in cell B23 and copy it to the range

C23:AE23. Select the range A23:AE25 for the One-Variable Data Table. This range includes

both the input values on the left side of the range and the output formula on the top of the range.

Then choose Data | Table from the main menu and a Table dialog box pops up. Enter the cell

address B5 (Interest Rate / Year) in the Column Input Cell and click on OK.

4. Graph. Select the range B9:AE9, hold down the Control button and keep holding it down, select

the range B18:AE19, continue holding down the Control button, and select the range B24:AE25.

Then choose Insert | Chart from the main menu. Select the XY (Scatter) chart type and make

other selections to complete the Chart Wizard.

From the graph, we see that the Interest Component is much lower at 7% than it is at 8%. Indeed you pay

$3,000 less in interest ($21,000 vs. $24,000) in year 1. The difference in interest component gradually

declines over time. The principal component nearly the same over time. The principal component is

slightly more frontloaded at 7% than at 8%. That is, $528 more of your payment goes to principal in year

1 at 7% than at 8%. Then it switches and $2,080 less of your payment goes to principal in year 30.

Problem. Examine the same 30 year mortgage for $300,000 as in the previous section. Consider what

would happen if the interest rate / year dropped from 8.00% to 7.00%. How much of each year's payment

goes to paying interest vs. how much goes to reducing the principal under the two interest rates?

Solution Strategy. Construct a data table for the interest component under the two interest rates.

Construct another data table for the principal component under the two interest rates. Create a graph of

the two interest components and two principal components.

FIGURE 5.3 Spreadsheet for Loan Amortization - Sensitivity Analysis.

How To Build Your Own Spreadsheet Model.

1. Start with the Basics Spreadsheet. Open the spreadsheet that you created for Loan Amortization

- Basics and immediately save the spreadsheet under a new name using the File | Save As

command.

2. Interest Component Data Table. Create a list of input values for the Interest Rate / Year (7.0%

and 8.0%) in the range A18:A19. Create an output formula that references the Interest

Component row by entering the formula =B12 in cell B17 and copy it to the range

C17:AE17. Select the range A17:AE19 for the One-Variable Data Table. This range includes

both the input values on the left side of the range and the output formula on the top of the range.

Then choose Data | Table from the main menu and a Table dialog box pops up. Enter the cell

address B5 (Interest Rate / Year) in the Column Input Cell and click on OK.

3. Principal Component Data Table. Create a list of input values for the Interest Rate / Year (7.0%

and 8.0%) in the range A24:A25. Create an output formula that references the Principal

Component row by entering the formula =B13 in cell B23 and copy it to the range

C23:AE23. Select the range A23:AE25 for the One-Variable Data Table. This range includes

both the input values on the left side of the range and the output formula on the top of the range.

Then choose Data | Table from the main menu and a Table dialog box pops up. Enter the cell

address B5 (Interest Rate / Year) in the Column Input Cell and click on OK.

4. Graph. Select the range B9:AE9, hold down the Control button and keep holding it down, select

the range B18:AE19, continue holding down the Control button, and select the range B24:AE25.

Then choose Insert | Chart from the main menu. Select the XY (Scatter) chart type and make

other selections to complete the Chart Wizard.

From the graph, we see that the Interest Component is much lower at 7% than it is at 8%. Indeed you pay

$3,000 less in interest ($21,000 vs. $24,000) in year 1. The difference in interest component gradually

declines over time. The principal component nearly the same over time. The principal component is

slightly more frontloaded at 7% than at 8%. That is, $528 more of your payment goes to principal in year

1 at 7% than at 8%. Then it switches and $2,080 less of your payment goes to principal in year 30.