Working with Scenario Categories

One of the most powerful concepts of Quantrix is the ability to add scenario categories to a matrix to analyze different ‘What-If’ situations. In this Modeler note, we will take a simple loan amortization matrix and create two additional what-if scenarios to analyze how much interest will be paid over the life of a 10, 20 and 30 year loan. All this will be done without adding or changing one single formula!

For this Modeler note, we will be using a model file called LoanAmortizationBeforeScenario.model. Download this model file to your computer by clicking here. Once downloaded, double click on the file to open the file in Quantrix.

The loan amortization model file is made up of three matrices: Loans by Year - This matrix contains the assumptions of the loan to be analyzed. Currently, it holds the information for a single 30 year loan analysis. Interest Paid - This matrix summarizes the interest paid column in the Amort [Amortization] Schedule showing the interest to be paid over the life of the loan. Amort Schedule - A period by period breakdown of the loan showing the principal remaining, interest paid and principal paid for each loan period.

Currently, this matrix is a two-dimensional matrix analyzing a typical 30 year loan. What if you wanted to analyze a 10 year and 20 year loan side by side with the 30 year loan? Think of what you would have to do in a traditional two dimensional spreadsheet. Copy, paste and edit all the cell based formulas TWICE for each scenario. After which additional formulas will need to be written to gain any type of side by side analysis. The result would be quite static and lack the 'slice and dice' capabilities that Quantrix offers. In Quantrix, it is as easy as adding a new category for the scenarios, and linking this category to the appropriate matrices. You never have to touch a formula!

 Here are step by step instructions on how to turn your downloaded two-dimensional loan file into a multidimensional loan analysis model.

Add Scenario Category and Items for Loan Years
In the Amort Schedule matrix, click on the Items category tile. Shift-Enter the category tile to add a category. Rename the new category ‘Scenario’ click on Item A1 and rename it to 30 Year. Press Enter twice and add 2 more scenarios. Rename Scenario 2 to 20 year, Scenario 3 to 10 year.

Link Scenario Category to other Matrices
To link the Scenario category to the other matrices, simply click on the Scenario category tile and drag it from the Amort Schedule matrix to the Interest Paid matrix to just above the Paid category. Drag the Scenario category tile from the Amort Schedule and link it to the Loans by Year Matrix.

Format the Loans by Year Matrix - Click the Price row and click the $ (dollar sign) format button - Click the Down row and click the % (percentage sign) format button - Click the Loan row and click the $ (dollar sign) format button - Click the Interest row and click the % (percentage sign) format button - Click the Monthly Payment row. Choose Format --> Number from the menu. In the Number Format section of the Format Toolbox, click the Number type, and select the red negative numer indicator (1234.56). Click OK. Bold the row.

Fill in information for 20 Year and 10 Year loans: -Price of $250,000 -Down of 10% -Interest for 20 Year 5%, 10 Year 5.5% -Years of 20 for 20 year, 10 for 10 year

Your Loans by year matrix should now look like this: