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.