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

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

I will e-mail you.

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.

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

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.

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

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

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

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?

Ashton,

That is correct. Enter a zero for CPR.

Don

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

Ashton,

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

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?

[…] MSR (Mortgage Servicing Rights) VBA Function April 21, 2015 […]

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

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.