Back when financial institutions wanted your money and actually competed for your deposits, there were all kinds of gimmicks to entice you to deposit your money with them. One such gimmick was a credit union that was offering to pay the interest upfront on their certificates of deposit (CDs). If you are reading this blog, I probably don’t have […]
Month: September 2014
Seasonality
Depending on the industry you are in, the seasons will likely play some part in your bottom-line. Back at the credit union, we knew that our deposit flows were affected by the time of year, holidays, and the makeup of our membership, many of whom were teachers. For my example spreadsheet, I used a set […]
Valuing Servicing Matrix
On August 26, 2014 I showed an amortization schedule that could be used for initially valuing servicing. I introduced the use of constant prepayment rates (CPR) in order to get a more realistic pricing than assuming the pool of mortgages or commercial loans would never prepay. Considering the ease of refinancing and the mobility of the labor […]
Annuity
There is nothing special about this annuity calculator except: All input is with sliders Each line of the results are one cell text and formulas The dollar growth and payout balances are charted You can pick from monthly deposits or lump sum deposit Inputs: Outputs: Download workbook “Annuity” from: http://www.pistulka.com/Excel_Shared/ Downloads Written in Excel […]
Decision Tree for Notice of Defaults
After the housing bubble crash, defaults on mortgage loans became a big problem for financial institutions. A large number of homeowners either could not or would not continue to make payments on homes that were “underwater” (home value less than the amount owed on mortgages and other liens). After 90 days of no payments, a […]
Two Mortgage Payoff Tables
Assuming you don’t have a balloon payment on your fixed rate 30-year mortgage, these two tables may be helpful. Table1 will tell you when your mortgage will be paid off, given making extra payments each month. Table2 is the reverse of Table1, in that you give it the number of years you want to have […]
Credit Card Payoff
When you get your credit card bill you might see a box that tells you how long it will take to pay off the balance if you make the minimum payment. This spreadsheet shows the payoff years, give a minimum payment of the greater of: A percent of the balance A dollar amount The percent […]
Sum Amounts Sold During Month & Year
You have a table of products, sales dates, and amounts: Objective: Sum all sales amounts , from the same product, during a certain month and year. Input in yellows cells only, any date within the month and year to be summed and a product code: The answer has two formulas that are the same formula except one […]
Delay Payments?
I don’t know if this calculator will be useful to anyone, but I used it to decide when to start collecting Social Security. I’m sure there are other situations where this spreadsheet may come in handy, although as I write this, none come to mind. My plan was to work until I was 70. So should I take S.S. at […]
Simple Mortgage Refi Break-Even
If you do a search, you will find dozens of mortgage refinance calculators on the internet, most on sites of companies that want to sell you a new loan. The big problem with calculating break-even (when the new lower interest rate, will cover the cost to refinance) is: What happens to the difference in monthly payments Where did the money used to […]
Forward Rates Part 3: Spot Rates
A newer version with four bootstrap methods can be found at: A Forth Way To Bootstrap Spot Rates This method of calculating spot rates is referred to as the bootstrapping method. Each spot rate (or zero coupon) along the Treasury yield curve needs the previous spot rates, in order to discount the current securities coupon payments. […]
Forward Rates Part 2: Forward Yield Table
In the last post, Forward Rates Part 1: Gap Analysis, we calculated the forward rate for a two year fixed rate investment, five years from now. Here is a review of the math: Why is everything multiplied by 2? That’s because we are assuming semiannual compounding. First we calculate the value of $1 in 5 years at […]
Forward Rates Part 1: GAP Analysis
Gap Analysis, sometimes referred to as breakeven analysis, will get us 3/4 of the way to understanding forward rates. There are three different interest rates involved with gap analysis: Term Rate: Security with the longest term to maturity. Head Rate: A shorter maturity alternative to the Term Rate. Tail Rate: A rate that starts at the end of […]
Lump Sum Calculator
Last week I showed a cash flow calculator for even cash flows give dates. This next calculator solves for one time (lump sum) cash flows. Excel has built in functions for all of these, but this one is slightly different , in that it uses dates as inputs for time. The advantage is that all […]