How to Use Excel What-If Analysis for Complex Loan Scenarios

When negotiating a mortgage for a new house or a massive commercial loan for a business expansion, the financial variables are constantly shifting. The bank might offer you a 5.5% interest rate on a 30-year term, or a 4.2% rate if you reduce the term to 15 years and increase the down payment. Instead of building five completely separate spreadsheets to calculate the monthly payments for each scenario, you can build a single, intelligent financial model using Excel’s built-in What-If Analysis tools, specifically the Scenario Manager.

Step 1: Build the Base Financial Model

Before you can run scenarios, you need a functional calculator based on standard formulas.

1. Create a clean input section in your spreadsheet:
   • B1: Home Price (e.g., $500,000)
   • B2: Down Payment (e.g., $100,000)
   • B3: Loan Amount (Formula: =B1-B2)
   • B4: Interest Rate (e.g., 5.50%)
   • B5: Term in Years (e.g., 30)
2. Create the output calculation using the PMT (Payment) function:
   • B7 (Monthly Payment): =PMT(B4/12, B5*12, -B3)

You now have a working baseline model that shows your monthly payment based on the current inputs.

Step 2: Name Your Cells (Crucial for Scenarios)

When the Scenario Manager generates a report, it refers to cells by their absolute references (like $B$4). A report that says “$B$4 changed to 6%” is unreadable. You must define human-readable names for your input cells.

1. Click on cell B4 (the interest rate).
2. Look at the Name Box in the top left corner (directly above column A). It currently says “B4”.
3. Click inside the Name Box, type Rate, and press Enter.
4. Repeat this for the other variables (name B1 Price, B5 Years, and B7 Monthly_Payment).

Step 3: Open the Scenario Manager

Now we will save our baseline model as the first scenario.

1. Navigate to the Data tab on the ribbon.
2. In the Forecast group, click What-If Analysis and select Scenario Manager…
3. Click the Add… button.
4. Scenario Name: Type “Baseline 30-Year”.
5. Changing cells: Click and drag to select the input cells you plan on altering (e.g., B4, B5 for Rate and Years).
6. Click OK. A box will pop up confirming the current values (0.055 and 30). Click OK again.

Step 4: Add Alternative Scenarios

Now you can add the alternative offers from the bank.

1. In the Scenario Manager window, click Add… again.
2. Scenario Name: Type “Aggressive 15-Year”.
3. The changing cells will remain the same. Click OK.
4. In the Scenario Values box, change the Rate to 0.042 (4.2%) and the Years to 15. Click OK.
5. Add a third scenario named “Worst Case Market” with a Rate of 0.075 (7.5%) and Years set to 30.

Step 5: Toggle Between Scenarios

With your scenarios built, you can now instantly flip your entire spreadsheet between them.

1. In the Scenario Manager list, click on “Aggressive 15-Year”.
2. Click the Show button at the bottom of the window.

Watch your spreadsheet in the background. The input cells instantly change to the new values, and your PMT formula recalculates the new monthly payment automatically. You can jump back and forth between scenarios effortlessly.

Step 6: Generate the Summary Report

If you are presenting this to a client, a boss, or your spouse, clicking buttons on a screen isn’t enough. You need a printable comparison.

1. In the Scenario Manager window, click the Summary… button.
2. Ensure Scenario summary is selected.
3. Result cells: Ensure it has selected your output cell (B7, or Monthly_Payment).
4. Click OK.

Excel will generate a brand-new, beautifully formatted worksheet. It places your baseline and all your alternative scenarios side-by-side in columns, clearly showing how the changing inputs affect the final monthly payment. This transforms a basic calculator into a powerful financial decision-making matrix.