1. Education

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 WordPerfect Quattro Pro.

Step 1: Open the software package.

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.

Step 3: In cell B4, enter the following formula:

For Excel : " =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.

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".

Step 5:
Excel 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.

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".

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.


Previous Articles

You can opt-out at any time. Please refer to our privacy policy for contact information.

Discuss in my forum

©2014 About.com. All rights reserved.