“LOAN MONTHLY PAYMENTS AND AMORTIZATION SCHEDULE”
Program Description:
“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.
This program is a workbook consisting of four (4) worksheets, described as follows:
Worksheet Name Description
Doc This documentation sheet
Loan Amort. (uniform payments) Loan amortization including extra uniform principal payments
Loan Amort. (random payments) Loan amortization including extra random or varied principal payments
Loan Comparison Analysis Loan comparison analysis for up to 4 financing scenarios
Notes on Program Usage:
- The formula for determining monthly payments for a loan is as follows:
Pmt = (Pi(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 - 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. - 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. - 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. - In all three (3) calculation worksheets, the user input data cells are always shaded in “light yellow”.
- All of the worksheets are “protected”, but NOT with a password.
- All of the worksheets are totally independent and stand alone.
- 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”.)
