The most common type of loan for personal consumers is such that the borrower makes

Project 1
Background: Amortization Schedule
The most common type of loan for personal consumers is such that the borrower makes fixed payments every period and where the payment is comprised of an interest payment and a reduction in the principal balance of the loan. An amortization schedule shows the amount of interest paid and the amount of principal balance remaining for each payment of the loan.
Objective:
Construct an amortization table for a 4 year automobile loan where the borrower pays off the loan with 48 equal monthly payments. Construct it so that the spreadsheet shows what happens when the amount borrowed or the annual interest rate changes.
What to Do:
Construct an attractive, easy to use spreadsheet that displays the amortization schedule for a particular amount borrowed and interest rate.
Each line of the amortization table should contain the following information:
-A sequence of numbers (from one to n, where n is the total number of months of the loan)
-The beginning of the period loan amount outstanding
-The monthly payment
-Interest paid that month
-Reduction in principal for that month
-The outstanding balance of the loan at the end of the month
Once you have constructed the first two lines of the table (be careful!), you should be able to copy the second line down to complete the amortization table.
Be sure that your spreadsheet is set up so that the calculations can be easily changed for a certain loan amount and interest rate (hint: these should be specific cells that you reference when making the actual table).
What to Hand In:
Deposit the spreadsheet in the Blackboard assignment link. Use the standard name convention for the spreadsheet. The file should begin with P01 and followed by your last name. Show the table for a loan of $25,000 and an annual interest rate of 11%.
Also answer the following:
1)How much total interest did the borrower pay on the loan?
2)Why may you want to know how to construct or interpret an amortization schedule? What happens if you change the input variables (length of loan, rate, or loan amount)? What problem happens with our model when the length of the loan changes?
The financial functions:
There are a number of ways of doing this assignment and you could, if you wish, use some advanced financial functions. For this assignment, however, use the payment (PMT) function.
The programmers who wrote Excel realized that its users did not want to memorize a lot of functions. Yet, the arguments and where they are placed in the function are important. For this reason, there is a way to search for the functions available in Excel. To get to this, click the ‘Formulas’ tab in Excel and hit the ‘Insert Function’ button located on the far left-hand side. The box then give you access to search for all of the Excel functions and simplifies entering the arguments for the functions. As you become more familiar with functions in Excel, you can simply enter the function into the cell with an “=FunctionName”. When entering a function, a list of possible functions are displayed once you start entering the letters in the screen after the equals sign. Then once you have entered the ( sign, if it is a valid function, the arguments for the function are displayed.
An example of a simplified of an Amortization Schedule:
Year Beginning Balance Total Payment Interest Paid Principal Paid Ending Balance 1 $ 5,000.00 $ 1,285.46 $ 450.00 $ 835.46 $ 4,164.54 2 $ 4,164.54 $ 1,285.46 $ 374.81 $ 910.65 $ 3,253.88 3 $ 3,253.88 $ 1,285.46 $ 292.85 $ 992.61 $ 2,261.27 4 $ 2,261.27 $ 1,285.46 $ 203.51 $ 1,081.95 $ 1,179.32 5 $ 1,179.32 $ 1,285.46 $ 106.14 $ 1,179.32 $ - Totals $6,427.31 $1,427.31 $5,000.00
Grading:
The following will be considered when your submission is graded:
Did you follow the instructions on the assignment sheet?
Is it easy to see what you are doing?
Did you use the power of the spreadsheet effectively? (Using formulas and cell references when you can)