Excel W11

One Variable Data Table and APR

  • Definition of APR: Annual Percentage Rate, represented as a percentage, which is primarily the interest that will be paid over a year on a financial product.

    • Present value of APR: 4% (subject to change).

  • Table Structure:

    • Heading Explanation: Indicates which input variable is changing, specifically APR in this case.

    • Description Under Heading: "Calculated results with the changing APR."

    • Involvement of APR in Calculations:

      • It is the input variable impacting several derived values:

        1. Periodic Rate: Influenced by APR; monthly calculations require division by 12:

          • Formula: extPeriodicRate=racextAPR12ext{Periodic Rate} = rac{ ext{APR}}{12}

        2. Monthly Payment Function: Receives periodic rate as input.

        3. Total Amount to Repay Loan: Dependent on monthly payment value.

        4. Total Interest Paid Over Loan Duration: Dependent on total amount to repay.

  • Dependencies Highlighted:

    • Four key calculations impacted by changing APR:

      1. Periodic Rate

      2. Monthly Payment

      3. Total Amount to Repay

      4. Total Interest Paid

      • Indicates significant dependency of these calculations on a single variable (APR).

Structure of One Variable Data Table

  • Table Area Description:

    • Formatted and Complete: Includes multiple cells organized under headings, necessary for data analysis.

    • Top-Left Corner Cell: Often left blank in one-variable data table; serves no purpose.

    • Substitution Values Area: Where various changing values will be populated—primarily the APR.

  • Usage of Substitution Values:

    • Initial value for APR: 4%.

    • Incremental changes in APR recommended:

      • Step Value: 0.25%

      • Maximum (Stop Value): 8% (can be adjusted to 6% if needed).

  • Cell Formatting for Clarity:

    • Use custom formatting to label derived calculations (e.g., monthly payment).

    • Formula adjustment required in calculations based on new APR values.

Financial Context Related to APR

  • Current Economic Indicators:

    • Inflation data released by Bank of Canada impacts interest rate decisions.

    • Decisions consider job numbers, employment rate, GDP growth, and inflation.

  • Implications of Interest Rate Changes for Consumers:

    • Variable Mortgage Holder's Insights:

      • If rates are steady, monthly payments remain unchanged.

      • If rates increase, monthly payments also increase due to the direct link between lending rates and personal loan rates.

      • Conversely, a decrease in rates results in lower monthly payments.

  • Illustration of Interest Rate Volatility (2022-2023):

    • Rate changes often in increments of 0.25%, escalating to 1% during significant adjustments.

Creating the One Variable Data Table

  • Selection of Calculation Structures:

    • Calculations included in the table for changing APR: monthly payment, total loan repayment, total interest paid.

    • Since only three calculations can fit into the structure due to spatial constraints, periodic rate is excluded from outputs.

  • Output Population Process:

    • Steps to visually create the table:

      1. Selecting the Area: Include substitution values and calculation areas.

      2. Navigate to Data Tab and Data Table: Determine input cell for APR and set up a data table appropriately.

      3. Confirm Input Cell Determination: For the column setup, identify APR input cell in the worksheet (e.g., B4).

  • Verification of Table Accuracy:

    • Check outputs against calculated values for a reference APR of 4%.

      • Example Validations:

        • Monthly payment: Expected outcome should align with calculated results.

        • Total to repay loan: Validate through similar calculations for accuracy confirmation.

Summary and Finalization of the Table

  • Final Adjustments and Formatting:

    • Custom number formatting applied to make results user-friendly and comprehensible.

      • Example: Change monetary figures to currency format, fitting appropriate column widths to avoid "####" error due to inadequate space.

  • Completion of Table Structure:

    • Ensure logical representation of results for user interaction, especially for financial advising contexts.

Two Variable Data Table Construction

  • Introduction of Two Input Variables:

    • Changing values of APR and corresponding cost of home.

    • Monthly payment calculation dependent on both inputs allows comprehensive examination of scenarios.

  • Populating Two Variable Data Table:

    • Structured by defining substitution values for APR in one column and cost of the home in one row.

    • Example Cost Structures:

      • Initial: 150,000, potential increments by 50,000 for analysis.

  • Identification of Input Variables:

    • Confirming cost references and APR reference should be set correctly for substitutive analysis.

      • Consistent checking against predetermined outputs is critical to ensure accuracy.

  • Finalizing Two Variable Data Table:

    • Confirm monthly payment outputs against standard references to validate calculations.

    • Address any discrepancies through iterative checks and adjustments in the calculations set up.