Reference: Analyzing data
This section contains additional information related to analyzing data. For example, it contains an indepth 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 MonthYear (MMMYY).

Yr Rate

Equivalent yearly interest rate for the period; for fixedrate 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 yintercept 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

Reference: Analyzing data