Assignment: Real Estate Finance

Objectives:

- Build an amortization schedule in MS Excel for FRM loans.

- Calculate the Annual Percentage Rate (APR) for each type of loan

Instructions:

Part A. Use MS Excel and the steps listed on the next page to build a loan amortization table for "standard" fully amortizing 30-year $416,000 fixed-rate mortgage (FRM) loan with a 4.20% annual interest rate, monthly payments, and 1 point ($4,160) of origination fees.

1. Download the MS Excel file titled "Assign1_F15.xls" under assignments on Blackboard.

2. Indicate your name in the space allotted in the worksheet titled "Questions."

3. Complete the steps listed on the following page to create an amortization schedule for the 30-year FRM fully amortizing loan listed above. The intent is to create a generalizable amortization schedule you can easily update to use on future assignments with different loan parameters.

4. Use the IRR() function embedded within MS Excel and the instructions listed on the following page to calculate the APR of this loan in cell C6 of the worksheet you created.

Part B. Copy the worksheet you created in Part A as a template to calculate the APR of a fully amortizing 15-year $416,000 FRM loan with a 3.48% annual interest rate, monthly payments, and 1.5 points of origination fees.

1. This should be simple if you followed the steps for Part A. Right-click the worksheet tab at the bottom of the page, select ‘Move or Copy,' click the ‘Create a copy' box, and select ‘Ok'

2. Change the parameters at the top of the copied worksheet to new loan parameters. Confirm $0 principal balance after 180 payments.
3. Rename worksheet to ‘15y FRM' Loan

Part C. Answer the questions listed on the first worksheet of the Excel file titled ‘Questions' using the worksheets you created in Part A and Part B of the Assignment.

1. Rename the file "Assign1_F15_`YOURLASTNAME''FIRSTINITIAL'.xls" and upload to Blackboard before the deadline listed above. For example, I would rename the file "Assign1_F14_EriksenM.xlsx."

2. Upload to Blackboard by the assigned deadline.

Building a FRM Loan Amortization Schedule in MS Excel

Step 1. Open MS Excel and enter the loan parameters at the top of the worksheet. If you are completing Assignment #1 for Real Estate Finance, enter the attributes of the loan in cells C2:C5 in the worksheet titled "Option A" in the file you downloaded from Blackboard.

A. Enter the length of amortization in years (e.g., 15 or 30)

B. Enter the contract rate of the loan in annual terms (e.g., 6%)

C. Enter origination fees of the loan in dollar values.

Step 2. Label cells A10:H10: Month, Begin Balance, Interest Rate, Interest, Principal, Payment, End Balance, and Cash Flow, respectively. This is already done in the file you downloaded for assignment #1.

Step 3. Starting with entering "0" in cell A11, fill-in consecutive values indicating each row represents a unique month for the total term of the loan.

A. While you could manually type by hand a unique number for each month, you could alternatively use MS Excel to automatically fill-in the values for you. After you type "0" in cell A11 and "1" in cell A12, use your mouse to highlight both cells and click on the bottom right of cell A12. While continuing to click on the mouse, drag the box straight down until the desired values are automatically inserted.

B. You should find if you are building an amortization schedule for a 15-year loan, the 180th payment occurs in row 191 of the MS Excel worksheet.

Step 4. Indicate the net loan amount inclusive of origination fees in cell H11. This should be a positive value as witnessed through the eyes of the borrower.

A. While you could manually type in the dollar amount in this cell, you will find in future assignments it will be helpful to have this number automatically update given different loan parameters.

B. This can be accomplished by making a cell reference to the parameters entered above. In particular type "=" in cell H11 and then type "C2-C5"

Step 5. Indicate the beginning balance of the loan in the first period in cell B12 by referencing the original principal indicated in cell C2.

A. You can either type in Cell B12 "=C2" or type "=" and then click on C2

Step 6. Similarly Indicate the contract interest rate from the loan in C12 by referencing the value indicated in C4, or typing "=C4"

A. While it may seem trivial to have an entire column representing interest rates for fixed-rate mortgage loan, this innovation will be crucial later in the semester when we consider adjustable-rate mortgage loan.

Step 7. Calculate the portion of the first monthly payment contributed towards interest in cell D12.

A. This is simply the beginning balance (Column B) multiplied by the interest rate you entered in Column C. Multiplication can be executed by MS Excel by typing "=B12*(C12/12)" in cell D12.

B. We could have similarly calculated the monthly portion interest associated with the mortgage payment using the IPMT() function embedded within MS Excel. Use the help function of the program if you are unfamiliar with using embedded functions in Excel.

Step 8. Calculate the first monthly payment in cell F12.

A. This is most easily accomplished using the PMT() function in MS Excel.

B. In cell F12 type "=PMT(C12/12, C3*12,-C2)" . Confirm this payment is correct on your financial calculator.

C. Multiply this payment by -1 using a * to indicate a net outflow by the borrower and indicate this negative amount in cell H12

D. Use the IF() function of MS Excel to set the monthly payment equal to 0 after the final payment (ie, balance equal to 0) to accommodate loans with different lengths of amortization. Replace what you have previously written in cell F12 to "=IF(B12>0.01,PMT(C12/12,C3*12,-C2),0)" . This will be useful when you answer questions pertaining to loans with different amortization lengths.

Step 9. Calculate the portion of the first monthly payment contributed towards principal in cell E12.

A. The easiest way to do this is by subtracting the portion of payment contributed to interest calculated in cell D12 from the total calculated payment in F12, or typing in cell E12 "=F12-D12"

B. We could have similarly used the PPMT() embedded within Excel to solve for this.

Step 10. Calculate the Ending Balance in cell G12.

A. This is simply the beginning balance indicated in cell B12 minus any portion of the payment contributed towards principal indicated in cell E12, typing "=B12-E12" in cell G12.

B. Note the beginning balance in the 2nd month is identical to the end balance of the 1st month. Make this explicit by typing "=G12" column B13.

Step 11. Calculate the interest, principal, payment, and end balance for the remaining months.

A. In Step 6 you previously entered "=C4" in cell C12 to refer to the interest rate of the loan. If you were to click on C12 and click on the bottom right corner and drag to C13, the new value for cell C13 would be "15" or "30" , or the amortization length, as the cell would now be referencing the value entered in C5. This is because whenever you move a formula to a different cell that references another, the relative position of the referenced cell to the one in the formula will be maintained.

B. Since you most likely don't want to have to refer to cell C4 179 more times, you can take advantage of the use of absolute references, indicated bys in MS Excel.

C. Instead of typing "=C4" in cell C12, alternatively type "=$C$4". The s indicate the same cell should be referenced in the formula regardless of the relative location of the formula to that cell. You should find when you click and drag C12 now to C13 the same value appears in both cells.

D. Similarly change the previous references in calculating the mortgage payment in cell F12. The new formula should be =IF(B12>0,PMT(C12/12,$C$3*12,-$C$2),0).

E. Highlight cells C12:H12 and then click and drag the bottom right corner of H12 to fill in the remaining months. Similarly repeat this step to indicate the beginning balance for each month in column B. Confirm the End Balance during the last month is $0.

Calculate the Annual Percentage Rate (APR) of the Loan.

A. This is best accomplished by using the IRR() function embedded within MS Excel.

B. In cell C6 type "=IRR(H11:H191, 0.01)*12" . I specify an initial guess of "0.01" since the IRR() will not always converge without one specified. Multiply the estimated value by 12 since by law the APR must be represented in annual terms.

C. If you receive an error message calculating the APR, check to make sure the cash flows entered in column H has at least 1 positive and 1 negative value. If that still does not work, try specifying different initial starting values.