Excel Solutions and Spreadsheets for the Financial Analyst & CFO
This is not meant to be an academic work on the predictive abilities of forward Treasury yields. See the expectations hypothesis. Rather, it is a spreadsheet that, given a date in the past, calculates forward rates and then compares the forward yield curve to the actual yield curve on that future date. Thanks to the […]
In July of 2015, I posted an animated yield curve chart:http://pistulka.com/Other/?p=1691. I have hopefully improved the chart and brought the yields curves up-to-date. You can also add daily curves if you like. The Excel file contains VBA, so some firms might not allow downloads. The yield curves are on a monthly basis from 1962-1989 and […]
The reason this is version 4.0 is that prior to this post and spreadsheet, I had three other posts on defeasance. With the addition of using Treasury spot rates, rather than posted yields on current coupons or Treasury strips, I hope to increase the accuracy of the calculations. If you are involved in commercial lending […]
I have posted spreadsheets on forward yield curves in the past, but this one is less cumbersome in that the spot rate calculations are simplified, compared to the previous spreadsheets. In addition, I added a forward math sheet to explain the calculations. Like my other spreadsheets that require spot rates, I start with a source […]
Note: I found an error in the spreadsheet and repaired it, so if you downloaded the spreadsheet before 3/21/19, download it again. This post starts by entering the U.S Treasury Yield curve by copy/pasting the rates from the Treasury web site. The yields are then interpolated in order to calculate spot rates using the […]
This is a second in a series of pricing methods, other than the normal present value of cash flows using one discount rate (yield). In the last post we considered pricing MBS, or any amortizing loan, using the U.S. Treasury spot rate Z curve. This post takes the previous post one step further and uses […]
The traditional method for calculating the price on mortgage backed securities (MBS) is to calculate the present value of each cash flow received from the security by one discount rate (market yield). Another method would be to use more than one discount rate, the Treasury spot rate curve, plus an add-on spread referred to as […]
A reader asked for help in creating the spot rates for the Federal Home Loan Banks market reference curve, used for amortizing advances. I have posted four ways to calculate spot rates for U.S. Treasury securities (A Forth Way To Bootstrap Spot Rates), but this is the first non-uses the same basic calculations, but uses […]
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 […]