Question

You have found your desired house for $500,000 and you like to purchase it. You approached a mortgage company for loan. The company asked you that if you pay 20% down, you are able to get a 30 years loan for the remaining balance with interest rate of 4.75% with zero points. You accepted the deal. Assuming the mortgage is payable at the end of each month.

Required: Develop an excel spread sheet and:

On the first couple of lines of your worksheet enter all the necessary information. Then Use these information to apply for the following requirements. Make sure to avoid typing in any unnecessary information. You need to use appropriate cells.

1) Determine your monthly mortgage payment

2) Prepare a worksheet as follows:

Period          Monthly Payment   monthly interest   Principle   Balance

0  

1

2

3

.

.

360

3) What is the total amount of interest that you have to pay over 30 years?

Answer 1

SOLUTION:

Please find the solution as below and Notes:

1) Montly installment will be $ 2087.

2) Last installment will be some reduced amount as amount is rounded to 0 decimals,

3) formula used : for Monthly Interest - Balance due * ROI /12

                               For Monthly Principal - monthly installment reduced by monthly interest.

                                For Balance - Loan reduced by monthly principal.

4) Due to length : after 5th period, it is being given for rest 3 periods.

5) Assumed that monthly installment will be paid as agreed.

     

3) TOTAL AMOUNT OF INTEREST to be paid over a period of 30 years : $ 350957