Question

On June 30, 2018, you dosed on your new condominium and obtained a $200,000 twenty-five year mortgage at an APR of 4.32%. The first monthly payment was due at that time (June 30, 2018), and then on the last day of each month thereafter (this is an "annuity in advance" or an "annuity due". a. What was the amount of your required monthly payment? 8. Assuming that you made all payments exactly when due, how much interest would you have paid on the mortgage in the year ending December 31, 2018? b. . How much interest would you pay in 2019 if you made all of your scheduled payments exactly when due? d. If the interest rate were to increase to 4.62% after sixty payments, what would be the amount of your new monthly payment? (the life of the mortgage would be unchanged).

Answer 1

Mortgage (or) Loan amount is $200,000 Mortgage (or) Loan period is 25 years Annual Percentage Rate (APR) is 4.32% Here, the Montly payment was due at the time of last day of each month (i.e. annuity in advance). Hence, this is the concept belongs to the "Annuity Due" Annuity Due is a series or level of cash flows that occur at the beginning of each period for some fixed number of periods. It is a recurring cash flow of an equal amount will be occurred at periodical intervals. In an Ordinary Annuity due the periodical cash payments should be occurred at the beginning (or) starting of the each period. Therefore, for an ordinary annuity due the first payment should occur at the period of zero and for an ordinary annuity the first payment should be occur at the time period one (or) at the end of the year. Ordinary Annuity Due Annuity (BEGIN) (END)

a) Calculate the amount of required monthly mortgage (or) loan payment amount (PM by using the Ms-Excel "PMT" function: Present Value (PV) of loan amount is $200,000 Loan Period (Nper) is 25 years APR (Rate) is 4.32% XPMT(4.32%/12,25 12,-200000) Function Arguments PMT PMT Rate 4.32%/12 0.0036 Nper 25 12 300 Pv -200000 Fv 200000 number number -1091.330341 Type Calculates the payment for a loan based on constant payments and a constant interest rate Pv is the present value: the total amount that a series of future payments is worth now Formula result 1091.330341 Helo on this finction Therefore, the Monthly Payment amount (PMT is $1,091.33

Calculate the total interest amount paid on the mortgage in the vear ending December 31, 2018: nning Balance InstalmentInterest Principal Ending Balance Year 30-Jun-18 31-Jul-18 $1,091.33 $720.00 $371.33$199,628.67 $1,091.33 $718.6 $372.67 $199,256.00 $1,091.33 $717.32$374.0$198,881.99 $1,091.33 $715.98$375.35 $198,506.64 $1,091.33 $714.62 $376.71$198,129.93 $1,091.33 $713.27$378.06 $197,751.87 $1,091.33$711.91$379.42 $197,372.45 $200,000.00 $199,628.67 31-Aug-18 $%199,256.00 30-Sep-18 $198,881.99 31-Oct-18 30-Nov-18$198,129.93 31-Dec-18 $197,751.87 Total Interest Paid on December 31st 2018 $198,506.64 $5,011.76 Ending Balance-Beginning Balance - Principal Interestamount-Beginning balance amountx Principal amount-Instalmentamount -Interest amount Therefore, the total interest amount were paid at the end of the period of December 31st 0.0432 12 2018 is $5,011.76

c) Calculate the Total Interest amount paid in the vear 2019 (or) at the end of the vear December 31,2019: nning Balance InstalmentInterest Principal Ending Balance $197,372.45 $196,991.66 31-Mar-19 $196,609.50 Year 31-Jan-19 28-Feb-19 31-May-19S195,841.05 $195.454.74 $195,067.05 31-Aug-19 $194,677.96 $194,287.47 $193,895.58 30-Nov-18 $193,502.27 $193,107.55 $1,091.33$710.54 $380.79 $196,991.66 $1,091.33 $709.17$382.16 $196,609.50 $1,091.33 $707.79$383.54$196,225.96 30-Apr-19 $%196,225.96 $1,091.33 $706.41 $384.92 $195,841.05 $1,091.33 $705.03 $386.30 $195,454.74 $1,091.33 $703.64 $387.69 $195,067.05 $1,091.33 $702.24$389.09 $194,677.96 $1,091.33 $700.84 $390.49 $194,287.47 $1,091.33 $699.43 $391.90 $193,895.58 $1,091.33 $698.02 $393.31$193,502.27 $1,091.33 $696.1 $394.72 $193,107.55 $1,091.33 $695.19$39614$192,711.41 30-Jun-19 31-Jul-19 30-Sep-19 31-Oct-19 31-Dec-19 Total Interest amount paid in the vear 2019 S8,434.92 Therefore, the total interest amount paid in the vear 2019 is $8,434.92 (the period started from January 31s1, 2019 to December 31st, 2019)

d) Caluclate the Mortgage New Monthly Payment amo to increases to 4.62% after the Mortgage (or) Loan amount is $200,000 Mortgage (or) Loan period is 25 years Annual Percentage Rate (APR) is 4.32% Here, the Montly payment was due at the time of last day of each month (i.e annuity in advance). Hence, this is the concept belongs to the "Annuity Due" The Loan Monthly Payment at 4.32% is $1,091.33 unt (PMT, if the interest rate were avments: Loan Amortization Table for the 60 periods at 4.32% interest rate: Periods $200,000.00 199,628.67 $199,256.00 5198,881.99 $198,506.64 198,129.93 197,75187 197,37245 196,991.66 196,609.50 30-Jun-18 31-Jul-18 31-Aug-18 $199,628.67 199,256.00 $198,881.99 $198,506.64 $198,129.93 197,75187 197,372.45 $196,991.66 5372.67 31-Oct-18 30-Now18 31 Dec-18 5376.71 378.06 5379.42 31-Mar-19 195,841.05 $195,454.74 $195,067.05 $194,677.96 $194,287.47 5193,895.58 $193,502.27 $193,107.55 192,71141 192,313.84 191,914 84 191,514.40 191.112.52 190,709.20 5190,304.42 189,898.19 $195,841.05 195,454.74 195,067.05 194,677.96 194,287.47 $193,895.58 $705.03 30-Jun-19 31-Jul-19 31-Aug-19 389.09 5699.43 31-Oct-19 30-Now-18 31 Dec-19 31-Jan-20 28-Feb-20 31-Mar-20 193,107.55 192,711.41 192,313.84 191,91484 191,514.40 191,112.52 190,709.20 $190,304,42 $189,898.19 189490.49 189,081.33 5188,670.69 399.00 5689.45 31-May-20 685.55 $406.23 31-Aug-20 $410.64 $412.12 $188,670.69 30-Now-20

5188,258.57 187,844.98 187,429.89 $187,013.31 186,595.22 186,17564 $185,754.54 $185,331.92 184,907.79 184,482.13 $184,054.93 $183,626.20 $183,195.93 $182,764.10 182,330.72 $181,895.78 $181,895.78 181,459.28 $1,091.33 $1,091.33 $1,091.33 $187,013.31 $186,595.22 $186,175.64 185,754.54 $185,331.92 $184,907,79 $184,482.13 $184,054.93 $183,626.20 $183,195.93 182,764.10 182,330.72 181,895.78 $181,459.28 $181,459.28 $181,021.20 180,581.55 $180,140.31 179,697.49 671.74 $1,091.33 $1,091.33 $668.72 1,091.33 $1,091.33 1,091.33 S659.51 S657.95 $656.39 $431.82 1,091.33 $1,091.33 $1,091.33 $1,091.33 $1,091.33 180,581.55 180,140.31 $179,697.49 $179,253.07 $178,807.05 178,359.42 177,910.19 177,459.33 $441.24 1,091.33 $1,091.33 $1,091.33 178,359.42 $1,091.33 1,091.33 $177,459.33 $177,006.86 $176,552.75 176,097.01 28-Feb-23 $1,091.33 $637.22 S635.59 30-Apr-23 $176,552.75 $1,091.33 Therefore, the Loan (or) Mortgage balance amount at the end of the 60th Month (or) period is $175,639.63

Hence, the Monthly payment instalment amount should be calculated after the 60th month at 4.62% interest rate by using the MS-Excel "PMT, Function: Present Value (PV) of the Mortgage (or) loan balance amount is $175,639.63 Number of remaining periods is [300 -60] 240 periods (or) 20 year:s Interest rate (Rate) is 4.62% :PNAT(4.62%/12,20.12,-175639.63) Function Arguments PMT ^ PMT 0.00385 240 Rate 4.62%/12 Nper 201 Pv -17S639.63 175639.63 Fv number Type -number 1122.592301 Calculates the payment for a loan based on constant payments and a constant interest rate. Nper is the total number of payments for the loan. Formula result 1122.592301 OK Cancel Therefore, the Monthly Loan Instalmetn Payment amount (PMT at the beginning of the 61st period (after completion of 60 months period) at 4.62% raised interest is $1,122.59