If this post looks familiar, it is supposed . My last post was “Market Price VBA Function For MBS (or single loans), Given Yield“. This Excel spreadsheet is almost the same function, except that this function solves for yield given price rather than price given yield. The main...

Continue reading »
I now also have another function to calculate yield given price see: Market Yield VBA Function For MBS (or single loans), Given Price Many of my previous posts and Excel spreadsheets have included amortization schedules that can calculate the price of MBS pools or single...

Continue reading »
In a recent post I showed math formulas for a pool of mortgages that could calculate any amount, for any month, without an amortization schedule. This post and spreadsheet takes these formulas one step further and creates names for the formulas. After entering a description of...

Continue reading »
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...

Continue reading »
In a previous post Simple Formula for Converting Compound Interest Rates and spreadsheet, I showed a conversion formula that allowed the user to convert an interest rate from one compounding period to another equivalent compounding period. For example, you could calculate the annual equivalent for a monthly compound...

Continue reading »
Remember to enter dates and rates in yellow cells only. The worksheet is protected, except for the yellow input cells, so you are not tempted to type over a formula. There is no password, so you can unprotect the sheet. Back in November of 2014...

Continue reading »
Back on September 8, 2015 I had a post call Chained Returns with an Excel spreadsheet. It was a calculator for chaining together periodic rates of return and calculating the annual yield (APY) and the periodic equivalent rate of return (APR). The data inputs (yellow...

Continue reading »
This is a stochastic tool that uses forecasts of what the Treasury yield curve will look like in six months, and generates holding period returns (total returns) at various points out the curve. The results are based entirely on the users best-guess estimates of the shape of future yield curves. This...

Continue reading »
I thought I would consolidate some of the past posts and spreadsheets into one workbook. It could come in handy when you need some quick answers, or just to check your calculations. I tried to give not only the Excel functions, but the math and alternative...

Continue reading »
Note: I have added a fourth method of calculating spot rates for the Treasury yield curve. The workbook is the same. For a brief explanation of the forth method: http://pistulka.com/Other/?p=2812 I have used what I am calling the “spreadsheet method” of bootstrapping spot rate...

Continue reading »
In previous posts, I have covered CPR, PSA, and a spreadsheet that allows for any prepayment vector you might want to create. There have been many attempts to describe what the prepayment pattern for MBS will look like in the future. One of the models that...

Continue reading »
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 »