Question

please show work. Thank you

3. You need to work on the mini-projects in Excel. Your final mini-project report should be well- organized and typed in a Word file. Are required to submit both your Word report and Excel files to the instructor using the mini-project submission tool in Blackboard. The style and organization of the project accounts for 10 points. 4. You are required to work on the work independently. The due date for the project is 11:59pm on Sunday, December 1st. Mini-Project 1: Mortgage Loan Analysis (50 points): You plan to buy a house in December 2019. The sale price is $385,000. You need to pay 20% down payments and borrow additional 80% from Bank of America with a 25- year, 3.0% fixed-rate mortgage loan. You are expected to pay an equal MONTHLY payment starting from January 2020 for a total of 25 years. (1) Calculate your expected monthly mortgage payment. (2) Develop the 2020-2044 amortization table for your mortgage loan. (1) When you prepare your 2020 tax filing, what

Answer 1

(1)

Monthly mortgage payment is calculated using PMT function in Excel :

rate = 3% / 12   (converting annual rate into monthly rate)

nper = 25*12 (25 year loan with 12 monthly payments each year)

pv = 385000 * 80% (loan amount = sale price * 80%)

PMT is calculated to be $1,460.57.

(2)

Interest in any month = principal outstanding at beginning of month * 3% / 12.

Principal portion of monthly payment = monthly payment minus interest portion of payment.

principal outstanding at end of month = principal outstanding at beginning of month minus principal portion of monthly payment.

A B C D E F G H $1,460.57 Principal Principal outstanding outstanding 1 Month at beginning Payment Interest Principal at end 1 $308,000 $1,461 $770 $691 $307,309 2 $307,309 $1,461 $768 $692 $306,617 3 $306,617 $1,461 $767 $694 $305,923 4 $305,923 $1,461 $765 $696 $305,227 5 $305,227 $1,461 $763 $698 $304,530 6 $304,530 $1,461 $761 $699 $303,831 7 $303,831 $1,461 $760 $701 $303,130 8 $303,130 $1,461 $758 $703 $302,427 9 $302,427 $1,461 $756 $705 $301,722 $301,722 $1,461 $754 $706 $301,016 11 $301,016 $1,461 $753 $708 $300,308 12 $300,308 $1,461 $751 $710 $299,598 13 $299,598 $1,461 $749 $712 $298,887 14 $298,887 $1,461 $713 $298,173 15 $298,173 $1,461 $745 $715 $297,458 16 $297,458 $1,461 $744 $717 $296,741 17 $296,741 $1,461 $742 $719 $296,023 18 $296,023 $1,461 $740 $721 $295,302 19 $295,302 $1,461 $738 $722 $294,580 21 20 $294,580 $1,461 $736 $724 $293,856 21 $293,856 $1,461 $735 $726 $293,130 10 $30 $747

24 26 Principal Principal outstanding outstanding 1 Month Jat beginning Payment Interest Principal at end $293,130 $1,461 $733 $728 $292,402 23 $292,402 $1,461 $731 $730 $291,672 $291,672 $1,461 $729 $731 $290,941 $290,941 $1,461 $727 $733 $290,208 $290,208 $1,461 $726 $735 $289,473 $289,473 $1,461 $724 $737 $288,736 28 $288,736 $1,461 $722 $739 $287,997 $287,997 $1,461 $720 $741 $287,256 $287,256 $1,461 $718 $742 $286,514 $286,514 $1,461 $716 $744 $285,770 $285,770 $1,461 $714 $746 $285,024 $285,024 $1,461 $713 $284,276 34 $284,276 $1,461 $711 $750 $283,526 $283,526 $1,461 $709 $752 $282,774 $282,774 $1,461 $707 $754 $282,020 $282,020 $1,461 $705 $756 $281,265 $281,265 $1,461 $703 $757 $280,507 $280,507 $1,461 $701 $759 $279,748 40 $279,748 $1,461 $699 $761 $278,987 41 $278,987 $1,461 $697 $763 $278,224 43 42 $278,224 $1,461 $696 $765 $277,459 30 31 32 33 $748 35

A B C D E F 281 281 $71 285 286 285 $57 $50 289 Principal Principal outstanding outstanding 1 Month at beginning Payment Interest Principal at end 280 $29,844 $1,461 $75 $1,386 $28,458 282 $28,458 $1,461 $1,389 $27,069 282 $27,069 $1,461 $68 $1,393 $25,676 $25,676 $1,461 $64 $1,396 $24,280 $24,280 $1,461 $1,400 $22,880 $22,880 $1,461 $1,403 $21,477 287 286 $21,477 $1,461 $1,407 $20,070 287 $20,070 $1,461 $1,410 $18,659 288 $18,659 $1,461 $1,414 $17,245 290 289 $17,245 $1,461 $1,417 $15,828 290 $15,828 $1,461 $40 $1,421 $14,407 $14,407 $1,461 $1,425 $12,982 292 $12,982 $1,461 $1,428 $11,554 $11,554 $1,461 $1,432 $10,123 295 294 $10,123 $1,461 $1,435 $8,687 295 $8,687 $1,461 $1,439 $7,248 297 296 $7,248 $1,461 $1,442 $5,806 297 $5,806 $1,461 $1,446 $4,360 299 298 $4,360 $1,461 $11 $1,450 $2,910 299 $2,910 $1,461 $7 $1,453 $1,457 301 300 $1,457 $1,461 $1,457 ($0) 291 $18 298 $15

с D E 2 =PMT(3%/12,25*12,-385000*80%) nmin -F7 00 -F10 1 Month Principal outstanding at beginning Payment Interest =385000*80% =$H$2 =B2*3%/12 =F2 =$H$2 =B3*3%/12 =F3 =$H$2 =B4*3%/12 =F4 =$H$2 =B5*3%/12 =F5 =$H$2 =B6*3%/12 =$H$2 =B7*3%/12 =$H$2 =B8*3%/12 =F8 =$H$2 =B9*3%/12 =F9 =$H$2 =B10*3%/12 =$H$2 =B11*3%/12 =F11 =$H$2 =B12*3%/12 13 12 =F12 =$H$2 =B13*3%/12 =F13 =$H$2 =B14*3%/12 =F14 =$H$2 =B15*3%/12 =F15 =$H$2 =B16*3%/12 =F16 =$H$2 =B17*3%/12 =F17 =$H$2 =B18*3%/12 =F18 =$H$2 =B19*3%/12 =F19 =$H$2 =B20*3%/12 =F20 =$H$2 =B21*3%/12 =F21 =$H$2 =B22*3%/12 12 11 Principal =C2-D2 =C3-D3 =C4-04 =C5-D5 =C6-D6 =C7-D7 =C8-D8 =C9-D9 =C10-D10 =C11-111 =C12-D12 =C13-D13 =C14-D14 =C15-D15 =C16-D16 =C17-D17 =C18-D18 =C19-D19 =C20-D20 =C21-D21 =C22-D22 Principal outstanding at end =B2-E2 =B3-E3 =B4-E4 =B5-E5 =B6-E6 =B7-E7 =B8-E8 =B9-E9 =B10-E10 =B11-E11 =B12-E12 =B13-E13 =B14-E14 =B15-E15 =B16-E16 =B17-E17 =B18-E18 =B19-E19 =B20-E20 =B21-E21 =B22-E22