Excel Solutions and Spreadsheets for the Financial Analyst & CFO
In February of 2017 I posted an Excel spreadsheet called “Internal Audit – Interactive Report”. With the newer functions in Excel, I thought I might try a different approach. I used nine boxes to graphically show which areas in the bank or credit union have the most risk to the organization. First, I listed various […]
Firsts let me state that I personally have never been involved in buying or selling pools of auto loans. This workbook represents how I might approach the analysis of a pool of auto loans, but it is not meant to be compliant with any industry standard methods. Because this program has not been used in […]
The attached Excel workbook was based on Microsoft 365. Earlier Excel versions may not work properly. I know this may look “old school” but then consider the author. This Excel workbook gives ideas on how you might want to set up a consumer rate pricing model, with four ways to calculate FICO scores. I […]
Warning: Some MS 365 formulas that might not work with prior versions of Excel. Back in April I publish a post called Daily Treasury Security Rates. That post featured an Excel workbook that downloaded U.S. Treasury rates for the current month. This new workbook has Treasury rates starting 1/2/1990 and can be updated daily, when the […]
Smaller credit unions and banks may not have access to mortgage pricing models. This post is an example that might get some institutions started in building their own models. Fannie Mae now posts their commitment rates for 15- and 30-year mortgages daily. The commitment rates are for 10, 30, 60, and 90 days. This Excel […]
Recently, I have had more requests for specialty amortizations. This workbook is intended to give readers a list of example amortizations that can be used as starting points to produce amortization schedules that fit their needs. The examples have not been used in real world situations, so if you find bugs, let me know. Here […]
At first glance this may seem to be out of my normal range of spreadsheets, most of which are financial based. Anyone that has worked at a credit union or bank, however, knows that there are at least three or more times a year that an association or client will hold a golf tournament. Anyway, this […]
I have not posted any new Excel workbooks for some time now, mostly because I have been retired for eight years and I have run out of ideas. There are some dynamic array functions contained in the workbook that will require the latest version of Excel. The U. S. Treasury has a post called “Daily Treasury […]
How many realistic payment schedules with dates can you think of? I came up with 12. Now, regardless of the payment cycle, how many compound interest methods can be applied to the 12 payment cycles? I came up with 15. This amortization schedule therefore has 180 (12 x 15) combinations of payment and compounding methods. […]
I have been retired for several year now, but back in the day, we determined the interest rates we charged on consumer loans by using tiered FICO scores. The FICO scores were tiered, so that for example, everyone between a score 620 and 674 got the same rate on their loan. The borrower with a […]
Excel’s functions for present and future value of cash flows with dates, will not work with the present and future value of an amortizing loan with dates based on an actual/360 basis. A few years back, a young man contacted be about calculating the present value of an investment made at his firm. He had […]
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 […]