Reference: Analyzing data

This section contains additional information related to analyzing data. For example, it contains an in-depth explanation about interpreting an amortization table, and a mortgage refinancing table.

You will also learn about understanding the regression output cells and about the different options available with the Optimizer.

Interpreting an amortization table

Wordperfect Quattro Pro btnbacktotopproc Reference: Analyzing data

You can generate an amortization schedule, showing principal and interest payments, as well as interest paid from start to date and from year to date.

The amortization table consists of two sections. In the top section you can input values related to your mortgage. You can change any of these input values to customize the bottom section of the table.

Column name
Interest Rate
The annual interest rate of the loan
Number of years of the loan, not the number of payments
Original Balance
Money originally borrowed
Ending Balance
The amount of principal remaining to pay off at the end of the loan
Last Year
The number of years to show in the table
1st PMT
The first payment date for the mortgage

The bottom section of the table consists of 10 columns:

Column name
Pmt #
Number of payments from the first payment to the last payment of the loan or to the last one in the last year specified by Last Year
Date on which the corresponding payment number is due. Mortgage payments are typically due on the first day of the month following the month covered by the payment. The default date format is Month-Year (MMM-YY).
Yr Rate
Equivalent yearly interest rate for the period; for fixed-rate loans, this rate does not change; for variable rate loans, specify an estimated average interest rate when you create the table, then replace individual monthly rate values later by overwriting the calculated values.
P&I Payment
Shows the principal and interest payment for the corresponding payment period. This will not vary for fixed loans, but will vary with variable interest loans or other variable loans such as graduated payment loans. In the case of “graduated payment” loans, you can type in the actual P&I payment to replace the calculated value. Even if this results in a negative amortization, the New Balance and other columns calculate correctly. Negative amortization occurs when the P&I payment is insufficient to cover the interest charged for the period, and so the loan balance increases by the shortage amount.
Principal portion of the P&I payment
Interest portion of the P&I payment
Extra Prin
Amount of extra principal per pay period. If you intend to pay extra principal for some portion of the loan, enter the amount in this column at the row of the corresponding payment number. The extra principal amount is included in every row that follows. If extra principal payments vary from month to month, type in the actual extra principal amount wherever it changes.
New Balance
Balance after each payment. The balance decreases with each payment and eventually reaches zero (or Ending Balance, if it is not zero). The amortization table stops short of zero if Last Year precedes the end of the loan.
Cum. Interest
Cumulative interest paid through the corresponding payment
Yearly Total Int
Cumulative interest paid for each calendar year. Use this column to calculate the yearly interest paid for tax purposes.

Interpreting a mortgage refinancing table

Wordperfect Quattro Pro btnbacktotopproc Reference: Analyzing data

The mortgage refinancing table provides you with information about refinancing a mortgage and lets you compare your current loan with candidate loans. The mortgage refinancing table consists of two sections: the Current Loan section and the Candidate Loans section.

The Current Loan section takes information about your present mortgage from the Analysis Experts dialog box.

Column name
Rem. Term
The number of years remaining on the loan, not the number of payments
The remaining principal to pay off in the current loan
The annual interest rate of the current loan
Candidate Loan Rate
The annual interest rate of the proposed loan
The amount of percentage fees for the proposed loan

The Candidate Loans section takes information about the rates and terms of different mortgages from the Analysis Experts dialog box. The table calculates the gross savings by showing how different the monthly payment is for the candidate loan compared to the current loan. It also calculates the net savings, by determining the amount of money saved.

The Candidate Loans section of the table consists of 19 columns:

Column name
Loan Description
Name of the candidate loan
Current Balance
Balance of the current loan
Cash Out
Cash removed from equity (a positive value), or cash paid up front into the loan to pay down the balance or to prevent financing or loan fees (a negative value)
Pct Fees
Percentage fees (points) charged by the lender (for example, 1.75%)
Fixed Fees
Fixed fees involved in the refinancing; if a loss (for example, a loan prepayment penalty), enter a positive value; if a gain (for example, a prorated return of prepaid Private Mortgage Insurance from payoff of current loan), enter a negative number
Loan Amount
Amount of the candidate loan
Annual interest rate of the candidate loan
Term (yrs)
Loan term in years
Future Value
Future value of the candidate loan; enter a positive value if the loan requires a balloon payment
P&I Payment
Principal and interest payment for each period
Fixed mo. Loan Fees
Fixed monthly loan fees (such as private mortgage insurance)
Loan Pmt
Monthly payment (Loan Pmt = P&I Payment + Fixed Mo. Loan Fees)
Savings: Gross
The difference in the monthly payment of the candidate loan and the current loan; if negative, the candidate’s monthly payment is higher; if positive, the candidate’s monthly payment is lower and all gross savings are applied as extra principal
Savings: Term +/-
Portion of gross savings due to lengthening the term of the loan; if the term is lengthened, Term +/- is negative; if the term is shortened, Term +/- is positive
Savings: Cash I/O
Portion of gross savings due to closing costs paid up front (a negative number), or the amount of gross savings that has already been reduced by cash taken out (a positive number enclosed by “><”, which does not affect the Net amount)
Savings: Net
Sum of savings from Gross, Term +/-, and Cash I/O
Payback: When
The initial estimate of the number of years it will take for the candidate loan to pay for the cost of refinancing; displays “Never” if the refinance loses money
Payback: Loan Life
The number of years you plan to keep the property before selling it or refinancing
Payback: Bal chg
The difference in the balance at the end of Loan Life of the candidate loan and the current loan due to extra principal paid on the candidate loan; a negative number represents money saved

Understanding regression output cells

Wordperfect Quattro Pro btnbacktotopproc Reference: Analyzing data

The regression output is nine rows deep and three columns wider than the number of columns in the independent cells. Make sure to leave enough blank space because any underlying data will be overwritten. The output cells contain the following information:

Output Cell
The y-intercept value, zero if Y Intercept is set to Zero instead of Compute
Std Err of Y Est
The estimated standard error of y values; the degree of deviation of observed y values from predicted values
R Squared
The variance; the degree of relationship between independent and dependent variables. With one independent variable, R Squa
red is the square of the correlation between the two variables.
No. of Observations
The number of values for each variable; the number of rows in the regression table
Degrees of Freedom
The number of observations minus the number of independent values being computed by the regression. With Y Intercept set to Zero, Degrees of Freedom = (No. of Observations) – (number of independent variables); with Y Intercept computed, Degrees of Freedom = (No. of Observations) – (number of independent variables + 1).
X Coefficient(s)
The regression coefficients of the independent (x) variables; the slope of the regression line representing the relationship between each independent variable and the dependent variable.
Std Err of Coef.
An error estimate of the X Coefficient above it. Interpret each coefficient as the X Coefficient value plus or minus the Standard Error of Coefficient.

Specifying the Optimizer tool options

Wordperfect Quattro Pro btnbacktotopproc Reference: Analyzing data

When you produce an optimizer report, you can specify the following options:

Assume linear model (use Simplex Method) — lets you use a linear method to solve the problem by indicating that the problem has only linear expressions in the solution cell or any of the constraint cells
Show iteration results — lets you pause between each iteration, or trial, to check the progress of the search. You can either stop or continue the search after each iteration.
Automatic scaling of variables — lets you scale variables to achieve the target value
Maximum time (in seconds) — lets you indicate the maximum amount of time to find the best solution to the problem
Maximum number of iterations — lets you set the maximum number of iterations, or trials, that Optimizer can perform to find the best solution
Allowed solution precision — lets you control the accuracy of the solution
Allowed solution tolerance — lets you indicate the maximum percentage by which a solution can differ from a theoretical optimum integer solution. As you increase the tolerance setting, solutions are produced more quickly; however, they may be less accurate.
Estimates — lets you select the approach for obtaining initial estimates of the basic variables in each iteration
Derivatives — lets you select the Forward or Central differencing for estimates of partial derivatives
Search — lets you select a method for computing the search direction

Reference: Analyzing data