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

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

21 Comments

  1. Is there a class or course that specifically teaches a few of these MSR valuations techniques from beginning to end with examples?

    1. Jeremy,

      I don’t know of any classes. It’s something you pick up by working in the MBS markets. It’s an easy concept, however. It’s the present value of the servicing cash flow of a mortgage pool. I listed a few of my post that reference serving specifically. For examples, download the attached Excel spreadsheets.





      Don

      1. Jeremy,

        The links I referenced did not show up in my reply. Try searching my site for the subjects below:

        Market Value Functions for Servicing (MSR), Interest (IO), & Principal (PO)
        MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity
        Math for amortization rows with CPR, Servicing, CDR, & Loss Severity
        MSR (Mortgage Servicing Rights) VBA Function
        Valuing Servicing Matrix
        Valuing Servicing With CPR

  2. Hi Don,
    Would you be able to provide the same spreadsheet that you sent to Darren McCammon (containing an amortization schedule that returns the value and price of servicing discounted at 11.56%)? Thank you so much for your help!
    Evan

  3. I read in the documentation that the Constant Repayment Rate is not typically expressed as a percentage. Our prepay speeds are all calculated as a percentage for IRR analysis, etc. Can you explain to me how the CPR if not expressed as a percentage is expressed and if you have a recommendation on how a percentage can be translated into a “non-percentage” factor for purposes of this spreadsheet? Thanks so much for this tool.

    1. Troy,
      It just seems to be the standard to not refer to it as a percentage, when it is. I am sending you a copy of this spreadsheet, that you can input CPR as a percent.
      Don

    2. Don would you be able to resend that spreadsheet? I am not sure what occurred but I am not able to find the spreadsheet with the revision you made to allow us to use a percentage as the CPR? Thanks again.

      For reference:

      Posted at 8:17 AM , March 29

      Troy,
      It just seems to be the standard to not refer to it as a percentage, when it is. I am sending you a copy of this spreadsheet, that you can input CPR as a percent.
      Don

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

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

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

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

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

  7. I would be interested in how default rates would affect MSR values

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

Leave a Reply

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