I have posted two other examples (and Excel workbooks) of CMO structures in the past.  Floater & Inverse-Floater CMO and Sequential Pay CMO. Now, we are going to look at a CMO PAC (Planned Amortization Class). It is a simple example, with only two tranches, the PAC and Companion (or sometimes called Support) bonds. The PAC […]

Not all banks treat leap years the same, when calculating interest on deposits. Many financial institutions use basically the same method (or a close variation) I used in this formula. Check with your bank before relying on this method. The method I am referring to is: Interest is compounded daily. The balance on any given […]

My first post on this blog was in August of 2014 and it was called: No XNFV Excel Function? The idea was a formula for calculating future value with dates. There was a XNPV function for present value, but no function for future value. My formula looks like this: =SUMPRODUCT((1+APY)^((MAX(Dates)-Dates)/365)*Data) Of course there are other workarounds, […]

I have posted previous calculations of effective duration and convexity for a pool of mortgage loans, however past spreadsheets required three amortization schedules, on separate sheets. Using the mega formula for pricing, this spreadsheet (in my opinion) is cleaner and easier to understand. Effective duration is used to analyze mortgage pools, rather then Macaulay or […]

Before I added default and loss severity to the mega formula for the price of a mortgage pool, I produced a price/yield table. That old post was http://pistulka.com/Other/?p=707.  This post and Excel workbook includes the added variables. As is true with all of my spreadsheets, only the yellow cells are input cells:    * Servicing – […]

In May of 2015 I published a post called “3 -Ways to Bootstrap Spot Rates for the Treasury Yield Curve”. There was also an Excel workbook linked to that post. The workbook link is: http://pistulka.com/Excel_Shared/3_Spot_Rates.xlsm Rather than posting a new workbook, I am leaving the old workbook, but I added a new sheet called “Fourth Method”. […]

I have to be honest. I had never heard of a flat rate loan, until Rishi asked me to create an Excel spreadsheet for one. According to Wikipedia, flat rate loans are: “Loans with interest quoted using a flat rate originated before currency was invented and continued to feature regularly up to and beyond the […]

This is my third post on RMD, thus the 3 point 0. The other two post are: http://pistulka.com/Other/?p=2422 http://pistulka.com/Other/?p=2674 Read my first post if you are not familiar with RMD. This Excel workbook came about at the request of a reader who’s firm manages hundreds of retirement plans. I put together a spreadsheet to calculate […]

Important This post and spreadsheet has been updated. Please click this link to Defeasance 4.0   If you are involved in commercial lending or perhaps municipal bonds you are probably familiar with the term defeasance. Commercial loans normally have hefty prepayment penalties, while some loan documents prohibit prepayment altogether. Commercial loan defeasance is a collateral […]

If you search my blog, you will find dozens of amortization schedules produced by formulas. So why would you need an amortization produced with VBA? Every time you change any inputs, you have recalculate the schedule, while all my other amortizations are automatic. I created this spreadsheet years ago, just to practice my VBA. Even […]

This is not an academic or financial post, but a fun look at the large number of possible paths available in the binomial lattice used in certain types of financial models. Although there is a small model in the accompanying spreadsheet, this post is mostly concerned with calculating the number of paths and the probability […]

James was searching the web, when he ran across my Taxes workbook and the sheet called Required Minimum Distribution (RMD). He was looking for an Excel spreadsheet that calculated the net balance each year on tax differed accounts (IRR, 401k, etc.) after the return on investments and the withdrawals due to RMD. Rather than starting […]

A reader came across my post called  Loan Pool Named Formulas, Without An Amortization Schedule . This post and spreadsheet showed how, by using mathematical formulas, any month in a mortgaged backed security amortization schedule can be calculated, without creating the whole amortization schedule. The reader asked if the formulas could be adjusted to calculate the interest on a loan for any given […]

First a Warning!! To use this spreadsheet, you need excel 2013 or higher, due to the use of table slicers (not to be confused with pivot table slicers). Internal Audit (IA) frequently reports to the board or supervisory committee. Typically, these groups are concerned with the areas of the firm that have the highest risk to […]

For those that follow my blog, you know that I have used amortization schedules extensively in my analysis. This post and spreadsheet takes a closer look at the default and loss severity assumptions. In previous spreadsheets, the defaults and loss severity have taken place in the same month. In other words, there has been no delay […]