## Loan Amortization.xls

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

Version History
Check the version history to see how this calculation has changed over time. Use the Subscribe button to receive an automatic email should this calculation be updated to a higher version. Use the Favourite button so you can easily find this calculation in the future.

Submitted On:
19 Nov 2010
Submitted By:
ATomanovich
File Date:
19 Nov 2010
File Author:
Alex Tomanovich
File Version:
1.6
File Size:
899.50 Kb
File Type:
xls
994
Rating:
HTML Window:
Like This?:
View the profile of the person who submitted this calculation and see all their other calculations hosted at ExcelCalcs.
Need Help?:
Use the comment feature below to raise any questions relating to this download. The question will be automatically emailed the author and all users subscribing to this comment thread.

Thanks!

#4 mohdimran1982 2009-02-01 06:26
Thank you for Useful worksheet

#3 MSH 2008-09-09 16:22
After I used, benefited, and enjoyed your spreadsheets, finally I found the time to say, "Thank you".

#2 PJay72 2008-01-26 21:53
Trying to decide whether to rent or buy. This file is very easy to use. Thanks for you help. PJay

#1 josephstrehle 2008-01-22 16:37
The Excel File is simplistic in in-put and showing all areas of cause and effects of different scenarios for the decision making process.