Www.austincc.edu



Excel Activity: Installment Loan Amortization Table Part I: PlanningNow that you’ve done an exercise creating the first few lines of an amortization table, use the image below to plan out your Excel formulas in order to create an amortization table. This table will also give you the ability to make extra payments. The six empty cells that have bold borders are the ones that need formulas. Think carefully about what cells need absolute references, what cells need relative references, and what cells, if any, need both types of references. Cell B12: =E11-F11, Cell C12: =(B12+$B$4/100)/$B$6, Cell D12: =$B$8-C12, Cell E12: =B12-D12, Cell G12: =C12, Cell G13: =G12+C13 Students have had some experience in the previous finance sections with the spreadsheets, as well as the by-hand amortization table exercise for the in-class loan problems. If they need more guidance, remind them that the interest paid is computed on the starting balance, and that the percentage used to compute interest comes from dividing the APR by the number of payment periods in a year. Since the monthly payments are fixed, the principal then comes from subtracting the interest paid from the fixed periodic payment. The remaining balance is found by subtracting the principal paid from the starting balance. Students will need to recognize that the starting balance is found by taking the previous period’s remaining balance and subtracting the extra payment. In the Accumulated Interest column in Row 12, only C12 is referenced as a starting point. Then in Row 13, students will have to recognize that we can take the previous year’s accumulated interest and add it to the current year’s interest paid. Part II: Creating the SpreadsheetNow that you have your formulas planned, open up the Excel file that contains the payment calculator and amortization table. Type your formulas into the same boxes and pull the formulas down. Did you do it correctly? Test it out entering the information from Problems 1 and 2 on the in-class loan problems. Do the numbers match the numbers from the activity? Note that Columns B through E can be pulled down together from Row 12. In Column G, only G13 should be pulled down. The spreadsheet goes down to 360 periods. Part III: Using the TableNow continue answering the questions on your In-Class Problems sheet using the payment calculator at the top of the spreadsheet and/or examining how the amounts in the table change after changing information at the top. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download