Answer the "EXTENSION" portion of the work sheet

372 373 374 $ 1,101.90 172 5566.765 359 564.89 S 186 $44.16 5 350 $ (0.00 $0.00 $42.76 5 II EXTENSION: How much does paying an extra $100 per month have on the length of the loan and the total cost paid? An extra $200? A popular financial consultant recommends that you get a 15-year loan for which the payments are no more than 25% of your take home pay. According to this guideline, how much do you need to make per year (before taxes and deductions) to be able to buy this house? Scenano Suppose that you are buying a $150,000 house. You have $35,000, which will cover the $5000 closing costs and leave $30,000 for a down payment, so you will not have to buy PMI! You can get a 30-year fixed mortgage at 3.94%, or a 15-year fixed at 2.90%. You are leaning toward the 30-year loan, but want to compare the two options (monthly payment amount, total interest paid, and total cost - purchase price plus interest -of the house). At some point, you may want to investigate the how paying extra on the principle each month will affect the length of the loan and the total interest paid. The extra amount you pay may vary from month to month, so you'll need to build an amortization table. Problem Statement: Determine the number of months and total interest paid for a given payment stream. Assumptions: Nothing changes about the loan agreement for the life of the mortgage; you stay in the house until the mortgage is paid off. Model: 201) = Remaining principal amount owed on the property) after month n. Pon+1) - P(x) = 7/12*P(1) - Pmt - addl.pmt. P(0)=Price - Down or Pon+1) = (1+r/12)P(n) - Pmt -addl.pnt. P(O)=Price - Down 1. Open a new worksheet 2. Enter your name and a brief problem description 3. Enter a description of the model 4. Put in labels for the problem parameters: Price, Closing, Down, Loan, APR (annual percentage rate of interest), Term (number of months for the loan), and Payment. Use the Formulas Create from Selection menu item to define the variables for use in the formulas 4. Put in labels for the problem parameters: Price, Closing Down Loan, APR (annual percentage rate of interest). Term (number of months for the loan), and Payment. Use the Formulas Create from Selection menu item to define the variables for use in the formulas. 5. Enter the values for Price, Closing, and Down in cells B4-B6, and the annual interest rate in cell B8 6. In cell B7, put the formula for the Loan amount: =Price-Down 7. In cell B9. Put the Term (number of months for the loan): =12*30 ("30" indicates 30 year loan) 8. In cell B10. Put the formula for Payment: --PMT (APR/12, Term, Loan) 9. Create column headings for n, Balance, Interest, Pmt 10., and Addi Pmt (assume we put "n" in cell A12, "Balance" in cell B12...). Define the ranges beneath those headings (all the way down to 360 months). 11. In the next row, enter the initial condition n=0 and PCO=Loan: put 0 in cell A13 and "=Loan" in Cell B13 If your down payment is less than 20% of the property value, your lender will require you to carry private mortgage insurance (PMI). For a $150,000 house, PMI will typically cost about $97/month with 5% down payment, or $65/month with 10% down, or $44/month with 15% down AMTH 518 Lab 1: DDS (Amortization Table) 12. In cell C13, put the formula for the interest accrued that month: APR/12 Balance 12. In cell C13. put the formula for the interest accrued that month: -APR/12*Balance 13. In cell D13. put in a reference to the regular monthly payment: -Payment 14. In cell E13, put the additional amount paid that month: $0 (for now) 15. In cell B14, put the formula for the new balance: -B134C13-013-E13 16. Copy those formulas into the remaining cells below until you get to a value of that you desire. 17. In cell E4 and E5, put the labels "Total Cost:" and "Interest Paid" 18. In cell F4, put the formula for the total cost paid aum (Pmt. Addl Prat) 19. In cell F5, put the formula for the total interest paid: -sum (Interest) You can get fancier with the formulas for Balance and post by using ifo and min() functions, so that Balance never goes negative and you stop paying when the balance hits zero. This will make your life easier for doing "what if" analyses... Your spreadsheet should look something like this (note that I've hidden rows 19-370 so you can see both the start and the end of the amortization table; you should have a zero balance at the end): 5 SOMMER A FORMA DATA HVW WW COM A 7 X FI 4 SUMA D 1 Name Tom Red Problem Description Amortation Table $110,000 Total Con5305,130 Closing 55.000 Interest PS4,75 Down 50.000 200N To 130000 19 Term 10 Payment 556076 11 12 Balance interest Pt Addi $120.000.00 $14.00 556.5 10 1 $119.825 24 593.41 $56.5 15 2 5119,69.93 572 55666 372 373 374 $ 1,101.90 172 5566.765 359 564.89 S 186 $44.16 5 350 $ (0.00 $0.00 $42.76 5 II EXTENSION: How much does paying an extra $100 per month have on the length of the loan and the total cost paid? An extra $200? A popular financial consultant recommends that you get a 15-year loan for which the payments are no more than 25% of your take home pay. According to this guideline, how much do you need to make per year (before taxes and deductions) to be able to buy this house? Scenano Suppose that you are buying a $150,000 house. You have $35,000, which will cover the $5000 closing costs and leave $30,000 for a down payment, so you will not have to buy PMI! You can get a 30-year fixed mortgage at 3.94%, or a 15-year fixed at 2.90%. You are leaning toward the 30-year loan, but want to compare the two options (monthly payment amount, total interest paid, and total cost - purchase price plus interest -of the house). At some point, you may want to investigate the how paying extra on the principle each month will affect the length of the loan and the total interest paid. The extra amount you pay may vary from month to month, so you'll need to build an amortization table. Problem Statement: Determine the number of months and total interest paid for a given payment stream. Assumptions: Nothing changes about the loan agreement for the life of the mortgage; you stay in the house until the mortgage is paid off. Model: 201) = Remaining principal amount owed on the property) after month n. Pon+1) - P(x) = 7/12*P(1) - Pmt - addl.pmt. P(0)=Price - Down or Pon+1) = (1+r/12)P(n) - Pmt -addl.pnt. P(O)=Price - Down 1. Open a new worksheet 2. Enter your name and a brief problem description 3. Enter a description of the model 4. Put in labels for the problem parameters: Price, Closing, Down, Loan, APR (annual percentage rate of interest), Term (number of months for the loan), and Payment. Use the Formulas Create from Selection menu item to define the variables for use in the formulas 4. Put in labels for the problem parameters: Price, Closing Down Loan, APR (annual percentage rate of interest). Term (number of months for the loan), and Payment. Use the Formulas Create from Selection menu item to define the variables for use in the formulas. 5. Enter the values for Price, Closing, and Down in cells B4-B6, and the annual interest rate in cell B8 6. In cell B7, put the formula for the Loan amount: =Price-Down 7. In cell B9. Put the Term (number of months for the loan): =12*30 ("30" indicates 30 year loan) 8. In cell B10. Put the formula for Payment: --PMT (APR/12, Term, Loan) 9. Create column headings for n, Balance, Interest, Pmt 10., and Addi Pmt (assume we put "n" in cell A12, "Balance" in cell B12...). Define the ranges beneath those headings (all the way down to 360 months). 11. In the next row, enter the initial condition n=0 and PCO=Loan: put 0 in cell A13 and "=Loan" in Cell B13 If your down payment is less than 20% of the property value, your lender will require you to carry private mortgage insurance (PMI). For a $150,000 house, PMI will typically cost about $97/month with 5% down payment, or $65/month with 10% down, or $44/month with 15% down AMTH 518 Lab 1: DDS (Amortization Table) 12. In cell C13, put the formula for the interest accrued that month: APR/12 Balance 12. In cell C13. put the formula for the interest accrued that month: -APR/12*Balance 13. In cell D13. put in a reference to the regular monthly payment: -Payment 14. In cell E13, put the additional amount paid that month: $0 (for now) 15. In cell B14, put the formula for the new balance: -B134C13-013-E13 16. Copy those formulas into the remaining cells below until you get to a value of that you desire. 17. In cell E4 and E5, put the labels "Total Cost:" and "Interest Paid" 18. In cell F4, put the formula for the total cost paid aum (Pmt. Addl Prat) 19. In cell F5, put the formula for the total interest paid: -sum (Interest) You can get fancier with the formulas for Balance and post by using ifo and min() functions, so that Balance never goes negative and you stop paying when the balance hits zero. This will make your life easier for doing "what if" analyses... Your spreadsheet should look something like this (note that I've hidden rows 19-370 so you can see both the start and the end of the amortization table; you should have a zero balance at the end): 5 SOMMER A FORMA DATA HVW WW COM A 7 X FI 4 SUMA D 1 Name Tom Red Problem Description Amortation Table $110,000 Total Con5305,130 Closing 55.000 Interest PS4,75 Down 50.000 200N To 130000 19 Term 10 Payment 556076 11 12 Balance interest Pt Addi $120.000.00 $14.00 556.5 10 1 $119.825 24 593.41 $56.5 15 2 5119,69.93 572 55666