- About Quantrix
- Product Overview
- Quantrix Modeler
- The Quantrix Qloud
- In-Memory Analytics
- Enterprise Integration
- Sample Screenshots
- Customer Success
- Sample Models
- Learning Overview
- Video Demos
- Webinar Series
- Quick Guide
- Online Help
- Modeler Notes
- Mortgage Modification Modeler Note
- Election Tax Model
- Marketing Response Curve Model
- Pivoting Data
- Quantrix in Corporate Budgeting
- Using Expressions in Quantrix
- Performing a Sales Analysis
- Indirect() w/Capex & Depreciation
- Analyzing ARM vs. Fixed Mortgages
- Integrated Financials Models
- Leveraging the @ and # Operators
- Using Circular References
- Converting Spreadsheets to Quantrix
- Using Functions with Black-Scholes
- Using Statistical Functions
- Using Quantrix in Capital Budgeting
- Working with Scenario Categories
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.
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.
>> Loan Amortization - Before Scenarios Model
>> Loan Amortization - After Scenarios Model