Pmt payment amount interest paid principal paid

This preview shows page 3 - 4 out of 4 pages.

Pmt Payment Amount Interest Paid Principal Paid Outstanding Balance 0 ±² ±² ±² 500 000 : 00 1 43 592 : 28 (500000)(0.06) = 30 000 : 00 43592.28 ² 30000 = 13 592 : 28 500000-13592.28 = 486 407 : 72 2 43 592 : 28 (486407.72)(0.06) = 29 184 : 46 43592.28 ² 29184.46 = 14 407 : 82 486407.72-14407.82 = 471 999 : 90 . . . Total 871 845 : 60 371 845 : 60 500 000 : 00 ±² 4. [6 marks ] A mortgage worth $225 000 with an interest rate of j 2 = 3% is to be repaid with payments of $1874.48 made at the end of each month and a °nal drop payment. Open the Excel template MA170wk10amortization.xls found on the MA170 lab web page. Use the following instructions to create an amortization schedule in Excel. (a) The periodic rate j 2 2 can be found in cell G2. To obtain the equivalent monthly rate, enter the formula =(1+G2)^(2/12)-1 into cell G4 and press enter. Record to your resulting interest rate below, using all decimal places provided. i : = 0 : 004938622 (b) Enter the payment amount 1874 : 48 into cell B3. To determine the interest paid with the °rst payment, enter the following into cell C3: =E2*$G$4 In cell D3, enter the formula: =B3-C3 to determine the principal paid with the °rst payment. In cell E3, enter the formula: =E2-D3 for the outstanding balance after the °rst payment . Record your results for the following: Principal paid: $763.29 Interest Paid: $1111.19 Outstanding Balance: $224236.71 (c) All payments, except for the very last payment will be a regular payment. To enter the values in for each regular payment amount , we can copy and the value in B3 to cells B4 to B184, inclusive. (Click on cell B3 and position the mouse at the bottom right corner of the cell. A black ³+" should appear. With the cursor as such, click, hold and drag the mouse down to cell B184 , and then release the mouse.) (d) For the interest paid with each payment, copy the formula in C3 to cells C4 to C185 inclusive. For the principal paid with each payment, copy the formula in D3 to cells D4 to D185 inclusive and the formula in E3 to cells E4 to E185 inclusive. (Note that your values in the row 185 will look a bit ³off" until we enter
Image of page 3

Subscribe to view the full document.

Image of page 4
You've reached the end of this preview.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern