PDA

View Full Version : Building a PAC in Excel


Allacalander
02-20-2009, 11:45 AM
I have found most of the details in the study notes regarding how PACs and related issues work to be inferior in describing the basics well enough. (Carmody does a little better, but still keeps it too simple.) That makes understanding the more complex aspects, like effective collar, even tougher to "see".

To attempt to remedy this, I built (or tried to build) a model in Excel that would show the cashflows for a 5% PAC and its companions based on a pool of 10 identical 6% 100,000 mortgages.

I'm trying to construct it so that you can specify a PSA level and see if and when the PACs break. I know its not the greatest since actual prepayments don't follow any model exactly, but it should be enough to get the idea, and see the effective collars at work.

After some more work I intend to post it here for everyone else to look at, but I'm having some serious problems building the darn thing. I tried to find an Excel example online so that I could see how they built it, but no luck.

Has anyone else tried to construct this? I think it might be valuable to do so since PACs come up in several places. I could easily see a PAC question on the exam. If you have done one, could you post it so I can determine where mine is going wrong?

Size17
02-22-2009, 02:07 AM
I found something over here, looks like it has a PSA option, and two classes....my apologies if it is no good, my brains kinda mush right now so I didn't evaluate it.

http://pages.stern.nyu.edu/~cliu/mbs.html

Size17
02-23-2009, 03:52 PM
defaultrisk.com has a ton of information as well.

Allacalander
04-14-2009, 12:18 PM
I was able to back into the exhibits in Carmody's study manual. The biggest problem I was having was determining the general payment each year. This does not stay constant, but reduces each year because a portion of the mortgages are paid off and thus do not continue to pay.

I could show everyone how I got it, but its more instructive if you figure it out yourself.