MSR (Mortgage Servicing Rights) VBA Function

I have tried to stay away from VBA calculations for my Excel spreadsheets, principally because my programming does not have the polished look of a good programmer. Programming in VBA for me was self taught, and based on the “Basic” programming language I learned in the 70’s. Enough excuses.

Download Excel spreadsheet :”MSR

There are times when a VBA function is necessary. Particularly if you are doing  hundreds, if not thousands of calculations. This post and spreadsheet are concerned with the calculation of mortgage servicing rights (MSR), which is the present value of a future income stream, based on the servicing fee for a mortgage or pool of mortgages. The VBA is in the form of a function called “MortServicingPV”. FASB 157 guidelines require the valuation of MSR for individual loans as well as the total mortgage portfolio. There are a number of factors that can affect the value of MSR:

1. The current principal value of the loan

2. The gross interest rate on the loan

3. The servicing fee expressed as a percentage

4. The remaining months (term) the mortgage will amortize to zero

5. CPR (Constant Prepayment Rate), conventionally not entered as a percent.

6. If the loan has a balloon payoff, the number of months until then (default is zero)

7. The market rate currently being paid for servicing

The object below shows the calculation inputs needed for the function. Note that there is not a default rate (CDR). If there is an interest in default losses let me know and I can adjust the function. Also it is assumed that the valuation takes place on a payment date (no accrued interest).

Below are the inputs needed (yellow cells) and the MSR value outputs:

MSR1

Download Excel spreadsheet :”MSR

Below is the VBA Function:

Function MortServicingPV(MonthsToMat, Gross_Rate, Service_Fee, CPR, Yield, Optional Balloon_Month = 0)
‘Don Pistulka
‘All Rights Reserved
gr = Gross_Rate / 12 ‘Gross Coupon
bm = Balloon_Month
sf = Service_Fee / 12 ‘Service Fee
nr = gr – sf ‘Net Rate
rm = MonthsToMat ‘ Remaining Months
sb = 100 ‘Starting Balance
y = Yield / 12
smm = ((1 – CPR / 100) ^ (1 / 12) – 1) * -1 ‘ Monthly CPR
tm = rm ‘Total Months
t = 0 ‘Interest
st = 0 ‘Servicing Interest
sv = 0 ‘Sum PV of Servicing Interest
p = 0 ‘New payment
am = 0 ‘Normal Amortization
pp = 0 ‘Principal Prepayment
For i = 1 To tm
t = nr * sb  ‘Interest
If bm <> i Then
‘Start———————————————–Balloon Calaculation
p = Application.WorksheetFunction.Pmt(gr, rm, sb * -1)
Else
p = sb + t
st = sb * sf
sv = sv + (1 / ((1 + y) ^ i)) * st
MortServicingPV = sv
Exit Function
‘End————————————————–Balloon Calaculation
End If
st = sb * sf
am = p – t – st
pp = smm * (sb – am)
sb = sb – am – pp
sv = sv + (1 / ((1 + y) ^ i)) * st
rm = rm – 1
Next i
MortServicingPV = sv
End Function

12 thoughts on “MSR (Mortgage Servicing Rights) VBA Function

  1. Darren McCammon

    It appears you using the coupon rate as the discount rate to be applied for future cash flows? Do you have another version where I can use a specified discount rate on the income stream? (e.g. coupon rate on mortgages is 4.3% but discount rate on future cash flows is 11.5%)

    Here is a specific scenario, used by an existing public MSR provider I would like to run:
    Remaining Life 6.0
    Borrower Interest Rate 3.95%
    Discount Rate 11.56%
    Prepayment Rate 9.09%
    Default Rate 0.88%
    bps (ave servicing fee) 28.0

    It seems weird to me that you can have a prepayment rate of 9.09% and a remaining useful life of 6 years at the same time given that these are predominately 30 year loans.

    Reply
    1. Don PistulkaDon Pistulka Post author

      Hi Darren,

      First let me answer your last sentence. The prepayment rate is a constant (Constant Prepayment Rate) that does not change through the life of the MBS. If you used a PSA as the prepayment rate, that ramps up in the early years and then flattens out, you would need to adjust the prepayment rate in the calculations.

      I am sending you an amortization schedule that returns the value and price of servicing discounted at 11.56%. Your left out the current balance, so I used $1mm. The remaining balance is not needed if you are only looking for a price (in this case .591).

      Please respond to my email address if you have more questions.

      Don

      Reply
  2. Ashton

    I am creating a mortgage servicing model and was curious if I were to use this equation on a whole loan basis rather than a pooling basis, would I still account for the CPR in the same way?

    Reply
      1. Ashton

        Thank you for the quick response! And were you using a specific resource when quoting the “market rate for servicing”?

        Reply
        1. Don PistulkaDon Pistulka Post author

          Ashton,

          No. Servicing rights are traded mostly by firms that specialize in owning mortgage servicing. It can be hard to find market quotes.

          Reply
  3. Ashton

    If I were to use this equation on a whole loan basis rather than a pooling basis would I still account for the CPR in the same manner?

    Reply
  4. Pingback: Market Value Functions for Servicing (MSR), Interest (IO), & Principal (PO) – Excel@CFO

    1. Don PistulkaDon Pistulka Post author

      Default rates act the same as CPR in that the loans are gone and so is the servicing cash flow. The only difference between the CPR and defaults is that default rates are generally calculated after regular principal and CPR. I will send you an example using the spreadsheet AllVary.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

WordPress spam blocked by CleanTalk.