When purchasing or selling a residential mortgage pool between credit unions, or other financial institutions, accrued interest must be considered. One way to get around accrued interest is to settle on the 1st of the month. That is not always practical however. The first of the next month might be a weekend, or a large pool might have some loans […]

I will start off by saying that this template only works with Excel 2013 or higher, because we need to use slicers with a table. This dashboard template might be of interest to the CFO or financial analyst that prepares the monthly presentations for the board, finance or ALCO (Asset/Liability Management ) committees. Loans and deposits make […]

A few posts back we looked at Holding Period Return On Option-Free Fixed Income Securities. I thought we could take that one step further, and show horizon analysis for a bond swap. In order to help explain the calculator, I have divided the analysis into three sections and placed a rounded triangle around each one. The blue […]

I put this spreadsheet together to tell me when the city is going to sweep my street. Obviously, you get a parking ticket if you leave your car parked on the street on those days. The street sweeping days for me are the 1st and 4th Thursday of each month. There are other reasons to have […]

Back in January of this years I wrote a post (Mortgage Pool – Holding Period Return) accompanied by a spreadsheet, on how to calculate the holding period return and the internal rate of return, when buying and selling a pool of mortgages. This post uses the same logic, but is applied to option-free fixed income notes and bonds. I […]

In my last post I talked about how important it is for boards to pay attention to financial ratios and I provided a dashboard to keep track of  the most important ratios. It is also important to follow the trends in these financial rations. Are they getting better or worse? To that end I am providing […]

When I first retired from the position of CFO, I thought I might try and help boards of directors of smaller financial institutions, that may not have a financial background, better understand their obligation to ask management the right questions. Typically, boards are given large board reports with dozens of pages of financial information, with one or […]

I want to revisit the post I wrote earlier in the month called “Payment Required to Earn “X” IRR”. The reason I want to revisit it is because when I said any cash flow can be changed, I meant any . I think this spreadsheet calculator can come in handy in a number of ways. Lets say your firm is […]

I have tried to stay away from VBA calculations for my Excel spreadsheets, principally because my programming does not have the polished look of a good programmer. Programming in VBA for me was self taught, and based on the “Basic” programming language I learned in the 70’s. Enough excuses. Download Excel spreadsheet :”MSR“ There are times when […]

What I mean by “All Variable” is that in previous spreadsheets I included options to make certain variables change throughout the term of the loan. This amortization schedule combines those options into one model. Why have I done this, you say. The answer is because it is my blog, and I can. No other reason that […]

Why do I seem obsessed with amortization schedules?  I  think they make it easier to understand other calculations. In this spreadsheet we will consider the relationship between the Excel XIRR() function (Internal Rate of Return), Present and Future Values, and of course an amortization schedule. The first thing to discuss is the difference between APR (Annual Percentage Rate) and APY […]

Back in December 2014 I wrote a post called “Solve For Last Payment Due“. The idea being that if you wanted to earn a certain return, you could easily calculate with a formula, the final payment required for you to earn that return on the last payment date. That seemed to me to be a useful calculator […]

Back in Oct. 2014, I posted a spreadsheet that calculated the present and future values of a cash flow with even periods and uneven cash flows. I thought I might expand on that spreadsheet by adding a few ways to proof the answers you get using the calculator. These are the formulas used for the […]

In my last post (PV & FV of Periodic Uneven Cash Flows & Rates) I created  uneven cash flows, terms, and rates for the present and future value of an annuity. I decided that I would apply these options to an amortization schedule. Two other posts and spreadsheets that allow multiple changes to amortization schedules […]