At one point in time when you took out a mortgage, a paper copy of an...

Free

90.2K

Verified Solution

Question

Finance

At one point in time when you took out a mortgage, a paper copyof an amortization table was provided to you detailing how much ofeach payment went towards the principal and how much went towardsthe interest as well as showing the remaining amount owed aftereach payment was made. Suppose that you want to purchase a home for$240,000 using a down payment as $91,088 and you finance the restof the purchase by taking out a 15-year mortgage at 4.5% interestcompounded monthly. Construct an amortization table for the entirelength of the loan making sure to include columns for the paymentnumber, the amount of the payment, the amount of each payment thatgoes towards the principal, the amount of each payment that goestowards the interest, and the amount still owed on the loan afterthe particular payment is made (you may want to create othercolumns in addition to these, but whether or not you do will bedependent on your spreadsheet set-up). A couple of tips/hints thatI will offer are that you may want to refer to the last example inthe section 11.6 and the last example in the section 6.3 lecturesfor a little help with getting the equations set up, the amountsthat go towards principal and interest will differ from one paymentto the next, and after the 180th payment has been made you shouldowe $0 on the loan.

Answer & Explanation Solved by verified expert
3.7 Ratings (606 Votes)

Amount financed = 240,000 - 91,088 = $148,912. This is Pv. Nper = 15 years*12 = 180 and rate = 4.5%/12

Amount paid each month = PMT(4.5%/12, 180, -148912) = 1139.07

The amortization table is shown below:

Payment no. Loan balance at the start of the month Amount of payment Interest Principal paid Loan balance at the end of the month
1                  148,912.00                          1,139.17      558.42                580.75          148,331.25
2                  148,331.25                          1,139.17      556.24                582.92          147,748.33
3                  147,748.33                          1,139.17      554.06                585.11          147,163.22
4                  147,163.22                          1,139.17      551.86                587.30          146,575.91
5                  146,575.91                          1,139.17      549.66                589.51          145,986.41
6                  145,986.41                          1,139.17      547.45                591.72          145,394.69
7                  145,394.69                          1,139.17      545.23                593.94          144,800.75
8                  144,800.75                          1,139.17      543.00                596.16          144,204.59
9                  144,204.59                          1,139.17      540.77                598.40          143,606.19
10                  143,606.19                          1,139.17      538.52                600.64          143,005.54
160                     22,963.44                          1,139.17         86.11            1,053.05             21,910.39
161                     21,910.39                          1,139.17         82.16            1,057.00             20,853.39
162                     20,853.39                          1,139.17         78.20            1,060.97             19,792.42
163                     19,792.42                          1,139.17         74.22            1,064.95             18,727.48
164                     18,727.48                          1,139.17         70.23            1,068.94             17,658.54
165                     17,658.54                          1,139.17         66.22            1,072.95             16,585.59
166                     16,585.59                          1,139.17         62.20            1,076.97             15,508.62
167                     15,508.62                          1,139.17         58.16            1,081.01             14,427.61
168                     14,427.61                          1,139.17         54.10            1,085.06             13,342.55
169                     13,342.55                          1,139.17         50.03            1,089.13             12,253.41
170                     12,253.41                          1,139.17         45.95            1,093.22             11,160.20
171                     11,160.20                          1,139.17         41.85            1,097.32             10,062.88
172                     10,062.88                          1,139.17         37.74            1,101.43               8,961.45
173                       8,961.45                          1,139.17         33.61            1,105.56               7,855.89
174                       7,855.89                          1,139.17         29.46            1,109.71               6,746.18
175                       6,746.18                          1,139.17         25.30            1,113.87               5,632.31
176                       5,632.31                          1,139.17         21.12            1,118.05               4,514.27
177                       4,514.27                          1,139.17         16.93            1,122.24               3,392.03
178                       3,392.03                          1,139.17         12.72            1,126.45               2,265.58
179                       2,265.58                          1,139.17           8.50            1,130.67               1,134.91
180                       1,134.91                          1,139.17           4.26            1,134.91                       0.00

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:
  • Unlimited Question Access with detailed Answers
  • Zin AI - 3 Million Words
  • 10 Dall-E 3 Images
  • 20 Plot Generations
  • Conversation with Dialogue Memory
  • No Ads, Ever!
  • Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!
Become a Member

Transcribed Image Text

At one point in time when you took out a mortgage, a paper copyof an amortization table was provided to you detailing how much ofeach payment went towards the principal and how much went towardsthe interest as well as showing the remaining amount owed aftereach payment was made. Suppose that you want to purchase a home for$240,000 using a down payment as $91,088 and you finance the restof the purchase by taking out a 15-year mortgage at 4.5% interestcompounded monthly. Construct an amortization table for the entirelength of the loan making sure to include columns for the paymentnumber, the amount of the payment, the amount of each payment thatgoes towards the principal, the amount of each payment that goestowards the interest, and the amount still owed on the loan afterthe particular payment is made (you may want to create othercolumns in addition to these, but whether or not you do will bedependent on your spreadsheet set-up). A couple of tips/hints thatI will offer are that you may want to refer to the last example inthe section 11.6 and the last example in the section 6.3 lecturesfor a little help with getting the equations set up, the amountsthat go towards principal and interest will differ from one paymentto the next, and after the 180th payment has been made you shouldowe $0 on the loan.

Other questions asked by students