VIEWS: 31 PAGES: 19 CATEGORY: Business POSTED ON: 11/15/2010
Interest and Principal Formulas 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 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
"CSE 101 – Track SS – Day 19 Additional Payments Escrow Overview of Day 19 SS Test of Cumulative Interest and Cumulative Principal formulas Formatting base"