Lecture Notes for Day 19

Track specific material I: Spreadsheets Scenario Analysis For homework you should have entered data into your credit card spreadsheet and modified it to handle "teaser" rates and full rates. One advantage of using a spreadsheet is that you can do scenario analysis of your data. Using your spreadsheet, you can compare different credit cards to see which card gives you the best deal. Open your spreadsheet and note the balance at the end of 6 payments and again at the end of 12 payments. Then change the interest rates as follows and note the balance at the end of the 6th and 12th payments: Test one Teaser Rate: 6.43% Full Rate: 17.00% Test two Teaser Rate: 9.00% Full Rate: 14.00% Compare the 6th and 12th balances for these three situations. Which card costs you the least interest for the purchases you made? Comparing the outcomes from different data sets is such a common use of spreadsheets that Excel provides a tool - scenario manager - to make it easier and possible for users to save certain test data sets for later reuse. To find out how Scenarios work, redo the previous comparison exercise: Use Excel HELP to find out how to Create a Scenario Create 3 different scenarios, named Card1, Card2, and Card3 Use the Teaser and Full Rates from the homework and the two test data sets for the Scenario Values in the respective scenarios note the changes in the calculated values in your spreadsheet when you SHOW each scenario Annuities and the Payment Function (PMT) One difference between an annuity and a credit card is that an annuity is a loan for a set amount of money over a fixed period of time. The required payments are the same each month. Credit cards allow you to increase the balance by adding additional purchases each month. A common type of annuity is one used to buy a house or car. We will be working on an annuity problem dealing with buying a house. The problem has 2 parts: You will first calculate the amount of the monthly payment for the mortgage. Then you will use that data to create an amortization schedule for the life of the annuity. Remember when designing a spreadsheet, you need to think about: the starting data given the results you need to calculate the intermediate results you might need built-in functions that you could use the data for your mortgage: cost of the house down payment annual interest rate number of years of payments What calculations do you need? What is the amount of the loan? How many payments will be made on the loan? What is the interest rate for the payment period? What is the monthly payment? determine all of these calculations with the data given above Create a new workbook and set up your spreadsheet to include the starting data and the calculations listed above. Calculating monthly payments on an annuity is complicated so Excel has a built-in function to handle the computation. See slide 7 for details of the PMT function. Use this function to calculate the montly payment value. Build your spreadsheet and format your cells according to the instructions on today's exercises page. Loan Amortization Schedule design and create a loan amortization schedule. It will show the following things: Monthly payment number Monthly opening balance Amount of interest paid each month Amount of principal paid each month Monthly closing balance To design your schedule, you should specify where you will obtain each of the items. You must obtain these items either from your Monthly Loan Payment worksheet or you must calculate it. Specify what numbers you would use in any calculations. You must specify where you will obtains these items for the FIRST payment and also for each SUBSEQUENT payment. On a new worksheet in the same workbook as the Monthly Loan Payment worksheet, create the amortization schedule. Name the new worksheet Amortization Schedule. Be sure that you have the following column headings in your spreadsheet: Payment number Opening balance Monthly interest payment Monthly principal payment Closing balance Your spreadsheet must reference the monthly payment that you calculated on the monthly payment sheet. You will also need to have enough rows to compute the payments for 30 years. Be sure to use the proper format in cells (dollar amounts in accounting; percentage in percent, with 2 decimal places) Build your spreadsheet and format your cells according to the instructions given in today's exercise page. If your amortization schedule is correct, you will have these results: At payment 125 closing balance: $77,401.26 interest: $484.66 At payment 360 closing balance: $0.00 Modify your amortization schedule as follows to keep track of Cumulative Interest Add a new column headed Cumulative interest it should keep a running total of the interest paid on the loan to-date (the total interest paid in all of the previous payments plus the interest paid in the current payment. Don't use the SUM function) format as accounting so that dollar values will display correctly test it with the following data: cost of home: $120,000 down payment: $30,000 annual interest rate: 7.5% number of years: 30 Your monthly payment should be $629.29. If your formulas are all correct, you should have a cumulative interest of $66,062.89 at payment 125.