View the step-by-step solution to:

# Instructions:5 lines as well as an excel file. Use the first sheet of excel to write your group information(Names and Student IDs) Carefully state...

I did parta) in excel not sure how to do from partb) and part f is write a summary report.

Instructions: The assignment contains this word file. The assignment should be done by a group of two – five students. The assignment MUST be type-written with font Times New Roman, font size 12, and line spacing 1.5 lines as well as an excel file. Each assignment has to include this cover sheet. Use the first sheet of excel to write your group information(Names and Student IDs) Carefully state the steps and assumptions you make in answering questions. The maximum number of pages is 8 (excluding the Excel file). The assignment has to be handed in electronically via LMS, including two files: a word file and an excel file. YOUR GROUP: Name Student ID Assignment Questions Question 1 (30 Points) Through this question, you will be guided to perform a role of fund manager specializing bank loan insurance. Assume Bank Mel has 10 loans, denoted by . Each loan has default probability , loss given default , and maturity . The correlation between loans is . You are the manager of FMI Fund. a) (5ps) Suppose all loan have the same parameters ,and. Please calculate the capital requirements for all eight possible scenarios given . This will give you some general idea how capital requirement is sensitive to the loan characteristics.
b) (5ps) Now, as the FMI fund manager, you want to know the real losses from these loans. Using the parameters in question a), simulate the loan losses. For each simulation, you need to use the real correlation. Assume there are three values:. In total, you need to consider 24 scenarios. For each scenario, try one hundred simulations. Keep the simulation results for question c). Also, a nice summary statistics for each scenario is needed. (see summary statistics at http://en.wikipedia.org/wiki/Summary_statistic) c) (5ps) Suppose now you are going to provide the insurance for those loans. The bank will absorb the expected loss itself. Let EL be the expected loss and RL the actual loss from the 10 loans. EL can be calculated from each scenario. RL is the loss simulation result in part b). If FMI fund agrees to cover z loss, then the FMI will need to pay to the bank in case the real lost is RL, that is, for any loss greater than EL, the FMI fund need to cover that extra loss for the bank, but only up to z in total. Now, pick your favorite z, and then calculate for each simulation, 1) the bank’s loss after the insurance payment and 2) the FMI fund’s payment to the bank. d) (5ps) This is the most difficult part of this question. For each scenario in question c), you obtained the bank’s loss after insurance and built the summary statistics. Now, for each scenario (24 scenarios in total), you need to find another group of parameters on default probability and loss given default so that the simulated loss under these new parameters will generate roughly the same mean and variance of the loss as the one in c). For example, if you use the original default probability, then the loss given default will be reduced to the match the loss after insurance; and vice versa. Now, find the new default probability (loss 2
Show entire document
scenario 1 Capital Requirement k 0.392175564 scenario 1 Capital Requirement k 0.392176 scenario 1 Capital Requirement k 0.392176 (1/(1-R))^0.5*G(PD) and N() 2.7351859391 0.996883 N((1/(1-R))^0.5*G(PD)) 2.735186 0.996883 N((1/(1-R))^0.5*G(PD)) 2.735186 0.996883 G(PD) -0.5244005127 G(PD) -0.5244 G(PD) -0.5244 G(0.999) 3.0902323062 G(0.999) 3.090232 G(0.999) 3.090232 b 0.0340305202 b 0.034031 b 0.034031 R - correlation bw loans p 0.1200000367 R - correlation bw loans p 0.12 R - correlation bw loans p 0.12 exptected loss 1.5 exptected loss 1.5 exptected loss 1.5 Defualt probability p 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Maturity t 6 Maturity t 6 Maturity t 6 scenario 2 Capital Requirement k 0.3421936324 scenario 2 Capital Requirement k 0.342194 scenario 2 Capital Requirement k 0.342194 N((1/(1-R))^0.5*G(PD)) 2.7351859391 0.996883 N((1/(1-R))^0.5*G(PD)) 2.735186 0.996883 N((1/(1-R))^0.5*G(PD)) 2.735186 0.996883 G(PD) -0.5244005127 G(PD) -0.5244 G(PD) -0.5244 G(0.999) 3.0902323062 G(0.999) 3.090232 G(0.999) 3.090232 b 0.0340305202 b 0.034031 b 0.034031 R - correlation bw loans p 0.1200000367 R - correlation bw loans p 0.12 R - correlation bw loans p 0.12 exptected loss 1.5 exptected loss 1.5 exptected loss 1.5 Defualt probability p 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Maturity t 2 Maturity t 2 Maturity t 2 scenario 3 Capital Requirement k 0.0784351128 scenario 3 Capital Requirement k 0.078435 scenario 3 Capital Requirement k 0.078435 N((1/(1-R))^0.5*G(PD)) 2.7351859391 0.996883 N((1/(1-R))^0.5*G(PD)) 2.735186 0.996883 N((1/(1-R))^0.5*G(PD)) 2.735186 0.996883 G(PD) -0.5244005127 G(PD) -0.5244 G(PD) -0.5244 G(0.999) 3.0902323062 G(0.999) 3.090232 G(0.999) 3.090232 b 0.0340305202 b 0.034031 b 0.034031 R - correlation bw loans p 0.1200000367 R - correlation bw loans p 0.12 R - correlation bw loans p 0.12 exptected loss 0.3 exptected loss 0.3 exptected loss 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Maturity t 6 Maturity t 6 Maturity t 6 scenario 4 Capital Requirement k 0.0684387265 scenario 4 Capital Requirement k 0.068439 scenario 4 Capital Requirement k 0.068439 N((1/(1-R))^0.5*G(PD)) 2.7351859391 0.996883 N((1/(1-R))^0.5*G(PD)) 2.735186 0.996883 N((1/(1-R))^0.5*G(PD)) 2.735186 0.996883 G(PD) -0.5244005127 G(PD) -0.5244 G(PD) -0.5244 G(0.999) 3.0902323062 G(0.999) 3.090232 G(0.999) 3.090232 b 0.0340305202 b 0.034031 b 0.034031 R - correlation bw loans p 0.1200000367 R - correlation bw loans p 0.12 R - correlation bw loans p 0.12 exptected loss 0.3 exptected loss 0.3 exptected loss 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Maturity t 2 Maturity t 2 Maturity t 2 scenario 5 Capital Requirement k 0.5370465765 scenario 5 Capital Requirement k 0.537047 scenario 5 Capital Requirement k 0.537047 N((1/(1-R))^0.5*G(PD)) 1.92894652 0.973131 N((1/(1-R))^0.5*G(PD)) 1.928947 0.973131 N((1/(1-R))^0.5*G(PD)) 1.928947 0.973131 G(PD) -1.2815515655 G(PD) -1.28155 G(PD) -1.28155 G(0.999) 3.0902323062 G(0.999) 3.090232 G(0.999) 3.090232 b 0.0598563682 b 0.059856 b 0.059856 R - correlation bw loans p 0.1208085536 R - correlation bw loans p 0.120809 R - correlation bw loans p 0.120809 exptected loss 0.5 exptected loss 0.5 exptected loss 0.5 Defualt probability p 0.1 Defualt probability p 0.1 Defualt probability p 0.1 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Maturity t 6 Maturity t 6 Maturity t 6 scenario 6 Capital Requirement k 0.4222112028 scenario 6 Capital Requirement k 0.422211 scenario 6 Capital Requirement k 0.422211 N((1/(1-R))^0.5*G(PD)) 1.92894652 0.973131 N((1/(1-R))^0.5*G(PD)) 1.928947 0.973131 N((1/(1-R))^0.5*G(PD)) 1.928947 0.973131 G(PD) -1.2815515655 G(PD) -1.28155 G(PD) -1.28155 G(0.999) 3.0902323062 G(0.999) 3.090232 G(0.999) 3.090232 b 0.0598563682 b 0.059856 b 0.059856 R - correlation bw loans p 0.1208085536 R - correlation bw loans p 0.120809 R - correlation bw loans p 0.120809 exptected loss 0.5 exptected loss 0.5 exptected loss 0.5 Defualt probability p 0.1 Defualt probability p 0.1 Defualt probability p 0.1 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Maturity t 2 Maturity t 2 Maturity t 2 scenario 7 Capital Requirement k 0.1074093153 scenario 7 Capital Requirement k 0.107409 scenario 7 Capital Requirement k 0.107409 N((1/(1-R))^0.5*G(PD)) 1.92894652 0.973131 N((1/(1-R))^0.5*G(PD)) 1.928947 0.973131 N((1/(1-R))^0.5*G(PD)) 1.928947 0.973131 G(PD) -1.2815515655 G(PD) -1.28155 G(PD) -1.28155 G(0.999) 3.0902323062 G(0.999) 3.090232 G(0.999) 3.090232 b 0.0598563682 b 0.059856 b 0.059856 R - correlation bw loans p 0.1208085536 R - correlation bw loans p 0.120809 R - correlation bw loans p 0.120809 exptected loss 0.1 exptected loss 0.1 exptected loss 0.1 Defualt probability p 0.1 Defualt probability p 0.1 Defualt probability p 0.1 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Maturity t 6 Maturity t 6 Maturity t 6 scenario 8 Capital Requirement k 0.0844422406 scenario 8 Capital Requirement k 0.084442 scenario 8 Capital Requirement k 0.084442 N((1/(1-R))^0.5*G(PD)) 1.92894652 0.973131 N((1/(1-R))^0.5*G(PD)) 1.928947 0.973131 N((1/(1-R))^0.5*G(PD)) 1.928947 0.973131 G(PD) -1.2815515655 G(PD) -1.28155 G(PD) -1.28155 G(0.999) 3.0902323062 G(0.999) 3.090232 G(0.999) 3.090232 b 0.0598563682 b 0.059856 b 0.059856 R - correlation bw loans p 0.1208085536 R - correlation bw loans p 0.120809 R - correlation bw loans p 0.120809 exptected loss 0.1 exptected loss 0.1 exptected loss 0.1 Defualt probability p 0.1 Defualt probability p 0.1 Defualt probability p 0.1 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Maturity t 2 Maturity t 2 Maturity t 2
scenario 1 Capital Requirement k 0.38987 scenario 1 Capital Requirement k 0.391034 scenario 1 Capital Requirement k 0.393321 N((1/(1-R))^0.5*G(PD)) 2.446425 0.992786 N((1/(1-R))^0.5*G(PD)) 2.565832 0.994854 N((1/(1-R))^0.5*G(PD)) 3.066756 0.998918 G(PD) -0.524401 G(PD) -0.524401 G(PD) -0.524401 G(0.999) 3.090232 G(0.999) 3.090232 G(0.999) 3.090232 b 0.034031 b 0.034031 b 0.034031 R - correlation bw loans p -0.1 R - correlation bw loans p 0 R - correlation bw loans p 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Maturity t 6 Maturity t 6 Maturity t 6 scenario 2 Capital Requirement k 0.340182 scenario 2 Capital Requirement k 0.341197 scenario 2 Capital Requirement k 0.343193 N((1/(1-R))^0.5*G(PD)) 2.446425 0.992786 N((1/(1-R))^0.5*G(PD)) 2.565832 0.994854 N((1/(1-R))^0.5*G(PD)) 3.066756 0.998918 G(PD) -0.524401 G(PD) -0.524401 G(PD) -0.524401 G(0.999) 3.090232 G(0.999) 3.090232 G(0.999) 3.090232 b 0.034031 b 0.034031 b 0.034031 R - correlation bw loans p -0.1 R - correlation bw loans p 0 R - correlation bw loans p 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Maturity t 2 Maturity t 2 Maturity t 2 scenario 3 Capital Requirement k 0.077974 scenario 3 Capital Requirement k 0.078207 scenario 3 Capital Requirement k 0.078664 N((1/(1-R))^0.5*G(PD)) 2.446425 0.992786 N((1/(1-R))^0.5*G(PD)) 2.565832 0.994854 N((1/(1-R))^0.5*G(PD)) 3.066756 0.998918 G(PD) -0.524401 G(PD) -0.524401 G(PD) -0.524401 G(0.999) 3.090232 G(0.999) 3.090232 G(0.999) 3.090232 b 0.034031 b 0.034031 b 0.034031 R - correlation bw loans p -0.1 R - correlation bw loans p 0 R - correlation bw loans p 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Maturity t 6 Maturity t 6 Maturity t 6 scenario 4 Capital Requirement k 0.068036 scenario 4 Capital Requirement k 0.068239 scenario 4 Capital Requirement k 0.068639 N((1/(1-R))^0.5*G(PD)) 2.446425 0.992786 N((1/(1-R))^0.5*G(PD)) 2.565832 0.994854 N((1/(1-R))^0.5*G(PD)) 3.066756 0.998918 G(PD) -0.524401 G(PD) -0.524401 G(PD) -0.524401 G(0.999) 3.090232 G(0.999) 3.090232 G(0.999) 3.090232 b 0.034031 b 0.034031 b 0.034031 R - correlation bw loans p -0.1 R - correlation bw loans p 0 R - correlation bw loans p 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Defualt probability p 0.3 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Maturity t 2 Maturity t 2 Maturity t 2 scenario 5 Capital Requirement k 0.52755 scenario 5 Capital Requirement k 0.531891 scenario 5 Capital Requirement k 0.544152 N((1/(1-R))^0.5*G(PD)) 1.724509 0.957692 N((1/(1-R))^0.5*G(PD)) 1.808681 0.96475 N((1/(1-R))^0.5*G(PD)) 2.161787 0.984683 G(PD) -1.281552 G(PD) -1.281552 G(PD) -1.281552 G(0.999) 3.090232 G(0.999) 3.090232 G(0.999) 3.090232 b 0.059856 b 0.059856 b 0.059856 R - correlation bw loans p -0.1 R - correlation bw loans p 0 R - correlation bw loans p 0.3 Defualt probability p 0.1 Defualt probability p 0.1 Defualt probability p 0.1 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Maturity t 6 Maturity t 6 Maturity t 6 scenario 6 Capital Requirement k 0.414745 scenario 6 Capital Requirement k 0.418158 scenario 6 Capital Requirement k 0.427797 N((1/(1-R))^0.5*G(PD)) 1.724509 0.957692 N((1/(1-R))^0.5*G(PD)) 1.808681 0.96475 N((1/(1-R))^0.5*G(PD)) 2.161787 0.984683 G(PD) -1.281552 G(PD) -1.281552 G(PD) -1.281552 G(0.999) 3.090232 G(0.999) 3.090232 G(0.999) 3.090232 b 0.059856 b 0.059856 b 0.059856 R - correlation bw loans p -0.1 R - correlation bw loans p 0 R - correlation bw loans p 0.3 Defualt probability p 0.1 Defualt probability p 0.1 Defualt probability p 0.1 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Loss Given Defualt l 0.5 Maturity t 2 Maturity t 2 Maturity t 2 scenario 7 Capital Requirement k 0.10551 scenario 7 Capital Requirement k 0.106378 scenario 7 Capital Requirement k 0.10883 N((1/(1-R))^0.5*G(PD)) 1.724509 0.957692 N((1/(1-R))^0.5*G(PD)) 1.808681 0.96475 N((1/(1-R))^0.5*G(PD)) 2.161787 0.984683 G(PD) -1.281552 G(PD) -1.281552 G(PD) -1.281552 G(0.999) 3.090232 G(0.999) 3.090232 G(0.999) 3.090232 b 0.059856 b 0.059856 b 0.059856 R - correlation bw loans p -0.1 R - correlation bw loans p 0 R - correlation bw loans p 0.3 Defualt probability p 0.1 Defualt probability p 0.1 Defualt probability p 0.1 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Maturity t 6 Maturity t 6 Maturity t 6 scenario 8 Capital Requirement k 0.082949 scenario 8 Capital Requirement k 0.083632 scenario 8 Capital Requirement k 0.085559 N((1/(1-R))^0.5*G(PD)) 1.724509 0.957692 N((1/(1-R))^0.5*G(PD)) 1.808681 0.96475 N((1/(1-R))^0.5*G(PD)) 2.161787 0.984683 G(PD) -1.281552 G(PD) -1.281552 G(PD) -1.281552 G(0.999) 3.090232 G(0.999) 3.090232 G(0.999) 3.090232 b 0.059856 b 0.059856 b 0.059856 R - correlation bw loans p -0.1 R - correlation bw loans p 0 R - correlation bw loans p 0.3 Defualt probability p 0.1 Defualt probability p 0.1 Defualt probability p 0.1 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Loss Given Defualt l 0.1 Maturity t 2 Maturity t 2 Maturity t 2
Show entire document

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

### -

Educational Resources
• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents