1) You wish to borrow $150,000 from a lending institution for the purchase of a house. The bank will lend this amount at an Annual Percentage Rate of 4.5% to be paid-off with equal monthly mortgage payments over a 30-year period. This is a 4.5% APR, 30-year fixed-rate mortgage loan. You wish to know how this loan will affect your federal income tax burden, as only the interest paid on a home mortgage, not the principal, is tax deductible. Construct an Excel spreadsheet that: 1. Calculates your monthly payments: Calculates for each month, how much of each payment goes toward paying down the principal, and how much is interest payment. 2. Use 8-point font size in the main table, and use only 3 pages for constructing your table, with suggested margins of about 0.5 inches on all sides. Include page numbers (Insert, Footnote, etc) 3. Include your name on each page. A good way to start is: FI G J K L EEE 30000, Homework 1, Spreadsheet Basics 1) Home Mortgage Principal Borrowed Annual Interest Rate 150,000 0.045 360 EEE 30000 Your Name Num. Monthly Payments Interest Paid Principal Paid Frst Year Interest 123610.0 150000.00 Monthly Payment 760.03 Month Interest Principal New Principal 10 150000.00 Month Interest Principal New Principal Month Interest Principal New Principal 48338 276 65128624 45 2 482.34 27789 128346 77 13T3 56102 1990114940552451 23552 139632.93 481.30 278.73 128068 04 53 14980247 1.7 19827 149604.20 1562 50 197 46 52626 233.76 140103.14 47 52539 234 64 139868.50 On page 4: 2) From summing the interest payments in the table in problem 1, calculate the total interest paid on the loan. Compare this value with that calculated with the built-in function CUMIPMT. (Place your answer on page 1 also. Loans: where: and, interest due at the end of each month A payment P = principal (amount of money) r repayment multiplier i annual interest rate (as a fraction, NOT as a percentage) m number of compounding periods per year (usually 12 for house loans) n total number of payments Investments (Present Value or Present worth): (2 A where P- present value A payment out of the investment (can be interest, or interest and principal) /r capital recovery factor (or) present worth factor Excel Built-In Functions (The help windows in Excel provide additional explanation, also note the related built-in functions under the "financial" option button): Payment on a loan Present Value Cumulative Interest paid PMT( rate, nper, pv, f. type) PV(rate, nper, pmt, fv, type) CUMI PMT rate, nper, pv, start-period, end-period, type) where rate annual rate number of periodsi/m) nper total number of (compounding) periods pv = present value pmt payment fv future value (zero is the default) type when is payment made (zero end of period, 1 beginning of period) start period first compounding period in which to make calculation (can be any except the last) end period - last compounding period in which to make calculation (can be any except the first)
