Real Estate Finance Assignment 2
Assignment Objectives:
In this assignment you will use the loan amortization schedule you created in MS Excel for Assignment #1 to: (i) calculate a lender's expected yield on a loan incorporating their assumptions of expected default hazards and loss severity; (ii) compare 2 loan options with different fees and interest rates.
Instructions:
Part A.
1. Download the Excel file titled "Excel_Sheet.xls" under assignments on Blackboard. Open the worksheet titled "Summary" of Assignment #2 file and under "Loan Data" input the original loan principal ($416,000), amortization length in years (30), interest rate (4.20%), and origination fees ($4,160) in cells C2:C5 of that worksheet. These parameters will help confirm you are completing each step correctly.
2. Open the amortization schedule you created for Assignment #1 and then "copy and paste" that worksheet into the Assign2_F15.xls file you just entered the loan inputs in Step #1 above. The easiest way to copy the worksheet into a new file is to open the worksheet you wish to copy and then right-click the bottom tab and then selecting ‘move or copy' and then selecting the new file (you can also try ‘copy and paste'). Rename that new worksheet "Schedule." Link the values you entered in Step #1 on the ‘Summary' worksheet to automatically update the corresponding values in C2:C5 of the worksheet you just labeled ‘Schedule'. For example, click cell C2 and type ‘=Summary!C2' and repeat for cells C3:C5. Confirm the payments, balances, and APR in the rest of the ‘Schedule' worksheet are identical to what you provided for Assignment #1.
3. Specify in cells C13:C14 on the "Summary" Worksheet that you initially assume a loss severity of 30% when borrowers default and that all borrowers default after 180 payments.
a. Open the worksheet titled "Default1" and notice the values in cells C2:C3 have already been linked to values you entered on the summary page.
b. Calculate the monthly anticipated cash flows a lender would receive if a borrower makes all anticipated payments until the month specified in cell C2. First, link cell B9 to the net loan amount calculated in schedule worksheet. Make sure this is a negative number by multiplying by -1.
c. To calculate each monthly cash flows this you will need to ‘nest' 2 separate ‘IF' statements in MS Excel. The first says if current month is before month of default, then the payment is the schedule payment specified on ‘schedule' worksheet. The second says if current month is equal to month of default, then multiply the scheduled payment and ending balance by 1 minus the assumed severity. If both of above conditions, then set anticipated cash flow to 0. This is executed by typing in cell B10 "=IF(A10 <$C$2,Schedule!F12,IF(A10=$C$2,(Schedule!F12 + Schedule!G12)*(1-$C$S),0))"
d. Calculate in cell C4 the lender's yield using the IRR() function. The lender's yield should be 3.09% if follow above steps correctly.
4. Specify in cell C15 of the ‘summary' worksheet that you alternatively assume a default hazard of 0.01% each month. Open worksheet labeled ‘Default2' and notice that value is already filled in in cell C2 of that worksheet. Now calculate the lender's yield based on that assumption.
a. Calculate the prob of a borrower defaulting or staying current each month. In cell B11 type ‘100%' to indicate 100% of borrowers were at risk of default initially. In cell C11 type ‘=B11*$C$2' to indicate prob of default after 1st month. In cell D11 subtract the prob of those defaulters from the percent at risk, or type ‘=B11-C11'. High cells C2:D2 and drag down 1 row. Type ‘=D11' in cell B12 to indicate those still current last period are at risk of defaulting next period. Highlight cells B12:D12 and double-click bottom right corner to auto-populate below fields.
b. Calculate the expected cash flow in column E based on a borrower defaulting or staying current on their payments. First, in cell E10 indicate the net loan to the borrower, which should be a negative, by typing ‘=-Schedule!H11'. If the default hazard is 0.01% this means that 99.99% of the time they will receive the 1st months payment (Schedule!F12) and the other 0.01% of the time will receive a percentage of the 1st months scheduled payment and outstanding principal. In cell E11, type ‘=(D11*ScheduleF12)+C11*(Schedule!F12+Schedule!G12)*(1-Default2!$C$3)'.
c. With the above assumed assumptions, a default hazard of 0.01%, and a loss severity of 30%, you should find the estimated lender's yield is 4.25%.
5. Specify in cell C16 of the ‘summary' worksheet that you alternatively assume borrower default at 100% of the Standard Default Assumption (SDA) of the Bond Market Association. 100% of the SDA assumes an increasing hazard of borrowers default during the first 29 months, it stays flat between months 30 and 60, decreases between 61 and 119 months, and stays flat after 120 months. This 100% pattern, along with 50%, 200%, and 300%, are displayed in the worksheet labeled ‘SDA_Schedule'.
a. Calculate the probability a borrower defaulting based on which % of the SDA is assumed using 4 nested IF() statements. Type in cell C11
‘=IF($C$2=50%,SDA_Schedule!B6,IF($C$2=100%,SDA_Schedule!C6,IF($C$2=200%,SDA_Schedule!D6,IF($C$2=300%,SDA_Schedule!E6))))'. If you receive an error, make sense you have the correct number of parentheses at the end.
b. Now calculate the probability of staying current on payments in column D. First, specify in cell B11 that 100% of borrowers at risk of default initially. The probability of staying current is the percent at risk minus those who default, or type in cell D11 ‘=B11-C11'.
c. Calculate expected cash flows based on the probability of defaulting or staying current each period in column E. First indicate in cell E10 the net loan amount. Next, calculate the weighted average expected cash flow in cells E11:E370. See above steps.
d. With the above assumed assumptions, a loss severity, and 100% SDA, you should find the estimated lender's yield is 4.23%.
Part B. Calculate the Effective Borrowing Cost of a loan incorporating loan origination fees and prepayment.
1. Calculate Effective Borrowing Cost (EBC) of the loan. Initially indicate in cell C8 an assumption the borrower prepays after 5 years.
2. Open worksheet labeled ‘Prepay'. Confirm in cell C2 month until prepay is linked to the value on the ‘summary' worksheet'
3. Indicate the net loan amount in cell B8 by linking to the ‘Schedule' worksheet. This number should be positive.
4. Use 2 nested IF() statements to calculate the anticipate cash flows paid by the borrower in column E. The first should indicate that if the month (A9) is less than month of prepayment ($C$2), then the normal scheduled payment is made. The second IF() indicates that if the
current month is equal to the month of prepayment, then the borrower will pay the scheduled payment plus the outstanding principal. Both of these numbers should be negative, but I'll leave the exact steps to you.
5. Calculate the EBC of the loan incorporating prepayment in cell C3 using the IRR().
6. You should find the EBC of the loan equal to 4.43% based on a 30y $416,000 FRM Loan at 4.2% with 1 point of origination fees.
Use the worksheets you created to answer the questions on the ‘Questions' worksheet in the Excel file. Please change the name of the file to indicate your last name first initial as a suffix and upload to Blackboard by the assigned deadline. Feel free to email me (mike.eriksen@uc.edu) with any questions.
Attachment:- Excel_Sheet.xlsx