[HW 2.3b] Homework – Mortgage Ratesheets

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).




  1. None of the links work….

    Liked by 1 person

  2. 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?

    Liked by 1 person

    1. 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.


  3. barroyo2015 · · Reply

    So going across the same row, what happens to the rate after x number of days. (i.e. what happens are 10 days?)


  4. 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.


  5. barroyo2015 · · Reply

    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?


  6. 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?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: