Question

aSuppose you bought a house and took out a mortgage for $100,000. The interest rate is 3%, and you must amortize the loan over 10 years with equal end-of-year payments.

A. Calculate the mortgage payment using the Excel function

Rate	Nper	PV	FV	Payment

B. Set up an amortization schedule that shows the annual payments and the amount of each payment that repays the principal and the amount that constitutes interest expense to the borrower and interest income to the lender

Year	Begin Ammount	Payment	Interest Paid	Principal PAid	Ending Balance
1
2
3
4
5
6
7
8
9
10

Answer 1

1 solution: Part B Year Begin Ammount Payment Interest Paid Principal PAidEnding Balance 3 Part A 4 4 Rate Nper PV FV Payment 3% 101000001 01 11,723 100,000 11,723 91,27711,723 82,29211,723 73,038 11,723 63,506 11,723 53,68811,723 43,576 11,723 33,16011,723 22,43211,723 11,382 11,723 3,000 2,738 2,469 2,191 1,905 1,611 1,307 995 673 341 8,723 8,985 9,254 9,532 9,818 10,112 10,416 10,728 11,050 11,382 91,277 82,292 73,038 63,506 53,688 43,576 33,160 22,432 11,382 14 10 16 17 27 1 イト . Sheet645·Sheet646·Sheet64, eet648 Sheet649 Sheet650 Sheet651 Sheet652. Sheet653 ENG 10:17 US 25 02/2018

solution: 3 PartA 4 Rate 5 0.03 Nper PV 10 100000 0 PMT(A5,B5,-C5,D5) Part B Year Begin Ammount Payment Interest Paid Principal PAid Ending Balance FVPayment 15-15 16-L6 -SESS 1-16"SAS5-16-KG SESS 17 SAS5 17-K7 -M5 M6 M7 -M8 M9 M10 M11 M12 M13 18-L8 -19-19 SES5-19*SAS5 |=19.KY SES5 10*SAS5 110-K10110-L10 SESS 111 SA$5 J11-K11-111-L11 -SES5 |=112*SA$5-12-K12-12-L12 SES5 113 SA$5 J13-K13 113-113 SES5 114 SAS5 M13 10 12 10 114-L14 15 16 18 19 20 23 24 25 ㅖ ト 뉘 Sheet645 . Sheet 46 , Sheet647 Sheet648 . Sheet 649 Sheet650 Sheet651 , Sheet652. Sheet653 10:18 US 25/02/2018