Question

This is an extensive, five-part question, and requires creation of an amortization table.

Part 1

Michael and Ashley owe $35,000 on their student loans at an interest rate of 5%. The term is 20 years. Find their monthly payment. Note: Round your final answer to two decimal places. Do NOT include a dollar sign in your final answer.

Part 2

Construct the first row of the amortization table for their student loans. How much of their first payment goes toward interest? How much of their first payment goes toward principal? After making their first payment, what is the remaining balance? Note: Round your answers to two decimal places. Do NOT include dollar signs in your answers.

Part 3

Construct the second row of the amortization table for their student loans. How much of their second payment goes toward interest? How much of their second payment goes toward principal? After making their second payment, what is the remaining balance? Note: Round your answers to two decimal places. Do NOT include dollar signs in your answers.

Part 4

Continue constructing the amortization table for their student loans until you have completed 12 rows of the table. What is the total amount of interest that Michael and Ashley will pay on their student loans in the first year? Note: Round your final answer to two decimal places. Do NOT include a dollar sign in your final answer.

Part 5 Explain how you constructed the amortization table for their student loans. Include the formulas that you used. Show your calculations for at least one row of the table.

Answer 1

Solution :

Amount = 35,000 , Interest = 5% per year = .4167% per month , Year = 20, Total monthly period = 12*20 = 240

Part A ) Form monthly payment calculation PMT function of excel is used

PMT(rate, nper, pv, [fv], [type])

PMT = 230.98

Part 2)

How much of their first payment goes toward principal: 85.15

remaining balance = 35000 - 85.15 = 34914.85

Part 3 )

How much of their second payment goes toward interest: 145.48

How much of their second payment goes toward principal: 85.51

Remaining balance : 34914.85 - 85.51 = 34829.34

Part 4)

the total amount of interest that Michael and Ashley will pay on their student loans in the first year: 1726.26

Calculations are given in excel diagram

Part 5 )

Construction of amortization table

• Find EMI using PMT function

• Opening Balance	PMT	Interest	Principle	Closing
  Last year's opening balance	PMT function	= Opening balance * interest rate	PMT - interest	Opening - Principle