Excel@CFO

You can tell I am running out of ideas. This is my second post in a row on Excel perpetual calendars. This one is for remembering all those birthdays in the family:     Download Birthdays.xlsx

So I was going through some old Excel files and came across a couple of calendars someone might find useful. When I was working, we were paid bi-weekly. In other words, every two weeks. There were three days I was concerned with: The end of the pay period (there was a five day delay between […]

A reader asked me if Average Life VBA Function For MBS could be changed from using CPR as the prepayment assumption to PSA. You can download the PSA version here: http://pistulka.com/Excel_Shared/vba_avlife_PSA.xlsm

One problem with budgeting interest expense, is that the typically credit union will have a majority of their CDs maturing during the next 12 months. The reinvestment rates for those CDs can make a significant difference to overall interest expense. This spreadsheet uses a data table of all CDs at the end of the current […]

A troubled debt restructuring (TDR) is defined as a debt restructuring in which a creditor, for economic or legal reasons related to a debtor’s financial difficulties, grants a concession to the debtor that it would not otherwise consider. —Wikipedia I have not used the linked spreadsheet in any real-world applications, so there may be errors […]

My last post was a comparison of my default CDR formulas (I will refer to this model as “my model” only because I have no idea who to credit) and that of an alternative model used as the industry standard. I put together a spreadsheet that, although not an official SIFMA product, gives an indication […]

A reader has questioned the model I use for calculating defaults (CDR) and loss severity. It was pointed out that my model uses the expected P&I, even though a stated percent of principal is in default each month. The reader also pointed out that my model is not the same as the Bond Market Association’s […]

In previous commercial loan amortizations I have used the standard 30/360 basis, compound interest amortization. More often than not, lenders will use simple interest for commercial loans. Don’t let the word simple in “simple interest” vs “compound interest” fool you.  Lenders are not looking to give borrowers a cheaper loan, based on simple interest. Chances […]

This is the fourth in a series of differently weighted butterfly swaps. I will adjust the list below, as I add more butterfly swaps: Cash & Duration Neutral Butterfly (aka Duration-Hedged Barbell) Fifty-Fifty Butterfly Swap Regression Weighted Butterfly Swap Maturity Weighted Butterfly Swap Note: Although this type of swap shows positive convexity at changes of […]

This the 3rd in a series of butterfly swaps. Cash & Duration Neutral Butterfly (aka Duration-Hedged Barbell) Fifty-Fifty Butterfly Swap Regression Weighted Butterfly Swap Maturity Weighted Butterfly Swap Note: Although this type of swap shows positive convexity at changes of + or – 25 basis points, it does not necessary display positive convexity for smaller […]

This is the second in a series of differently weighted butterfly swaps. I will adjust the list below, as I add more butterfly swaps: Cash & Duration Neutral Butterfly (aka Duration-Hedged Barbell) Fifty-Fifty Butterfly Swap Regression Weighted Butterfly Swap Maturity Weighted Butterfly Swap Note: Although this type of swap shows positive convexity at changes of […]

First in a series of butterfly Swaps. I will adjust the list below, as I add more butterfly swaps: 1. Cash & Duration Neutral Butterfly (aka Duration-Hedged Barbell) 2. Fifty-Fifty Butterfly Swap 3. Regression Weighted Butterfly Swap 4. Maturity Weighted Butterfly Swap This Excel spreadsheet shows how to set up a cash & duration neutral butterfly […]

I was playing with some assumptions for commercial loan amortizations and came up with the this Excel spreadsheet. For example: Assume the borrower wants to make payments quarterly, semi-annually, annually, or bi-monthly, instead of monthly, in order to match the borrowers source of income. You might want to oblige the borrower, but you still want […]

Just what everyone has been waiting for, another CPR vector analysis. I have at least two other CPR vector posts. This one allows for your own CPR model, with CPRs potentially changing each year. I say potentially, because one could keep the same CPR for numerous years. First enter a description of the pool on […]

Technical trading is a little out of my wheelhouse, but when I came across the exponential moving average (EMA), I thought it was interesting enough to build a model. You can do a Google search to see how various traders use a simple moving average (SMA) and an EMA. As is true in all my […]