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

Comments

  1. Darren McCammon says:

    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. Don Pistulka Don Pistulka says:

      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

      1. Brian says:

        This is a great find. Thank you. Would you be able to provide the same file?

        1. Don Pistulka Don Pistulka says:

          Brian,
          When you say “provide the same file”, the file is hyperlinked at “Get the new workbook here: http://pistulka.com/Excel_Shared/VBA_functions.xlsm“. The VBA is not protected. I will email you a copy of the Excel file in any case.

          Don

  2. Ashton says:

    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. Don Pistulka Don Pistulka says:

      Ashton,

      That is correct. Enter a zero for CPR.

      Don

      1. Ashton says:

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

        1. Don Pistulka Don Pistulka says:

          Ashton,

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

  3. Ashton says:

    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?

  4. Justin Cohanim says:

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

    1. Don Pistulka Don Pistulka says:

      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 *

*
*

WordPress spam blocked by CleanTalk.