Question

Instructions Find the MONTHLY mortgage PAYMENT of stress tested(+2%) on your rate of 3.18% with a 20 year amortization mortgage. You will use the mortgage amount from previous sections. You must also determine the amount of INTEREST, PRINCIPAL and BALANCE owing for the mortgage after 3 Years and 1 Months. Input all the TVM variables and answers into the fields below. Mortgage Amount From Previous Question - Amortization $464372.71 20 Years - P/Y r Present Value of Loan (PV, FP1 Months P2 Months Number of Periods (N) Monthly Payment (PMT) BALANCE OWING (BAL) Mortgage Rate As % (1/Y) Value At End of Loan (FV) – PRINCIPAL PAID (PRN) – Section Value: 7 - INTEREST PAID (INT) - Submit Answers

Answer 1

=PMT(B2,B4,-B5) B6 A В C E F 1 Annual Rate 2 Monthly rate Number of years 0.0318 |=B1/12 20 3 |=B3*12 Number of months 4. 5 Mortgage Amount Monthly Payment 7 Mortgage Amount after 3 years 1 month 464372.71 =PMT(B2,B4,-B5) 6 =F48 -SUM(D12:D48) -SUM(E12:E48) Interest Paid after 3years 1 month 8 Principal Paid after 3years 1 month 9 10 Periodic Paymeny -$B$6 =SB$6 =$B$6 $B$6 -$B$6 Loan Repaid Period (in months) Closing Loan Balance Opening Loan Balance Interest Paid 11 =$B$2*B12 1 -B5 =C12-D12 B12-E12 12 =SB$2*B13 =$B$2*B14 SB$2*B15 =SB$2*B16 -$B$2*B17 SB$2*B18 -SB$2*B19 =SB$2*B20 =$B$2*B21 -$B$2*B22 =$B$2*B23 -SB$2*B24 =$B$2*B25 =$B$2*B26 -SB$2*B27 =SB$2*B28 =$B$2*B29 - B13- E13 2 =F12 -C13-D13 13 3 =F13 =C14-D14 -B14-E14 14 4 F14 -C15-D15 -B15-E15 15 5 C16-D16 F15 -B16-E16 16 =$B$6 -SB$6 =$B$6 =SB$6 =SB$6 - B17- E17 6 =F16 =C17-D17 17 C18-D18 -B18-E18 7 =F17 8 =F18 -C19-D19 -B19-E19 19 -C20-D20 9 =F19 -B20-E20 20 -B21 - E21 10 =F20 -C21 -D21 21 $B$6 =SB$6 =$B$6 $B$6 -$B$6 -C22-D22 -B22-E22 11 F21 22 C23 -D23 12 =F22 =B23-E23 23 -B24-E24 13 =F23 -C24-D24 24 -C25-D25 -B25-E25 14 =F24 25 15 -B26-E26 F25 C26-D26 26 =$B$6 =SB$6 =$B$6 16. =F26 -C27-D27 -B27-E27 27 =C28-D28 17 =F27 -B28-E28 28 18 =F28 -C29-D29 -B29-E29 29 =$B$2*B30 $B$6 -C30-D30 19 =F29 -B30-E30 30

fix -РM (В2, В4, -B5) X В6 A В C D E F G Annual Rate 3.18% 1 2 Monthly rate Number of years 0.27% 20 3 Number of months 240 4 Mortgage Amount 6 Monthly Payment Mortgage Amount after 3 years 1 month 8 Interest Paid after 3years 1 month 9 Principal Paid after 3years 1 month 464372.71 5 $ 2,617.44 4,10,533.96 $ $ 43,006.67 53,838.75 10 Period (in months) Closing Loan Balance 1,386.86 $ 4,62,985.85 1,390.53 $ 4,61,595.32 1,394.22 $ 4,60,201.11 Opening Loan Balan Periodic Paymeny Interest Paid 1230.587682 $ 1226.912513 $ Loan Repaid 11 464372.71 $ 1 2,617.44 12 2 $ 3 $ 4 $ 5 $ 6 S 7 $ 4,62,985.85 $ 4,61,595.32 $ 4,60,201.11 $ 4,58,803.20 $ 4,57,401.58 $ 4,55,996.25 $ 2,617.44 13 1223.227605 $ 1219.532932 $ 2,617.44 14 1,397.91 $4,58,803.20 1,401.62 $4,57,401.58 1,405.33 $4,55,996.25 1,409.05 $4,54,587.20 1,412.79 $ 4,53,174.41 1,416.53 $4,51,757.88 1,420.29 $4,50,337.59 1,424.05 $4,48,913.54 1,427.82 $4,47,485.72 2,617.44 15 1215.828468 $ 1212.114187 $ 1208.390064 $ 2,617.44 16 2,617.44 17 2,617.44 18 8 S 4,54,587.20 $ 4,53,174.41 $ 1204.656071 $ 1200.912184 $ 1197.158375 $ 1193.394618 $ 2,617.44 19 9 S 2,617.44 20 10 $ 11 $ 12 $ 13 $ 14 $ 15 $ 16 $ 17 $ 18 $ 4,51,757.88 $ 4,50,337.59$ 2,617.44 21 2,617.44 22 4,48,913.54 $ 4,47,485.72 $ 4,46,054.11 $ 4,44,618.71 $ 1189.620888$ 1185.837157 $ 1182.0434 $ 1178.239589 $ 1174.425697 $ 2,617.44 23 1,431.61 $4,46,054.11 1,435.40 $ 4,44,618.71 1,439.20 $ 4,43,179.51 1,443.02 $4,41,736.49 1,446.84 $4,40,289.65 1,450.68 $4,38,838.97 2,617.44 24 2,617.44 25 2,617.44 26 4,43,179.51 $ 4,41,736.49 $ 4,40,289.65 $ 2,617.44 27 1170.601699$ 1166.767568 $ 2,617.44 28 2,617.44 29

The mortgage table is constructed in similar way till month 240 when all the mortgage is paid and loan after 240 months =0.

I/Y = monthly rate = 0.27%

Number of period = 240

Monthly Payment = $2617.44