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:

**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

Darren McCammonIt 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.

Don PistulkaPost authorHi 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

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

Don PistulkaPost authorBrian,

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

AshtonI 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?

Don PistulkaPost authorAshton,

That is correct. Enter a zero for CPR.

Don

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

Don PistulkaPost authorAshton,

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

AshtonIf 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?

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

Justin CohanimI would be interested in how default rates would affect MSR values

Don PistulkaPost authorDefault 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.