This is a follow-up to my last post, Sequential Pay CMO. The purpose of this example spreadsheet is to show a simple example of how floating and inverse-floating rate securities can be produced from a fixed rate pool of mortgages. It is not the only...

Continue reading »
A reader ask me if I had published a sequential pay CMO example. I had not. I did start a spreadsheet at one time, but never completed it. I worked on it over the weekend and came up with a simple, three tranche and residual sequential. I’m going to...

Continue reading »
This is an update of a post and spreadsheet I wrote in Oct. of 2014 called: “Modified & Effective Duration, Plus Convexity of a Loan”. The previous post was hard-wired for a plus or minus 100 basis point change in yields. These new formulas and...

Continue reading »
In August of 2014 I showed a spreadsheet for interpolating a yield, given a range of dollar prices. That post was call Interpolation. I thought of another way to use the same linear interpolation. Lets say you want to know what the Treasury yield would be...

Continue reading »
First, let me say that this is my 100th post and spreadsheet. I started in August of last year. If this spreadsheet looks a lot like my last post, it is very close. My last spreadsheet showed how to calculate any row of an amortization schedule,...

Continue reading »
After you read this post, check out my updated post In my post called The Math Behind Excel’s CUMIPMT() & CUMPRINC() Functions I showed how to calculate a single row of a simple amortization schedule, using math instead of the amortization schedule. In this post and...

Continue reading »
There is a newer post that replaces the amortization schedule to calculate historic PSA with a UDF: http://pistulka.com/Other/?p=2964 Back in December of 2014 I wrote a post and Excel spreadsheet called Reverse Engineering Constant Prepayment Rate (CPR). I called it that because instead of giving a...

Continue reading »
There are all kinds of calculators you can find to “optimize” your rate of return by laddering certificates. Of course the term optimize is a misnomer because you don’t know what the reinvestment rate will be on the shorter term CDs. There are some advantages to laddering...

Continue reading »
See updated formula at: MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity http://pistulka.com/Other/?p=2384 This adjustment to the mega formula for mortgage-backed securities only affects those that use an odd first payment, by entering an amount other than 30, in the “Payment...

Continue reading »
Calculating the total rate of return on a chain of returns produces a time weighted rate of return. I covered this in my post “Time & Dollar Weighted Rates of Return Calculator“. In this spreadsheet I have segregate the time weighted return into an easier...

Continue reading »
If you normally sell your car before it is paid off, you are going to want to stay away from lenders that still use the Rule of 78 (also known as sum-of-the-digits) to calculate the interest you have accrued. This method was used before the technology was...

Continue reading »
Note: I have made some changes to the spreadsheet since the original post. The Treasury data source was not pulling in the new rates as I had expected, so I hard wired a table of rates for February of 2016. A link to the Treasury page is still there, so...

Continue reading »
It occurred to me that my last post Treasury Bill Math with twelve formulas, has little value if you are sitting on a trading desk and need to work quickly. I put together this calculator that will calculate two of the four pieces of information, given two...

Continue reading »
Money market securities mature in less than a year and pay simple interest (as opposed to compound interest). Treasury bills are money market instruments that pay simple interest, but are quoted (sold) based on a discount rate. Securities that use a discount rate can be thought of as paying...

Continue reading »
In May of this year I offered a template of gages that a board of directors might find useful to spot problems in their credit union or small bank. Read the post Financial Ratio Gauges for more information on why I wrote the financial gages spreadsheet. The...

Continue reading »