Market Value Functions for Servicing (MSR), Interest (IO), & Principal (PO)

Last year I posted an Excel workbook with a user defined function that calculated the market value of Mortgage Servicing Rights (MSR). I have taken the same workbook and added two more functions. These two functions are the market values of interest only (IO) and principal only (PO) on a mortgage loan. I am including the function VBA for all three on this post. If you want to know more about MSR go to the Mortgage Servicing Rights (MSR).  post.

Get the new workbook here: http://pistulka.com/Excel_Shared/VBA_functions.xlsm

Each yellow input cells is set at the gross rate for the market rate as a default, just to show that they add up to the par value.

Interest Only (IO) function:—————————

Function MortgageIO(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
tv = 0 ‘Sum PV of 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
p = Application.WorksheetFunction.Pmt(gr, rm, sb * -1)
Else
tv = tv + (1 / ((1 + y) ^ i)) * t
MortgageIO = tv
Exit Function
‘End————————————————–Balloon
End If
st = sb * SF
am = p – t – st
pp = smm * (sb – am)
sb = sb – am – pp
tv = tv + (1 / ((1 + y) ^ i)) * t
rm = rm – 1
Next i
MortgageIO = tv
End Function
Principal Value Only PO):——————-
Function MortgagePO(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
tv = 0 ‘Sum PV of Principal
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
p = Application.WorksheetFunction.Pmt(gr, rm, sb * -1)
Else
tv = tv + (1 / ((1 + y) ^ i)) * sb
MortgagePO = tv
Exit Function
‘End————————————————–Balloon
End If
st = sb * SF
am = p – t – st
pp = smm * (sb – am)
sb = sb – am – pp
tv = tv + (1 / ((1 + y) ^ i)) * (am + pp)
rm = rm – 1
Next i
MortgagePO = tv
End Function
Servicing (MSR):——————
Function MortgageS(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
p = Application.WorksheetFunction.Pmt(gr, rm, sb * -1)
Else
st = sb * SF
sv = sv + (1 / ((1 + y) ^ i)) * st
MortgageS = sv
Exit Function
‘End————————————————–Balloon
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
MortgageS = sv
End Function

 

 

Leave a Reply

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

*
*

WordPress spam blocked by CleanTalk.