COMPLETE H-J PLEASE. A $300,000 loan is being repaid with equal monthly payments for 20 years at an interest rate of 10% compounded monthly per annum. c) What is the outstanding balance at the end of the third year using both the retrospective and prospective methods? d) Explain in detail how you would get your financial calculator to give you the answer in part (c). e) Write down the Excel functions that would give you the answer in part (c). f) What is the total interest paid for this loan? g) Explain in detail how you would get your financial calculator to give you the answer in part (f). Suppose now that the interest rate changes after the first year to 11% compounded monthly a year. [in order to obtain full points for the questions below you must show all work i.e., Excel reference will not merit credit]. h) Find the increased installment so that the loan is repaid at the end of the original 20 year term. i) Find the term of the loan if the installment is not increased. j) Calculate the amount of the last fractional payment. Expert Answer 100% (2 ratings) This solution was written by a subject matter expert. It's designed to help students like you learn core concepts. Varun Jain answered this 2,046 answers C) Firstly, we will determine the monthly payments for this loan using Present Value of Annuity (PVAF) which is equal to : 1-(1+r)^(-n) r , here r = 10%/12, n=20*12 = 240. Using the financial calculator weve got PVAF = 103.62 So, monthly installment would be, Present Value of Loan, i.e., $300,000/PVAF Monthly installment = 300000/103.62 = $2895.06 Outstanding Balance at the end of third year: Prospective Method : Till 3rd year 3*12 = 36 installments will be paid. Hence, remaining installments = 240-36 = 204. So, the loan balance would be present value of all 204 installments made. Loan Balance = Annuity * PVAF(0.10/12, 204) = 2895.06 * 97.92 = $283,492.98 = $283,493 (approx.) Retrospective Method : The loan balance would be : Future Value of $300,000 at the end of 3rd year - Future Value of $2895.06 annuity at the end of 3rd year = $300,000 * (1+r)^n - $2895.06 * [(1+r)^n 1]/r = $300,000 * (1+10%/12)^36 - $2895.06 * [(1+10%/12)^36 -1]/(10%/12) = $404,454.55 - $120,961.09 = $283,493.47 = $283,493 (approx.) D) Rather than calculating PVAF, Present value and Future Value manually, we can use the PMT, PV and FV function in the financial calculator to find out loan balance. PMT : to calculate monthly installment PV : to calculate present value of 204 monthly installments FV : to calculate future value of $300,000 and $2895.06 at the end of 3rd year. e) The following excel functions can be used : PMT : to calculate monthly installment PV : to calculate present value of 204 monthly installments FV : to calculate future value of $300,000 and $2895.06 at the end of 3rd year. f) Total Interest paid = $2895.06*240 - $300,000 = $394,815 approx. g) In financial calculator, we can use the PMT function to find out the annuity amount, i.e., $2895.06 and then find out the Total Interest paid by multiplying the annuity by 240 and subtracting $300,000.