Documentation Guide
for
Loan Comparison
August 2001
Loan Comparison: Program Overview
The loan comparison analysis program is used to compare two (or more) loan alternatives that may differ in terms of their interest rates, fees and costs, payment schedules, variable rate features, and other terms. Additionally, it can be used to evaluate a refinancing alternative against an existing loan. These two options will be explained separately.
Upon entering the required inputs, three types of reports are produced: cash flow reports for both loans, input and output summaries comparing data for both loans, and graphs showing the present values of the outflows for both loans, as well as the present value benefits of the preferred loan.
The information needed to run this program can come from:
- An individual’s farm records.
- Approved loan information from bank.
- Current loan records (including balance, interest rate, and schedule of interest and principal payments).
To make use of this program, you must be able to provide financial and tax information. Below is a list of the required information needed for each section.
- For the loan comparison alternative, the following information is required: loan terms, rate terms, and fees and costs for both of the loans, marginal tax rate, discount rate, and the evaluation horizon.
- For the refinancing alternative, the following information is required: current loan information, loan terms, rate terms, fees and costs for both of the loans, marginal tax rate, discount rate, and the evaluation horizon.
Navigating the Loan Comparison Analysis program
The Loan Comparison Analysis program has a set of buttons and pull-down tabs that help the user move navigate among screens and reports. The navigation tools are shown below.
The compare two loans button takes you to the input screens where all applicable information about the two loans is entered.
The evaluate refinancing button takes you to the input screens where all applicable information about the current loan and potential new loan is entered.
The drop-down tab entitled “select report to print” is used for printing the output reports. This is done by clicking on the arrow, scrolling down to the report you’d like to print until it is highlighted, and then clicking on it.
The drop-down tab entitled “select report to view” is used for accessing the output reports, such that they appear on the computer screen. This is done by clicking on the arrow, scrolling down to the report you’d like to print until it is highlighted, and then clicking on it.
There are tabs at the bottom of the Excel spreadsheet screen that help navigate the user between the input and output worksheets. Click on the name of the screen you wish to view. The tabs are called:
Main Loan Compare takes you to the main menu. The main menu screen is shown above.
Alternative 1 takes you to the cash flow output report for the first of the two loans being analyzed.
Alternative 2 takes you to the cash flow output report for the second of the two loans being analyzed.
Summary takes you to the output report that provides a comparison of the inputs and outputs for each loan, as well as charts and diagrams.
How to use the Input Sections
To explain the input required to use this program, a case study of Farmer’s State Bank vs. Farmer’s National Bank has been developed. As you read, the case study is presented such that the input sections are filled in.
This program contains two different analysis options: compare two loans and evaluate refinancing.
- The “compare two loans” option was developed to help you decide which of two loans is financially preferred when you have two financing options available for an upcoming purchase.
- The “evaluate refinancing” option was developed to help you decide to keep a current loan or refinance. The idea of refinancing can be triggered by a number of causes. One common reason for refinancing is because interest rates drop. By refinancing, the amount of interest due is reduced, leading to reduced payments.
The analysis options will be explained in two different sections of this documentation. Each section will contain a complete description of the inputs required, as well as the output reports generated. Some of the information may be repetitive between the two sections; however, an entire explanation from start to finish is helpful.
Section 1: Compare Two Loans
The “compare two loans” section collects the terms, costs, and fees associated with the two loans that are being considered for a new purchase. A visual and numerical comparison of the present values and cash flows for each loan is generated.
There is one input screen that contains 4 tabbed input sections, as well as a calculation section. The tabbed input sections are labeled:
- Loan Terms;
- Variable Rate Terms;
- Fees and Costs;
- Analysis Information.
The calculation section of the input worksheet is called Effective Rate and Net Present Value. Each of these sections is described in the following text.
Reports and Graphs
The Loan Comparison Analysis program contains 4 sections of reporting that provides both reports and graphs. The sections are called:
- Main-Loan Compare.
- Alternative 1.
- Alternative 2.
- Summary.
The sections can be accessed in two ways. From the main menu, you may use the drop-down tab entitled “select report to view”. Alternatively, you may click on the sheet tab located at the bottom of the Excel screen. The sheet tabs are named as the sections described above.
Report 1: Main-Loan Compare
The Main-Loan Compare provides a visual and description comparison of the two loans.
The preferred loan is defined at the top of the report. The report provides the difference in the net present values between the two loans. Net present value (NPV) is defined as “the value of future payments/expenses today”. It is preferred to want a higherNPV in terms of worth or a lowerNPV in terms of expenses. This report also explains which loan is preferred according to the investment horizon. For example, Loan A is preferred from year 1 to 7, while Loan B is preferred in years 7 to 10. If you plan on repaying the loan in 7 years, Loan A is the better choice. However, if you plan on repaying the loan in 8 years, Loan B is the better choice.
Graphically, the program shows the present value of the outflows, or payments for each loan. In this case, the higher the line, the more costly the loan is. The preferred loan is the lower of the two. The present value benefit of Loan 1 for the given investment horizon is graphically shown also. This graph shows the monetary value comparison for Loan 1 and Loan 2, in terms of Loan 1. In this example, Loan 1 is preferred in years 1 – 7, however, in year 7, Loan 2 is preferred. This is represented by Loan 1’s benefit values turning negative.
Report 2: Alternative 1 and Report 3: Alternative 2
The Alternative 1 and Alternative 2 reports provides both a summary of the inputs entered for the loan, as well as a payment schedule for the loan. This payment schedule includes: balance, payment amount, the amount of the payment that goes towards interest, the amount of the payment that goes towards the principal, and the ending balance after accounting for the payment.
Included in this report are: lifetime interest paid, deductible costs (when applicable), first year nondeductible costs (when applicable), after-tax cash outflow, present value, and cumulative present value with loan.
Report 4: Summary
The summary report provides a numerical side-by-side view of the inputs and outputs for each loan.
The two graphs below are similar to the graphs in the “Main-Loan Compare” report. The top graph provides an explanation of which loan is recommended for the length of the evaluation horizon. Also included is the difference in the net present values for each loan.
The bottom graph provides a visual of the present value of the preferred loan.
Section 2: Evaluate Refinancing
The “evaluate refinancing” section compares the terms, balance, and payments of a current loan to the terms, costs, and fees of a new loan. This program provides a visual and numerical comparison of the present values and cash flows for each loan.
There is one input screen that contains 5 tabbed input sections, as well as a calculation section. The tabbed input sections are entitled:
- Current Loan
- Loan Terms
- Variable Rate Terms
- Fees & Costs
- Analysis Information
The calculation section of the input worksheet is called Effective Rate and Net Present Value. Each of the sections is described in the following text.
Reports and Graphs
The Evaluate Refinancing program contains 4 sections of reporting that provides both reports and graphs. The sections are called:
- Main-Loan Compare.
- Alternative 1.
- Alternative 2.
- Summary.
The sections can be accessed in two ways. From the main menu, you may use the drop-down tab entitled “select report to view”. Alternatively, you may click on the sheet tab located at the bottom of the Excel screen. The sheet tabs are named as the sections described above.
Report 1: Main-Loan Compare
The Main-Loan Compare provides a visual and description comparison of the two loans.
The preferred loan is defined at the top of the report. The report provides the difference in the net present values between the two loans. Net present value (NPV) is defined as “the value of future payments/expenses today”. It is preferred to want a higherNPV in terms of worth or a lowerNPV in terms of expenses. This report also explains which loan is preferred according to the investment horizon. For example, Farmers National Bank is the preferred loan throughout the analysis.
Graphically, the program shows the present value of the outflows, or payments for each loan. In this case, the higher the line, the more costly the loan is. The preferred loan is the lower of the two. The present value benefit of Loan 1 for the given investment horizon is shown also. This graph shows the monetary value comparison for Loan 1 and loan 2, in terms of Loan 1. In this example, Loan 1 (Farmers State Bank) is not preferred during any of the analysis. This is shown by the negative values.
Report 2: Alternative 1 and Report 3: Alternative 2
The Alternative 1 and Alternative 2 reports provides both a summary of the inputs entered for the loan, as well as a payment schedule for the loan. This payment schedule includes: balance, payment amount, the amount of the payment that goes towards interest, the amount of the payment that goes towards the principal, and the ending balance after accounting for the payment.
Included in this report are: lifetime interest paid, deductible costs (when applicable), first year nondeductible costs (when applicable), after-tax cash outflow, present value, and cumulative present value with loan.
Report 4: Summary
The summary report provides a numerical side-by-side view of the inputs and outputs for each loan.
The two graphs below are similar to the graphs in the “Main-Loan Compare” report. The top graph provides an explanation of which loan is recommended for the length of the evaluation horizon. Also included is the difference in the net present values for each loan.
The bottom graph provides a visual of the present value of the benefit of Loan 1.