00 01 Assignment Sp 2024 (student)

docx

School

University of Notre Dame *

*We aren’t endorsed by this school

Course

5432

Subject

Finance

Date

Apr 3, 2024

Type

docx

Pages

Uploaded by DeanUniverseAardvark10

Name: Assignment #1 (ARM vs. FRM over time) Real Estate Finance Dr. Conklin Due by 8:59am on the due date (see eLC for due date) ; eLC assignment drop box will close at this time; no late assignments accepted; also must hand in a hard copy of Answer Sheet tab in class on due date ) Instructions: This is an individual assignment. Each student should create their own deliverables. You are not allowed discuss the assignment with classmates. Posted on the course website is a spreadsheet titled “00 01 Interest Rate Data” that is to be used for this assignment. Download the spreadsheet and complete the following tasks. You will need to use absolute and mixed references in this and future assignments (for a brief tutorial see http://www.excel-easy.com/functions/cell-references.html ). Since Excel is the industry standard, a goal of this assignment is to get you familiar with some of the TVM concepts within Excel as they apply to mortgages. We discussed the differences between adjustable and fixed rate mortgages. Now, we want to perform some analysis with some actual data to put what we’ve learned into practice. The goal of this assignment is to look at what you would have paid on each type of loan, had you chosen it at a specific point in time. For example, if you chose an ARM in 2001 and held onto it for five years, would you have paid more than if you selected a fixed rate mortgage in 2001 with the same holding period? Download the “00 01 interest rate data” spreadsheet on eLC. The data contains historical interest rate data in the “Rates and Points” tab. 1 The 30 year rate column has the 30 year FRM rate for the given year. The 30 year points column has the fees (in percentage terms) that you would have paid to get that rate. So, for example, if you took out a 30 year FRM in 1988 your interest rate would have been 10.34% and you would have paid 2.06 points to get the loan. The spreadsheet also contains interest rate information for a one year adjustable rate mortgage. The 1 year index column is the one year constant maturity treasure (CMT) rate that serves as the index for the mortgage. The 1 year margin is the margin on the 1 year ARM originated in that given year. The 1 year points column gives the points to get the 1 year ARM loan. So, if you took out a 1 year ARM in 1988, you would pay 2.29 points at origination, and your rate for the first year would be 10.4% (7.65% + 2.75%). In 1989, your rate on this loan (that you took out in 1988) would be 11.28% (8.53% + 2.75% ). For each year from 1988 to 2011, I want you to compute the all-in costs for a $500,0000 FRM and a $500,000 ARM. Assume each type of loan is amortized over 30 years, with monthly payments, and that your holding period for the loan is 5 years (e.g., you will pay off the remaining balance at the end of 5 1 Underlying data: http://www.freddiemac.com/pmms/pmms_archives.html ; https://fred.stlouisfed.org/series/DGS1

years). The all in costs should be in dollar terms (all-in costs = points at origination + total of monthly payments over holding period + remaining mortgage balance at the end of year 5). In calculating the all in costs, make sure that your spreadsheet is flexible. If, for example, I wanted to see the all in costs over the different years for a $600,000 loan, I should just be able to change an input cell for loan amount, and all of the calculations (and graphs discussed below) should update accordingly. This means you will probably want to think carefully about how you set up your spreadsheets/calculations ahead of time (Hint: You should not be calculating everything by hand and just manually entering it into the spreadsheet. But, it probably makes sense to manually check in your financial calculator some of the calculations in Excel. This is particularly important so that you can be confident that your absolute and mixed cell references are working the way you think they are. In fact, this is exactly what I did when I created and completed the assignment myself). You have a lot of freedom in how you set up your spreadsheets/calculations, and there is no single “right” way to do it. I would suggest that you do most of the grunt work in a sheet other than the Answer Sheet Tab. The Answer Sheet Tab should be fairly “clean” as you will be printing this out as part of your deliverables. There is interest rate data through 2015, but you are only calculating all-in costs for loans originated up until year 2011 because you need 5 years of ARM index data to calculate the ARM all-in costs. Here is the information I would like you to complete in the “Answer Sheet” tab. By the end of the assignment, all yellow highlighted cells should be filled out: 1) Put your name (first and last) in cell B1 in the “Answer Sheet” tab. Put your UGA ID # in cell A2. Put your section time (12:45 or 2:20) in cell A3. 2) Put the all in costs for a FRM (ARM) originated in each year in cells B7 – B30 (C7 – C30). In cells D7 – D30, put the differences in all-in costs between the two (FRM – ARM). 3) Create a graph that plots the all-in costs of the 30 year FRM and 1 year ARM over time (you should use a scatter plot to do this; this can be found under the “Insert\Charts” tab at the top of the spreadsheet). There should be two different lines on the chart. Year should be on the X- axis. Title the graph “Total Costs FRM vs. ARM.” Also include a legend for the lines and anything else you think is appropriate. Basically, make your graph look professional (presentation will count towards your grade). Make the minimum value on the Y-axis $500,000 so that it is easier to interpret the graph. 4) Create a separate graph that plots the difference in all-in costs (FRM – ARM) over time. Title the graph “Additional Cost on 30 Year FRM $500,000 Loan with 5 Year Holding Period.” Adjust the formatting of the graph to make it look professional. 5) In how many years (and what fraction of total years), would you have paid more by taking out the FRM? Put your answers in cells E33 and E34. 6) What is the maximum dollar benefit of the ARM over these years? Put your answer in cell E36.

7) What is the maximum dollar loss by taking the ARM over these years? Put your answer in cell E37. 8) What is the expected dollar benefit of taking the adjustable rate mortgage based on this historical data? Put your answer in cell E39. 9) Are there any problems or shortcomings of the analysis? What are we leaving out? Is there anything surprising about the analysis? Put your answer in A42. Use the wrap text feature and increase the cell length so that it is easy to read when you print it out. 10) Given the information you have now, would you select an ARM when buying a property? Why or why not? There is no right or wrong answer here, as long as you provide a reasonable defense. Put your answer in cell A45. Use the wrap text feature and increase the cell length so that it is easy to read when you print it out. Deliverables: 1) Save your spreadsheet as “lastname_firstname_assignment1” and submit the file on the eLC dropbox by 8:59am on the due date. 2) Print out a hard copy to hand in during class. Includes: - Answer Tab - Graph from #3 above - Graph from #4 above *Staple these three items together (I will not have a stapler in class; please do this ahead of time) *Note: Deliverable #1 and #2 are both necessary to get credit on this assignment

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version