IRR Output needs to match selected solutions

Here's the question. Answer in Pure Excel Model

Selected Solutions The below solutions are with the base case inputs provided. Your model will also need to be able to adjust for different inputs. Try changing the inputs and verify that the model changes in the way it should respond. Do this for all the inputs. Note that your solution may differ significantly from mine due to the random nature of the model. Run your model with 1,000 iterations to get a decent check (this will take about 10 minutes). Even then it may be off by up to a couple percentage points. I am also providing the IRRs for each possible default situation in the model with base case inputs and a 20% interest rate. This way you can check your model without having to run lots of iterations. Make sure that your model can reproduce each of the IRRs corresponding to each default case, and then you will only need the full solutions to check that the probabilities are set correctly. Note that unlike the full solutions, you should be able to match these default case solutions exactly. \begin{tabular}{|l|l|c|} \hline & \multicolumn{1}{|c|}{ A } & B \\ \hline 1 & \multicolumn{1}{|c|}{ Inputs } & \\ \hline 2 & Price of Machine & $1,000,000 \\ \hline 3 & Loan Life & 20 \\ \hline 4 & Initial Default Probability & 30% \\ \hline 5 & Decay of Default Probability & 90% \\ \hline 6 & Final Default Probability & 40% \\ \hline 7 & Recovery Rate & 40% \\ \hline 8 & Interest Rate & 40% \\ \hline 9 & Default Year & 2 \\ \hline \end{tabular} = Outputs when using Excel for a single default case at once =Outputs when doing a pure Excel model Probabilistic Loan Pricing Due: Mar, 15th,2023 Overview Problem Definition You work for a bank who is considering loaning funds to a small manufacturing business. The business needs price machine to buy machinery. The business would like to borrow the funds for nwhe, and at that time it will interest at the rate of r interest will be paid). As this is a small business, there is significant default risk, but that default risk decreases over time as the business matures. The probability of default in the first year is pheffourt, and then each year thereafter it is: Finally, the default probability is different in the final year, as it is the repayment year. The business has to pay a lot more in this period so there is a greater likelihood it can't come up with the funds. In the final year, (at year nufee, the default probability is pdefouit.. When the business defaults, then the default covenants of the loan trigger bankruptcy for the borrower, and the borrower must pay as much as it can on the loan in the bankruptcy process. The bankruptcy process takes two years, and then once it is resolved, the lender will collect rmecover % of pricemectine. For the year of default and the year after, the lender will not collect any cash flows, and then two years after default, the lender will collect rrecovenprice mactine. Note that this means the number of years of cash flows may be up to two years greater than the life of the loan. Main Question You are the commercial loan analyst trying to decide if this loan makes sense for the bank. You want to give the lending officer all the information she would need to negotiate a rate for this loan. Given the inputs, what is the expected IRR of the loan for a variety of interest rates on the loan? The lending officer would like you to evaluate rates in 5% increments from 30% to 40%. The lending officer is also worried that she may have estimated pdeffut incorrectly. She is hoping for the answers to the above questions considering that pdeffoul may vary. Evaluate the above questions for pdefoul=0.1,0.3 in addition to the base case of 0.2 . Finally, the lending officer is unsure for how long she should extend the loan. So she would like to see all of the previously mentioned results with loan lifes of 5,10 , and 20 years. You should visualize the results of your model through graphs, conditional formatting etc. - With the internal randomness approach, make sure you set the number of iterations to 1,000 per set of inputs to get a good estimate. - While you are testing things out, set it lower, such as 10 or 100 , to have it run quicker, but beware that the lower your number of iterations, the less consistent the results will be. - Also beware that with 1,000 iterations as required for the final submission, it may take over an hour to run the model, so plan for that time. - You may choose to cither submit a pure Python model, pure Excel model, or a combination of the two. If you use both, then the Python model should be what I ultimately run and extract results from. The Python model would be running the Excel model many times and extracting the results. - Upon reading the prior note, you may think to implement in pure Excel because of greater familiarity, but I think you will find meeting the objective of running the model repeatedly with three changing inputs quite difficult. You need to run your model 27,000 times in total with three inputs changing together. - Your answers may differ slightly from those in the Selected Solutions section. This is the nature of a random model. They should be very close with 1,000 iterations, though. - Do not move any of the input or output cells. Inputs 1. pricemachine: 1000000 2. noufe: 5 3. piefoulf: 0.2 4. Decaydefoult: 0.9 5. p plefoult: 0.4 6. rrecovery:0.4 Submission \& Grading Submission Work off of the "Project 2 Template.xlsx" and "Project 2 Template.ipynb" files to my email. It is up to you whether you submit a pure Python, pure Excel, or combination model. For whichever tools you use, you should use the templates, so those submitting a combination model will submit both a Jupyter notebook and an Excel workbook, both based off the templates. With the combination model, your ultimate answer should be in the Jupyter notebook. - Default in t=1:26.32% - Default in t=2;14.96% - Default in t=3:6.43% - Default in t=4:0.00% - Default in t=5:4.82% - No Default: 20.00%