IDSB Banner

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:



Format and Analyze Interest Paid Matrix

Now double-click the Interest Paid matrix in the Model Browser to bring it to the front. Click the Interest Paid row, and click the $ (dollar sign) button on the format toolbar. At this point it is easy to compare interest figures for each type of loan. Your interest matrix should look like this:



Review and Analyze the Amortization Schedule Matrix

Double-click the Amort Schedule matrix in the Model Browser to bring it to the front. Note how the amounts in each column have changed. Drag and drop tiles among axis to easily compare scenarios. Double-Click on the View By Payment view in the Model Browser to bring it to the front. Make the columns bigger to fit all the information. Click on the drop down list to view a payment for each scenario.

All this was done without changing a single formula in Quantrix! Additionally, this entire matrix is calculated by only 9 readable formulas. Doing this exercise in a traditional two dimensional matrix would take over 1,800 cell-referenced formulas.

Downloads:

   >> Loan Amortization - Before Scenarios Model

   >> Loan Amortization - After Scenarios Model



idbs_banner_960x41
About Quantrix - Contact - Site Map - Refer A Friend - Support
Solutions - Products - Services - Community - Partners - Webinars - Learning - Store
© Copyright 2014 Subx Inc.-All rights reserved -+1.207.775.0808 -