Loan Amortization.xls
![](/components/com_remository_files/file_image_288/img_288_01.png)
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. 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 |
Calculation Preview
Full download access to any calculation is available to users with a paid or awarded subscription (XLC Pro).
Subscriptions are free to contributors to the site, alternatively they can be purchased.
Click here for information on subscriptions.
In the "Loan Comparison Analysis" worksheet, I re-formatted the appearance, and I also added plots for quick visualization and comparison of what is happening with each of the 4 possible financing scenarios.
I also added a new worksheet to the workbook, called "Home Equity Line of Credit". This worksheet creates a "HELOC" payment schedule based on a set loan term/duration. This worksheet should prove to be useful to those who wish to compare the pros/cons of a fixed rate loan versus a home equity loan.
I did not change the version number for this minor change.
By the way, getting the plot x-axis auto scaling to work like I want is still a problem for me. Where this problem becomes visually obvious is with short term loans, and the plot in the x-direction "scrunches" up. I believe that this could be corrected with the use of a macro, but I'm not going there with it.....I'm sticking with the "K.I.S.S." principle.