Docstoc

CSE 101 – Track SS – Day 19 Additional Payments Escrow Overview of Day 19 SS Test of Cumulative Interest and Cumulative Principal formulas Formatting base

Document Sample
CSE 101 – Track SS – Day 19 Additional Payments Escrow Overview of Day 19 SS Test of Cumulative Interest and Cumulative Principal formulas Formatting base Powered By Docstoc
					CSE 101 – Track SS – Day 19


  Additional Payments; Escrow
              Overview of Day 19 (SS)
      Test of Cumulative Interest and
      Cumulative Principal formulas
      Formatting based on conditions
      Monthly Principal vs. Interest
           Impact of Additional Principal Payments
      Escrow Accounts
           Calculating Monthly Escrow Payment
           Calculating the Escrow Account Balance
           Resetting the Escrow Account Balance
               Use of Month() and IF() functions
      Homework and BT preparation

CSE 101 - Spreadsheet Track - Day 19                 1
                          Modified
                    Amortization Schedule
      Added Cumulative Interest and
      Cumulative Principal columns
           Running totals of interest and principal paid
           to date
           Do NOT use the SUM function!
      Tested with the following data:
                  Inputs                        Outputs
   Home Cost: $120,000                 Monthly Payment: $629.29
   Down Payment: $30,000                     at payment 125:
   Interest Rate: 7.5%                 Cum. Interest: $66,062.89
   Number of Years: 30                 Cum. Principal: $12,598.74

CSE 101 - Spreadsheet Track - Day 19                                2
              Principal versus Interest
      Recall: Monthly Interest ($) = Opening
      Balance * Monthly Interest Rate
      How much of the monthly payment
      goes toward interest for:
           Payment 1 (start of loan period)
           Payment 25 (start of third year)
           Payment 121 (start of eleventh year)
      When does more of monthly payment
      go toward principal than interest?
      How can we make this easy to see?
CSE 101 - Spreadsheet Track - Day 19              3
                Conditional Formatting
      Excel makes it possible to format
      cells based on their values
      This type of Conditional Formatting
      can be accessed on the Home tab in
      the Styles group
      Let’s format the cells in the Monthly
      Principal Payment column
           As bold font, green fill with thin diagonal
           stripe pattern
           ONLY when the cell value is larger than that
           in the Monthly Interest Payment column


CSE 101 - Spreadsheet Track - Day 19                      4
         Conditional Formatting Demo
      To do this:
           First put your cursor in the first formula cell of the
           Principal Payment column
           Under Conditional Formatting, select New Rule ->
           Format only cells that contain
           Select greater than from the 2nd drop-down box
           Click in the Interest Payment cell for the same row, but
           remove the $ symbols so it is a relative reference
           Use the Format… button to select the formatting (bold
           font, green fill with thin diagonal stripe)
           Click OK, then OK again
           Finally, pull down the formula and formatting to the rest
           of the cells in the Principal Payment column
      Scroll down and check the results
           Only the cells with values satisfying the conditional rule
           should display; if not, edit the rule for the first cell, etc.

CSE 101 - Spreadsheet Track - Day 19                                        5
                            Additional
                        Principal Payments
      Let’s try speeding up when principal > interest
      Add an Additional Principal Payment column
           To hold optional Additional Principal payments
           Format these cells as Accounting
      Adjust the formulas for Cumulative Principal &
      Closing Balance to include the Additional Principal
                  Inputs                         Outputs
   Home Cost: $120,000                        at payment 125:
   Down Payment: $30,000               Cum. Interest: $65,040.96
   Interest Rate: 7.5%                 Cum. Principal: $14,620.67
   Number of Years: 30                 Closing Balance: $75,379.33
   Additional Principal Payment:       When is Monthly Principal >
   $1000 in 12th payment only          Monthly Interest now?
CSE 101 - Spreadsheet Track - Day 19                                 6
        Impact of Additional Principal
      With the extra $1000 principal
      payment in month 12, when will the
      loan be paid off?
      If instead, you paid $10 extra for the
      first 100 months, when would the
      loan be paid off?
      Why is there a difference?

      If you wanted to pay $50 extra for the
      first two years only, how would you
      set this up using only one formula?

CSE 101 - Spreadsheet Track - Day 19           7
                        Escrow Accounts
      Banks often also require monthly
      payments toward home insurance and
      property taxes, since the loan is
      secured by the house
           If Government seizes the house when taxes are
           not paid, the bank is unable to collect on loan
           If the house burns and cannot be rebuilt due to
           lack of insurance, the bank again cannot collect
      These payments go into an escrow
      account, from which the bank pays the
      property taxes and home insurance

CSE 101 - Spreadsheet Track - Day 19                          8
         Exercise on Escrow Payments
      First add the following entries to the
      Monthly Payment Sheet:
           Annual Property Tax
           Annual Insurance Payment
           Combined Annual Escrow Costs
           Monthly Escrow Costs
           Total Monthly Payment
               =Loan Payment + Monthly Escrow Costs
      Think about which are inputs and
      which are calculations
CSE 101 - Spreadsheet Track - Day 19                  9
         Escrow Exercise, continued
      Then add a new Escrow Account
      Balance column to the Amortization
      Schedule worksheet
           To hold a running total of the Monthly
           Escrow Payments
               Use the previous month’s Escrow Balance to help
               calculate the current month’s Escrow Balance
           Should be rightmost column on the sheet
           Use 3-D absolute cell references to the
           Monthly Payment Sheet to get the
           Monthly Escrow Costs

CSE 101 - Spreadsheet Track - Day 19                             10
            Escrow Exercise Test Data
      Test 1:
           Annual property taxes: $1000
           Annual insurance cost: $600
           Should have paid in $48,000 at 360th payment period
      Test 2:
           Annual property taxes: $1500
           Annual insurance cost: $850
           Should have paid in $70,500 at 360th payment period
      Consult the Instructions for Exercises link (on
      today’s Classwork page) in LON-CAPA for
      directions from the previous slides

      Save and back up your spreadsheet!

CSE 101 - Spreadsheet Track - Day 19                             11
            Modifying Escrow Balance
      Escrow funds DON’T just “sit” and
      accumulate forever
           Insurance and taxes are paid out from the
           Escrow Account yearly – often at year end
               Escrow Balance must then be “reset”

      How can we handle this situation
      in our Amortization Schedule?
           In January, the Escrow Balance should
           include only one escrow payment
           Rest of year, balance should be calculated
           as before
CSE 101 - Spreadsheet Track - Day 19                    12
               Month() Function Demo
      In an open cell to the right of your
      amortization calculations, type:
           =MONTH(B3) (or whatever cell contains
           the Payment Date for that row)
               Used to get month number of a cell with Date
               formatting
           Copy this formula down about 15 cells
           Do the resulting numbers "match" the
           months (i.e., 2 for Feb, 5 for May, etc.)?

      Now delete these formulas
CSE 101 - Spreadsheet Track - Day 19                          13
                  Functions and Escrow
      Use the IF() function to handle cases
           "Nest" the MONTH() function inside the IF()
           to test whether or not the Payment Date is in
           January
           When the month IS JANUARY, set the escrow
           balance to one monthly escrow payment
           When the month is NOT January, calculate
           the escrow balance using the same formula
           used before
           Modify the formulas in your existing Escrow
           Balance column – don’t create a new column!

CSE 101 - Spreadsheet Track - Day 19                       14
              Summary of Day 19 (SS)
      Conditional Formatting
           Formatting depends on cell values
      Additional Principal Payments
           Effect interest versus principal ratios for all
           months after the extra payments
           Impact how soon the loan is paid off
      Escrow Accounts - entail
           Calculating Monthly Escrow Payment
           Calculating Escrow Account Balances
           Resetting the Escrow Account Balance when
           insurance and taxes are paid
               Use of Month() and IF() functions


CSE 101 - Spreadsheet Track - Day 19                         15
                Graphing in Homework –
                        see Link
   In Mortgage                         In Credit Card
     spreadsheet:                        spreadsheet:
      Line Chart                        Line Chart
           Trend over time                Trend over time
      Data Series:                      Data Series:
           Interest                       New Purchases
           Principal                      Payment Amount
      X-axis: Payment Num                 Ending Balance

      Y-axis: Dollar amount             X-axis: Payment Num
      Chart on new sheet                Y-axis: Dollar amount
      Save and back up!                 Chart on new sheet
                                        Save and back up!

CSE 101 - Spreadsheet Track - Day 19                            16
                     Day 20 Bridge Task
      Individual (not group) work
      All students should come for a BT
      You will get the next BT that you
      have not yet passed
      For the 3.0 BT:
           Covers first 3 days of track material
           Be sure to have all class work and
           homework completed for the BT
           Be sure to have all financial spreadsheets
           backed up!
CSE 101 - Spreadsheet Track - Day 19                    17
               To Take a Bridge Task…
      You MUST have a
      photo ID in order to
      take ANY BT
      You MUST go to your
      assigned section to
      take in-class BTs
CSE 101 - Spreadsheet Track - Day 19    18

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:31
posted:11/15/2010
language:English
pages:19
Description: Interest and Principal Formulas document sample