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 […]
Author: Don Pistulka
Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.
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 […]
Even Cash Flow Calculator
This worksheet just might make calculating things like loan payments, the amount that could be borrowed, the interest rate, etc. a little easier than the HP-12C calculator, with Reverse Polish Notation, you have been holding onto for the last 30 years. Their are five inputs to the calculator: Rate Cash Flow (Payment) Number of Cash Flows Present […]
Interpolation
There has been an addition to the spreadsheet, explained in the post: Simplified Linear Interpolation of Treasury Rates Back in the day, when I was building a pricing model for mortgages that would be sold to FNMA, I ran into a problem with their pricing tables. The table below shows the problem. The X column […]
APR Calculator for PayDay Loans
Most banks and credit unions offer payday loans. So do many check cashing stores and online sites. You can find payday loan calculators online, but as far as I can find, they don’t give the option of telling you if the fee or interest charged is upfront or at the end of the loan. For […]
Valuing Servicing With CPR
Long before I wrote user defined functions for valuing servicing on a pool of mortgages, I developed amortization tables to understand the cash flows and proof my VBA. The amortization schedule provided below is not the typical amortization. It has a number of uses besides explaining the math of constant prepayment rates (CPR) and servicing. For example, if you […]
Dashboard for Bonuses
After the end of the year, it is time to start thinking about budgets and goals. It is also time to set goals for next years bonuses. The linked Excel workbook shows six year- end objectives (targets), along with a threshold and a stretch for each goal. The threshold is a level not quite up to target, but close enough to warrant […]
Total the amount for each date, on multiple sheets
I ran into this problem in accounting. A workbook was being generated by the general ledger that created separate sheets for each ATM, with dates and deposits. There were a total of 19 ATMs. Individual dates for each sheet were in column A and amounts in column C. The dates did not necessarily show up in the same […]
No XNFV Excel Function?
You might have already used the XNPV built-in Excel function for calculating the present value of a stream of cash flows with dates. How about XNFV for future value? Wait a minute. There is no XNFV. It would have been easy to include an XNFV function, but maybe they couldn’t think of a reason to use […]