Simple Loan Analysis

of 07


Spreadsheet software packages are included in many of the bundled packages that are available on computers. These packages are a wonderful tool for developing tools such as a mortgage analysis sheet. Try the following to see how this can work.

Prerequisite: Spreadsheet package such as MS Excel or online tool such as Google Sheets.

of 07

Step 1.

Open your spreadsheet application.  Each of the grid boxes are referred to as cells and can be addressed as the column reference and the row reference.  i.e.,  cell A1 refers to the cell located in column A row 1. 

 Cells can contain labels ( text ), numbers ( example '23') or formulas that calculate a value. ( example ' = A1 + A2')

of 07

Step 2.

In cell A1, add the label , "Principal". In cell A2, add the label " Interest". In cell A3, enter the label " Amortization Period". In cell A4, enter the label " Monthly Payment". Change the width of this column so all labels are visible.

of 07

Step 3.

 In cell B4, enter the following formula:

For Excel and Sheets : " =PMT(B2/12,B3*12,B1,,0) " ( no quotation marks )

For Quattro Pro: " @PMT(B1,B2/12,B3*12)" ( no quotation marks )

We now have the payment that will be required for every monthly period of the loan. We can now continue to analyze the debt process.

of 07

Step 4.

In cell B10, enter the label "Payment #". In cell C10, enter the label "Payment". In cell D10, enter the label "Interest". In cell E10, enter the label "Paydown". In cell F10, enter the label " Balance O/S".

of 07

Step 5.

Excel  and Sheets Version- In cell B11, enter "0". In cell F11, enter "= B1". In cell B12 enter " = B11 + 1". In cell C12, enter " =$B$4 ". In cell D12, enter "=F11*$B$2/12 ". In cell E12, enter "=C12 + D12". In cell F12, enter"=F11 + E12 ".

Quattro Version- In cell B11, enter "0". In cell F11, enter "= B1". In cell B12 enter " B11 1". In cell C12, enter " $B$4 ". In cell D12, enter " F11*$B$2/12 ". In cell E12, enter " C12-D12". In cell F12, enter" F11-E12 ".

You now have the basics of one payment setup. You will need to copy the cell entries of B11 - F11 down for the appropriate number of payments. This number is based on the number of years in the Amortization Period times 12 to put it in terms of months. Example- a ten year amortization has 120 monthly periods.

of 07

Step 6.

In cell A5, add the label " Total Cost of Loan". In cell A6, add the label " Total Interest Cost".

Excel Version-In cell B5, enter "=B4*B3*-12". In cell B6, enter "=B5-B1".

Quattro Version-In cell B5, enter " B4*B3*-12". In cell B6, enter " B5-B1"
Try your tool by entering a loan value, interest rate and Amortization Period.  You can also 
copy down Row 12 to setup an Amortization table for as many payment periods as needed.

You now have the tools to see the amount of interest paid on a loan based on the detail provided. Change the factors to see the numbers. Interest rates and Amortization periods dramatically affect the cost of borrowing.

Watch for more business math concepts.