Finance Homework Assignment - Loan Amortization Table and Chart

Loan Amortization Table and Chart

a. Your client asks you to develop an amortization table and chart (displays principal

and interests) for his mortgage (table loan) with the specifications given below. Use

worksheet “Loan Amortization A” to develop your solution.

- Loan amount $200,000

- Time to maturity: 3 years

- Payment: quarterly pay in equal instalments at end of period

- Fixed interest rate 6%

b. Your client asks you to develop an amortization table and chart (display principal and

interests) for his mortgage (reduce loan) with the specifications given below. Use

worksheet “Loan Amortization B” to develop your solution.

- Loan amount $200,000

- Time to maturity: 3 years

- Payment: quarterly pay in equal instalments at end of period

- Interest rate is 6% in the first year, it will be decreased by 10% in the second year, and

then it will be changed back to 6% in the third year.

Efficient frontier

The following questions are shown in the worksheet “Efficient frontier”. Assume the

short-selling is prohibited.

a. Use variance-covariance matrix S to calculate the standard deviation and return of

portfolio if the shares are held in equal weighted proportions.

b. Use Excel building function ‘solver’ to find returns and standard deviation for optimal

portfolio (minimum variance).

c. Use Excel building function ‘solver’ to find returns and standard deviation for optimal

portfolio (maximum Sharpe ratio).

Analysis of Data on Banks with Pivot Tables. Worksheet “Banks data” contains a list of information on selected international banks.

Full marks will be given only results are linked with Pivot table

Question 1 Pivot table for Question 1

Country Number of banks

Question 2 Pivot table for Question 2

Bank Assets (EUR million)

Largest in terms of assets

Question 3 Pivot table for Question 3

Country Average Net income (USD million)

Highest average:

(Keep 2 decimal points)

Lowest average:

(Keep 2 decimal points)

Question 4 Pivot table for Question 4

Bank name Total assets (USD million)

(Keep 2 decimal points)

Bank name Total equity (USD million)

(Keep 2 decimal points)

Question 5 Pivot table for Question 5

Country Average ROA (%)

(Keep 2 decimal points)

Country Average ROE (%)

(Keep 2 decimal points)