Loan Amortization.xls

"Loan Amortization" is a spreadsheet program written in MS-Excel for the purpose of determining the monthly payments (principal and interest) for a loan, as well as developing a complete amortization schedule for the given loan.  The ability to input additional monthly uniform or varied/random direct principal payments is also provided, as well as the ability to perform an analysis of up to four (4) different loan financing scenarios all at one time.  A home equity line of credit payment schedule, including extra principal payments, is also included. This program is a workbook consisting of five (5) worksheets, described as follows:


Loan Amort. (uniform payments) - Fixed loan amortization including extra uniform principal payments
Loan Amort. (random payments)- Fixed loan amortization including extra random or varied principal payments
Loan Comparison Analysis - Fixed loan comparison analysis for up to 4 financing scenarios
Home Equity Line of Credit - Home equity line of credit (HELOC) including extra principal payments

Notes on Program Usage:

1.   The formula for determining monthly payments for a fixed loan is as follows:
Pmt = (P*i*(1+i)^N)/((1+i)^N-1)
where:Pmt = monthly payment (principal & interest)
P = principal (loan amount financed)
APR = Annual Interest Rate  (Example: 5% = 5/100 = 0.05)
i = Interest rate/month = APR/12  (Example: i = 5% APR/12 = 5/100/12 = 0.004167)
N = loan term (duration) in total number of months
2.   In the "Loan Amort. (uniform payments)" worksheet, the user may input a single value of equal uniform monthly extra payments to be applied directly to the principal of the loan.  Doing this will result in both shortening the duration to pay off the loan and reduce the total interest paid on the loan.
3.   In the "Loan Amort. (random payments)" worksheet, the user may input directly into the "light yellow shaded" cells of the table any random or varied monthly extra payments to be applied directly to the principal of the loan.  Again, doing this will result in both shortening the duration to pay off the loan and reduce the total interest paid on the loan.
4.   In the "Loan Comparison Analysis" worksheet, the user may compare up to four (4) different loan financing scenarios, again with the flexibility of allowing the user to input a single value of equal uniform monthly extra payments to be applied directly to the principal of any of the loan scenarios.  In this worksheet, the user can scroll the PC display to the right to view the complete solution and amortization table for all of the scenarios, side by side.
5.   In the "Home Equity Line of Credit" worksheet, the user can select from three (3) different payment options, either 1.0%, 1.5%, or 2.0% of the remaining balance.  The interest accrued per month is equal to the Annual Percentage Rate (APR) divided by 12, then multiplied by the previous remaining balance.  The minimum monthly payment is calculated as a percentage of the remaining principal balance. The minimum monthly payment will change each month, and if one only makes the minimum monthly payment, the balance will not be zero at the end of the loan's term.
6.   In all four (4) calculation worksheets, the user input data cells are always shaded in "light yellow".
7.   All of the worksheets are "protected", but NOT with a password.
8.   All of the worksheets are totally independent and stand alone.
9.  This program contains several “comment boxes” which contain a wide variety of information including explanations of input or output items, equations used, data tables, etc.  (Note:  presence of a “comment box” is denoted by a “red triangle” in the upper right-hand corner of a cell.  Merely move the mouse pointer to the desired cell to view the contents of that particular "comment box".)

Calculation Reference
Financial Calculations | Find on Amazon.com | Find on Amazon.co.uk | Find on Amazon.fr | Find on Amazon.de | Find on Amazon.ca |