Mortgage Pass-Throughs in Excel

In this module we're going to go through the mechanics of mortgage pass through

securities. And we will do that within the context of

our Excel spreadsheet, which I hope you have with you.

You can download that Excel spreadsheet with these modules and mortgage backed

securities. We're going to look at the second

worksheet in this Excel workbook. It's going to be about mortgage pass

throughs. We'll discuss the mechanics of mortgage

pass throughs. And see how the, how the cash flows of a

mortgage pass through are created, and passed on to investors.

The second worksheet on the XL workbook that goes along with these modules, and

mortgages, and mortgage backed securities is called pass through.

This worksheet shows the mechanics of a simple pass through mortgage backed

security. Now, I just want to state in advance.

Don't worry too much if it takes you some time to figure out.

What the various self formulas are doing. it's not a hugely important part of this

course. What I want you to come away with is a

basic understanding of the mechanics of how these kinds of securities work.

So what we have here is the following. We've assumed that the pass through.

Now remember, a pass through mortgage backed security is constructed.

From a pool of underlying mortgage loans. So we might assume, for example, that the

mortgage balance, or here, the remaining mortgage balance, is 400.

That might represent $400 million for example, representing a large pool of

underlying mortgages. We're going to assume that the mortgage

rate, so if you like, this is the coupon rate on the underlying mortgages.

It's 8.125%. The pass through rate is 7.5%.

Now, what is the difference between these 2 rates?

Well, the pass through rate is the rate that gets passed on to investors.

And it will always be less than the mortgage rate.

And that difference accounts for the fees associated with servicing.

The mortgage-backed security, in this case the pass-through.

Somebody has to organize and manage and service the mortgage-backed securities.

They need to be paid a fee for doing so. That fee is represented by the difference

between the mortgage rate, 8.125%, And the pass thru rate 7.5%.

The initial monthly payment, or if you like, the average monthly payment, that

the underlying mortgage owners are paying, is 24.989.

The seasoning, so this is how old the mortgage pool currently is, it's 3

months,and the term of the loan of the underlying mortgages is assumed to be 20

months. Now of course this is very unrealistic in

realty you would be in a much larger pool certainly for mortgages or mortgage

backed securities that have just been initiated.

But I've assumed 20 months here, just so that we can see all of the payments on

one screen. In reality, this might start off as being

240 or 360, corresponding to 20 or 30 year loans, respectively.

We start off in Month 1, and let's work across.

So, we have our conditional prepayment rates.

So, this is our CPR, which is expressed as an annual rate.

It tells you what percentage of the outstanding mortgage at the beginning of

the period will be prepaid. Of course, we will need convert this into

a single month mortality rate, and that's what we do here.

The SMM gives us the percentage of the mortgage balance that is prepaid that

month. We have an initial beginning monthly

balance of 400. The monthly payment is 24.989 as we

caluculated earlier. This is the monthly interest paid in by

mortgage holders. And this is the monthly interest paid out

to the past through investors. Note that this interest.

Payment here 2.5 is less then 2.71 and that's because of the difference 8.125%

and 7.5%. Finally the scheduled principle repayment

is 22.281. That's equal to E13 minus F13.

So that's the monthly payment minus the monthly interest paid in.

And then finally there are some prepayments.

As I mentioned in the last module. Sometimes mortgage owners prepay, and

they might do so for various reasons. Maybe they're selling their house and

moving to another location, maybe they've gotten divorced, maybe they've had a

flood or a fire, maybe they've defaulted on their payments.

And all those situations What the mortgage investors see, investors in

mortgage-backed securities see is the mortgage being prepaid.

So we're going to assume that these prepayments take place according to this

schedule, the cpr. And SMM schedule and we get a prepayment

of 2.53. So therefore the total principle payment

is .253 plus 22.281 and that gives us 22.533.

We can therefore subtract that from the initial mortgage balance of 400 to give

an ending mortgage balance of 377.47. We then move on to the next time period,

time period two. We see that the conditional pre-payment

rate is increased to one percent. We get our single monthly mortality rate

and so on. We know our beginning monthly balance is

377, this is the same monthly balance we had at the end of the previous period.

We get our monthly payment. Now notice our monthly payments are now

no longer the same constant. So in our last module we had a constant B

being paid in every period. We would've the same constant B being

payed in every period here, if there were no prepayments.

But there are prepayments. Those prepayments actually reduce the

outstanding principle by more than what you'd expect.

And therefore that changes the B in each period.

So if you, if you look carefully at the formulas here, you'll see that this

formula is the same formula we assume to calculate B.

In our earlier module. But of course we have to keep

recalculating it in every period to adjust for the fact that prepayments are

taking place. So what I want you to get from this

spreadsheet is basically just how the repayments are calculated.

How the interest payments are calculated. The interest payments paid out to past

due investors are calculated. And the fact that prepayments take place

and that these prepayments Alter the outstanding mortgage principal that

remains at the end of every period. Now this is an idealized world, we don't

have any defaults. We don't have any randomness in our

prepayments. We're assuming prepayments occur

according to this deterministic schedule given to us by the CPR.

That's all fine as I said. In the real world of course You have to

take these into account, but for our purposes we just want to understand some

of the mechanics behind how these mortgage backed securities are created.

And the simplest type of mortgage backed security is what is called the mortgage

pass through. Where you just pool a whole series of

loans together and then you pay out the principle and interest on those loans out

to the investors in the pass through security.

金融工程与风险管理，第 1 部分

您将学习的技能

审阅

KA

SM

教学方

Martin Haugh

Garud Iyengar

探索我们的目录

免费加入并获得个性化推荐、更新和优惠。