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

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	Description
Interest Rate	The annual interest rate of the loan
Term	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	Description
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	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	Principal portion of the P&I payment
Interest	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

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	Description
Rem. Term	The number of years remaining on the loan, not the number of payments
Balance	The remaining principal to pay off in the current loan
Rate	The annual interest rate of the current loan
Candidate Loan Rate	The annual interest rate of the proposed loan
Fees	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	Description
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
Rate	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

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	Description
Constant	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

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

Wordperfect Quattro Pro