Question

For the following question, please show your work. Every number you get, show how you got it. And please make everything as clear as possible. Thanks in advance.

2. What is the outstanding balance on the following loan after the borrower has made 12.5 years of payments? (For full credit, calculate manually and show all of your work.) (20 pts)

Loan amount: $300,000 Term: 25 years Discount Points: 5 Payments: Monthly Interest Rate: 4.5% (annual)

Answer 1

The outstanding balance on the following loan after the borrower has made 12.5 years of payments is $191036

To construct a EMI chart in excel

Step 1 > Input fixed values, i.e Loan amount, Term, payments per year etc

For our problem its monthly payments thus 12, in case quarterly payments it would be 4 and so on.

Step 2 >

Calculate the monthly installment by using PMT function in excel.

=PMT (rate , nper , pv, fv , type)

Rate = 4.5%/12 as it is monthly payment

Nper = total number of payment periods = 25*12 = 300

PV = Present value i.e the loan taken

Step 3 >

Construct monthly EMI chart, have displayed the formula for first 2 instatements

1. One formula is same from 2 month onwards, drag the cell to get the values

2. Too keep cells constant use $ sign, eg the loan amount, monthly installment is the same hence we have used $

3. Since the tenure is 300 i.e 300 months the loan will end on the same

4. 12.5 years = 150 months, the balance at end of 150 months is 191036