代做FIN6102 Spreadsheet and VBA Modelling in Finance Homework 1代做Python编程

日期：2025-02-17 03:31

FIN6102 Spreadsheet and VBA Modelling in Finance

Homework 1

Suppose that you are an investment manager with a bank, and you’represented with the following investment opportunities of loans. You are evaluating the loans on 31 Dec, 2024. All payments are in arrears (meaning payment at end of each period).

	Loan 1	Loan 2	Loan 3	Loan 4
Drawdowns	$14 million on Dec 31, 2024	$22 million on 31 Dec, 2024	$16 million on 31 Dec 2024, and $4 million on 30 Jun 2026.	$20 million on Dec 31, 2027
Term (years from first Drawdown to maturity)	7 yrs	9 yrs	6 yrs	5 yrs
Repayment Schedule	Loan is paid back in equal installments, meaning that the total payment (principal + interest) for each period is the same. Payment is made semi-annually	Interest is paid semi-annually for the first 5 years, then the loan is then paid back in equal installments.	Interest is paid semi-annually. The principal is paid back at maturity.	Interest is capitalized semi- annually for the first 2 years and is paid semi-annually thereafter. Then the loan (with the capitalized interest) will be paid back with equal principal payments semi-annually.
Interest Rate	6.50%	8% for the interest only periods, 8.5% during the remaining periods.	7.00%	9.5% for the first two years, 10.5% for the remainder of the years.

1.    List out the cash flow and outstanding amount for each loan for every year with semi-annual frequency. See template provided in the Excel workbook. Note that you only need to fill the years for each loan as they last.

2.    Assume a discount rate of 5%, calculate the net present value of each loan as of Dec 31, 2024.

3.    Suppose that you have only 40 million dollars budget, so you can’t take all the loans. Assuming you can either choose one investment or leave it (i.e.,  you cannot choose to invest in a fraction of the investment). Your target is to maximize the future value of your portfolio at the end of year 2033, which loans will you choose?

4.    If you can invest at any fraction of the loan (but not more than 100%), how should you construct your portfolio? Assume that no interest will be earned for cash at hand. Describe the process of solving the problem. (Hint: use Solver)

5.    What if you can earn an interest of 5% for your remaining cash at hand (use semi-annual compounding), will that change your answer for question 3 and 4? What is the final value of your portfolio at the end of 2033?

6.    If each loan has an independent probability of default of 1% on the date of Dec 31, 2028 (the last day of 4 years). There is no probability of default at any other time. If they default, you will lose all the outstanding principal in the loan. For the portfolio that you constructed in 4:

a.    What is your 4-year 99% Value at Risk (in million dollars)?

b.    What is your 4-year 99% Expected Shortfall (in million dollars)?

Hint: at end of Dec 31, 2028, your portfolio value = cash at hand + principal that has not defaulted. Calculate the probability of each portfolio value and its P&L. Then use it for VaR/ES calculation.