Wholesale mortgage issuers publish their rate sheets in tremendous detail.
Review the below mortgage rate sheet. Do some research and figure out what is meant by:
- The numbers in the 30 year column.
- The 15-day, 30-day, etc column headings
- The numbers in the body of the table.
Use the links below to dowload a mortgage ratesheet. Alternatively if you google “Mortgage Rate Sheet pdf” you may find others.
Use the rates sheets from the picture above to model out at least three loans and determine their all-in cost, after considering upfront fees.
What APR would you expect? (Hint: You must use XIRR to figure this out).
https://download.flagstar.com/rates/RBR.pdf
http://www.coasthills.coop/rates/Mortgage_Rates.pdf
https://wholesale.guildmortgage.com/guildpub/wsrates/San%20Diego%20Guild%20Wholesale%20ratesheet.pdf
http://www.mortgagesbywms.com/tools/ratesheets/sheets/wells01.pdf
http://www.cmgfi.com/wholesale-ratesheet.pdf
Financial Modeling and Data Analysis 
None of the links work….
LikeLiked by 1 person
Here – let me google Mortgage Rate Sheet PDF for you.
Click to access Prime_Plus_Pricing_Sheet.pdf
Click to access plaza_slc_rates.pdf
Click to access r_fha_brokers.pdf
Click to access CMSRates_Current.pdf
And when you’re ready:
Click to access The%20Rapaport%20Diamond%20Report%20round%2009-07-12.pdf
LikeLiked by 1 person
For “model out at least three loans” does this mean, for example, model out a 15 year fixed, 20 year fixed and 30 year fixed off of one of the links?
Also, what does it mean to use the “Fannie Mae 30 Year Fixed” screenshot?
LikeLiked by 1 person
Cindy,
You *could* model a 15, 20, and 30 year mortgage, (and if you completed that don’t redo it) but I think it would be more interesting to show three loans from the either the same column or the same row. In the same column, you would expect to see the same annual effective interest rate (XIRR). If you look go across a row, you’d expect to see the pricing of the interest rate lock between 10, 30, 45, and 60 days.
LikeLike
So going across the same row, what happens to the rate after x number of days. (i.e. what happens are 10 days?)
LikeLike
There is a cost for a longer closing period. That cost can easily be calculated as an upfront dollar amount based on the table. For the 3.25% loan, for example, if you close in 10 days, you’re at 96.398 and if you close in 60 days you’re at 96.009. This means that on the 10 day close you would have to pay an upfront closing fee of 100-96.398 or 3.602%. And on the 60 day close it would be 100-96.009 or 3.991%. So, on a $100,000 mortgage, that’s the difference between upfront fees of $3,602 and $3,991. You can think of that incremental $389 as the incremental cost of the swap for an extra 50 days.
LikeLike
On our sample cashflow for each loan, all the numbers are negative, which means that =XIRR doesn’t work. Where would the postive amount for the cashflow come from that is required in order for the XIRR formula to work?
LikeLike
Look at it from either the borrower’s perspective or the lender’s perspective. Pick one. From the lender’s perspective, they’re shelling out $100,000 on day one, plus or minus some fees. Let’s say fees are $1,250. So their net out of pocket is $98,750. That’s a negative cashflow from the bank’s perspective. Each month after the loan is made, however, the bank is going to be receiving, say, $600. So the cashflow stream is -98,750, +600, +600, +600 … and there are going to be 360 of the +600s. So, you’ll have a one month period of negative cash flows and a 360 month of period of positive cash flows. Does that make sense?
LikeLike