Anyone have to deal with how to account for adjustable rate mortgages? I have to come up with a model in excel illustrating how to amortize/accrete the discount/premium on the bond (the ARM) to par, 100. Some of the inputs to the model would be prepayment rate and a changing coupon rate. Some readings suggest using the Level Yield method. Anyone come across this problem? any advice?
There is software on the market that can do it for you. Why not pay the $$$ to buy one of them? Doing ARMs are not an easy task
Thanks for your suggestion… I will look around for some off the shelf software solutions but the thing is we need to show our clients all the calculations in Excel of how its done. Their risk management needs to get a handle on the nitty-gritty stuff. I have the cashflow amortization nailed down (for different prepayment rates and coupon rates) and it compares just fine with Bloomberg ARM screens. Now I need to get the bookvalue amortization part working. We touched this topic in Level-I when we used the effective yield at purchase to calculate the periodic income. The ARM amortization would be a bit more complex than that and i have been reading the FASB-91 for some guidance. They recommend using the “restrospective” method. I was just hoping someone on this board would have encountered this problem.
The off the shelf solutions start at 30k. What is your e-mail. Are you looking to just model one or two?
Forgive my ignorance on this, but what is the problem?
sorry for the delay in responding. email: yodhava at yahoo The problem is that I have to model a book value amortization projection for an ARM pool. If the ARM pool was bought at a price of 101 for a par amount of 1,000,000, then starting bookvalue will be 1,010,000. The premium of 10,000 paid has to be amortized through the life (weighted average maturity) of the pool to 0 so that the bookvalue equals 1,000,000 in the end. Every month the projection will calculate a new ending bookvalue which would be the carrying value of this investmest on the financial balance sheet (quarterly or year-end). The amortization projection has to incorporate a prepayment rate assumption (in CPR) and should not be far off the actual prepayment experienced by the pool over time. The market yield at the time of purchase is used to find out the monthly income and the difference between this income and the coupon payment would be the amount by which the premium is paid down every month. Now this is what I am having a problem with. Over a predetermined interval, say every 3 months, if we find that the actual CPR prepayment experience for the pool has been way different than the initial CPR assumption, then going forward, a new CPR rate has to be assumed. 1) How would one incorporate this change to recalculate a “new” effective yield. would this new yield calculation only look at the future cash flow projections (principal+coupon), or would it be a meld of actual cash flows experienced so far and the future cash flows projections from that point onwards? 2) Would that new yield be applied right from the beginning or would it be used just for future bookvalue calculation from that point onwards?
I would think (although I’ve never done this particular variation) that modeling the potential cash flows and yields, and thus book values, would be best captured by a monte carlo simulation. If it could be redone quarterly based on the CPR path and then the premium/discount could be amortized using some kind of smoothing mechanism determined by you or the client to best fit their needs or the law if there is some specific accounting treatment required. Like I said, I’ve never done this specifically and I may be too naive about real estate backed pools to know if it would work in your case.