PROBLEM 3 - FINANCIAL MODELS A bank hires you to build a model to show the...

Transcribed Image Text:

PROBLEM 3 - FINANCIAL MODELS A bank hires you to build a model to show the customers the payment schedule for a type of mortgage. The mortgage (or loan) is described as below: - All mortgages will last for maximum 10 years. Payments can be made monthly or quarterly. Periodic payments are made periodically, and all payments are equal. Interest payments are made periodically, and each is based on the remaining loan balance. Payments are made at the end each period. Question 1 (15 marks): Build a model (name the sheet PRO3.1) to show payment schedule for the 10 years-period. The schedule must specify detailed payment for each period and the ending balance. Note that current borrowing rate is set at 10%. Question 2 (15 marks): Copy your model built in question 1 to a new sheet (name it PRO3.2) and modify it so that it accommodates the change in the bank policy as follows: Periodic payments are made periodically. In which, principal repayments are divided evenly through the life of the mortgage, and the periodical payment of the interest is based on the outstanding loan balance. Question 3 (10 marks): Copy the model built in question I to a new sheet (name it PRO3.3) and modify it so that it accommodates the new bank's policy of the interest rate on mortgages. The interest rate is determined through the two criteria: credit rating of the consumer and the borrowing amount: If the borrower has the Excellent credit rating: For the amount of less than $100,000, the interest rate is 10% For the amount from $100,000 to less than $500,000, the interest is For the amount from 500,000, the interest is 9% 8.5% If the borrower has the Good credit rating: For the amount of less than $100,000, the interest rate is 10.5% For the amount from $100,000 to less than $500,000, the interest is For the amount from $500,000, the interest is 9.5% 9% If the borrower has an Average credit rating: For the amount of less than $100,000, the interest rate is For the amount from $100,000 to less than $500,000, the interest is For the amount from $500,000, the interest is 11% 10.5% 10% PROBLEM 3 - FINANCIAL MODELS A bank hires you to build a model to show the customers the payment schedule for a type of mortgage. The mortgage (or loan) is described as below: - All mortgages will last for maximum 10 years. Payments can be made monthly or quarterly. Periodic payments are made periodically, and all payments are equal. Interest payments are made periodically, and each is based on the remaining loan balance. Payments are made at the end each period. Question 1 (15 marks): Build a model (name the sheet PRO3.1) to show payment schedule for the 10 years-period. The schedule must specify detailed payment for each period and the ending balance. Note that current borrowing rate is set at 10%. Question 2 (15 marks): Copy your model built in question 1 to a new sheet (name it PRO3.2) and modify it so that it accommodates the change in the bank policy as follows: Periodic payments are made periodically. In which, principal repayments are divided evenly through the life of the mortgage, and the periodical payment of the interest is based on the outstanding loan balance. Question 3 (10 marks): Copy the model built in question I to a new sheet (name it PRO3.3) and modify it so that it accommodates the new bank's policy of the interest rate on mortgages. The interest rate is determined through the two criteria: credit rating of the consumer and the borrowing amount: If the borrower has the Excellent credit rating: For the amount of less than $100,000, the interest rate is 10% For the amount from $100,000 to less than $500,000, the interest is For the amount from 500,000, the interest is 9% 8.5% If the borrower has the Good credit rating: For the amount of less than $100,000, the interest rate is 10.5% For the amount from $100,000 to less than $500,000, the interest is For the amount from $500,000, the interest is 9.5% 9% If the borrower has an Average credit rating: For the amount of less than $100,000, the interest rate is For the amount from $100,000 to less than $500,000, the interest is For the amount from $500,000, the interest is 11% 10.5% 10%