Question

Need to generate Amortization Table in Excel for the following problem. The correct answers are given but they need to be generated using Financial Functions.

Marley has been saving and is soon planning to buy a house. The maximum amount she wants to mortgage is $220,000 and is considering either a 20 or 30 year mortgage. Assume payment is made at beginning of the period 1) What will her payment be for a 20 or 30 year mortgage. Assume a fixed rate mortgage of 4% and 4.5% respectively. 2) Complete an amortization table for each morteage. 3) What is the accumulated interest that Marley will pay over the life of each mortgage? 4 Just in Case Marley has opted for the 30 year mortgage, but i committed to investing the payment difference. Assuming an 8% annual return, wil she be able to pay off the mortgage at 20 years? use a a viookup and if statement to help provide your answer 5) Complete a one way data table to help calculate the difference at different rates of return. What is the minimum that she neds to earn? Requirement: Use financial functions where applicable 20 Year 30 Year Can she pay it off? Hint: Use if statement Data Table 22,216 49,166) 42,728) 35,346) (26,864) 17,101 Amount 220,000220,000 Rate 4.00% 4.50% 30 Rate ears Payments per Year Total Payments Payment Differential Future Value Balance at 240 payments 20 12 Payments per Year Total Payments Monthly Payment Accumulated Interest Accumulated Principle Total Paid 360 $1,329 $98,895$179,796 220,000 $220,000 $318,895 $399,796 $129,372 107,156 Hint: Use Vlookup here 15 Year Mo Amortization 30 Year Mort Payment Amortization Principle Beginnine Balance Principle Ending Balance 218,889 218,600 218,309 218,017 217,724 217,430 217,135 Beginning Balance Payment Interest Interest 220,000 | 1,329 218,671 1,329 729 $600 S 218,071 1,329 $ 217,470 S 1 216,866 1,329 $ 216,260 1,329 $ 215,652 1,329 $ 218,671 218,071 217,470 216,866 216,260 215,652 215,042 220,000 218,889 S 218,600 S 218,309 $ 218,017 $ 217,724 $ 217,430 $ 1,111 S 1,111 S 821 S 1,111 819 S 1,111 $ 816 S 727 $ 291 $ 293 $ 295 $

Answer 1

Can she pay off Yes rate 2 Mortage amt 3 Rate 4 Years 5 Payment p 6 Total 7 Monthly payment PMT(B3/B5,B6,B2,0,1) 0.04 20 0.045 30 12 0.08 20 ear 12 nts B4 85 Payment p Total payments F4 F5 payment different 87-C7 future value Bala at 240 Difference ear 12 -PMT(C3/c5,c6,C2,0,1) Acc interest 9 Acc 10 total paid --CUMIPMT (B3/B5,B6,B2,1,B6,1 CUMIPMT(C3/C5,C6,C2,1,C6,1) -CUMPRINCIB3 B5B6B2,1A6,1) --CUMPRINE(C3C5,C6,C21,C6, 11 107156 12 13 14 1 15 2 16 3 17 4 18 5 19 |б 20 7 21 15 year amortization balance Principle C14-D14 B151SB$3/12) -C15-D15 -816"(SBS3/12) | 16 D16 B17(SB3/12) C17-D17 B18 B$3/12) C18-D18 819 (SBS3/12) -C19-D19 B20SB$3/12) -C20-D20 End bal 814-E1 B15-E15 Beg balance Payment Interest Principle End bal J14-K14 114-L14 C$7 115*SC$3/12) J15-K15 115-L15 Payment Interest F14 =F15 F16 #17 F18 F19 -M14 -M15 M16 -M17 M18 M19 $B$7 :SCS7-117"(SCS3/12)-17-K171:17-17 817-E17 B1B-E18 819-E19 -B20-E20 -SC$7 119 ISCS3/12 19-K19 119-L19 $C$7 120(SC$3/12) J20-K20 -120-L20