Eight years of Retirement (190 Posts)

Since I have retired, I have been publishing Excel spreadsheets, mostly about finance but also about other areas. Many of the older posts have been improved on by more recent posts but are still out there. Some browser searches for subjects I have posted show up on page one or two, but many are buried deep. I thought I would list all 190 posts for those that are following this blog. Hopefully, this will make it easier to look up subjects you are interested in or might have missed:

 

190 Negative Amortization Payoff
189 Watts, Volts, Amps, Ohms Calculator (Excel)
188 Simple Break-Even for Solar Installation. (Excel)
187 Present Value of a Future Annuity
186 Percentage Change in GDP (Scroll Bar)
185 Chart Organization’s Salaries / Bonuses
184 Compliance Risk
183 Automobile Loan Portfolio Model (ABS)
182 Consumer Loan Pricing with Four FICO Scoring Methods
181 U.S Treasury Daily Yield Curves From 1/2/1990-Present
180 Mortgage Pricing Model
179 Sample Amortizations
178 Golf Prize Distribution
177 Daily Treasury Security Rates
176 Amortization With Dates – 12 Payment & 15 Compounding Options
175 Risk Management – Interpolated FICO Scores on Consumer Loans
174 Present Value For Amortizations With Dates
173 Can Forward Yields Predict Future Treasury Yields?
172 Animated Treasury Yield Curves (1962-Present)
171 Commercial Loan Defeasance 4.0
170 Treasury Forward Yield Curve Table – Revised 
169 Yield Curve Smoothing With The Nelson-Siegel-Svensson Model
168 Pricing MBS Against the U. S Treasury Forward Curve
167 Pricing MBS Against the U. S Treasury Spot Rate “Z” Curve
166 Bootstrapping Spot Rates For FHLB Amortizing Advance Rates
165 Birthday Calendar
164 Bi-Weekly Pay Day Calendars
163 Average Life VBA Function For MBS (Using PSA)
162 Budgeting Interest Expense on Certificates of Deposits
161 Troubled Debt Restructuring (TDR) – Present Value
160 Comparing CDR Default Formulas to Industry Standard Default Formulas
159 Assumptions Behind The Default (CDR) Model
158 Simple Interest Commercial Loan Amortization
157 Maturity Weighted Butterfly Swap
156 Regression Weighted Butterfly Swap
155 Fifty-Fifty Butterfly Swap
154 Cash & Duration Neutral Butterfly  (aka Duration-Hedged Barbell)
153 Commercial Loan Amortizations
152 Annual CPR Vectors
151 Simple (SMA) and Exponential (EMA) Moving Average
150 Cherry-Picking a Loan Portfolio, Revisited
149 THE MIRR EXCEL FORMULA WITH DATES (XMIRR)
148 Interest Penalty for Overtime Changes in State Law
147 CPR Prepayment Vectors
146 HEL (Home Equity Prepayment)
145 MHP (Prepayment Rate for Manufactured-Housing)
144 Change to “Calculate Historic CPR and PSA”
143 CMO-Inverse Interest Only (Inverse IO)
142 CMO -IO/PO PAIR
141 CMO – PAC
140 Future Value Bank Interest in Leap Years
139 Example of using the Future Value Formula on a Commercial Loan
138 Effective Duration & Convexity of a Loan Pool Using My Mega Formula
137 Mortgage Loan Pool Pricing Table with CPR, CDR, & Loss Severity
136 Revisiting  MBS Math Formula 
135 A Forth Way To Bootstrap Spot Rates
134 Flat Rate Loans
133 Required Minimum Distribution (RMD) 3.0
132 Defeasance 3.0
131 MBS Amortization With VBA
130 Counting Binomial Lattice Paths
129 Addition to Required Minimum Distribution
128 Monthly Interest Using Actual Days, Without an Amortization
127 Internal Audit – Interactive Report
126 Mortgage Loan Pool – Default Recovery
125 U.S. Gross Domestic Product Dashboard
124 Add-On Interest Rate to Compensate for Financing Expenses
123 Testing my VBA Mortgage Portfolio Cash Flows
122 Required Minimum Distribution (RMD)
121 How much to withdraw from a taxable account (IRA, 401K, etc.)
120 Valuing a Mortgage Portfolio With Cash Flow Analysis
119 MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity
118 Tax Equivalents
117 Average Life VBA Function For MBS
116 Macaulay Duration VBA Function For MBS (or single loans), Given Market Yield
115 Market Yield VBA Function For MBS (or single loans), Given Price
114 Market Price VBA Function For MBS (or single loans), Given Yield
113 Loan Pool Named Formulas, Without An Amortization Schedule
112 Market Value Functions for Servicing (MSR), Interest (IO), & Principal (PO)
111 When Cash Flows Don’t Match Compounding Periods
110 Variable Rate Amortization – Day/Year Count & Last Payment Options
109 Solve for return needed for one more period to equal a required APY
108 Monte Carlo Yield Curve Forecasting
107 24 Time Value of Money Calculations in One Excel Workbook
106 3 -Ways to Bootstrap Spot Rates for the Treasury Yield Curve
105 4-Factor Prepayment Model
104 Floater & Inverse-Floater CMO
103 Sequential Pay CMO
102 Effective Duration, Convexity, and Convexity Adjustment For MBS
101 Simplified Linear Interpolation of Treasury Rates
100 Math for amortization rows with CPR, Servicing, CDR, & Loss Severity
99 Math for amortization rows with CPR and Servicing
98 Calculating Historic CPR
97 Certificate of Deposit Ladder Optimizer
96 Conforming the “Mega” MBS Formula to Street Conventions
95 Chained Returns
94 Rule of 78
93 U.S. Treasury Forward Rate Curves
92 Treasury Bill Calculator
91 U.S. Treasury Bill Math
90 Financial Ratios – Bullet Charts
89 Bond Immunization
88 Default Rates Affect on Servicing
87 Credit Union Certificate Analysis
86 Over Six Decades of Yield Curve Animation
85 Mortgage Pool Pricing Tool
84 The Math Behind Excel’s CUMIPMT() & CUMPRINC() Functions
83 Math for Excel Functions With Starting and Ending Values
82 Simple Amortization Schedule
81 Breaking Down the Mortgage Mega-Formula
80 Annuity – Excel Functions and Math
79 Defeasance2.0
78 Spreads Between the 10-Year Treasury and 30-Year Mortgage
77 Certificate Pricing for Credit Unions
76 Mortgage Pool Price Given Yield, Amortization vs. Mega-Formula
75 Cherry-Picking a Loan Portfolio
74 Mortgage Loan Pool Settlements
73 Bank and Credit Union Board Presentations
72 Bond Swap – Horizon Analysis – Calculator
71 Street Sweeping Days
70 Holding Period Return On Option-Free Fixed Income Securities
69 Financial Ratio Trends
68 Financial Ratio Gauges
67 Any Payment Required, Revisited
66 MSR (Mortgage Servicing Rights) VBA Function
65 Amortization Model – All Variable
64 Relationships – XIRR(), Amortization, PV & FV
63 Payment Required to Earn “X” IRR
62 Present and Future Value Proofs
61 Time & Dollar Weighted Rates of Return Calculator
60 Amortization – Variable Terms, Rates, & Payments
59 PV & FV of Periodic Uneven Cash Flows & Rates
58 PV & FV of Periodic Uneven Cash Flows
57 Graduated Annuities
56 Approximating Duration, Modified Duration, and Convexity 
55 30/360 Present and Future Values with Dates
54 Amortization Consolidation
53 Solve for a target net worth ratio
52 Simple Interest
51 Mortgage Pool – Holding Period Return
50 Build your own CPR model
49 Effective Duration – No VBA or Amortization
48 California Home Prices Dashboard
47 Purchasing Power – FRED Add-In
46 Prepayment Penalty
45 Defeasance
44 Asset Growth vs. ROA – Net Worth Ratio
43 Why you can’t trust median home prices
42 Skip-a-Payment
41 Simple Formula for Converting Compound Interest Rates
40 Standard Default Assumption (SDA)
39 Constant Default Rate (CDR)
38 Convert CPR, SMM & ABS (APS)
37 Solve for Last Payment Due
36 Reverse Engineering Constant Prepayment Rate (CPR)
35 Mortgage Pool Price and Average Life One Cell Formulas
34 Currency Risk
33 Amortization Schedule With Variable Rates
32 Tax Calculators
31 Mortgage Backed Securities (MBS) “MegaFormula”
30 PSA vs. CPR
29 Bonus Checking
28 Macaulay Duration Plus Balloon Payment
27 Dutch Auction
26 APR – Adjustable Rate Mortgage (ARM)
25 APR – Fixed Rate Mortgage
24 PV and FV of Periodic Cash Flows
23 Modified & Effective Duration, Plus Convexity of a Loan
22 Macaulay Duration of an Amortizing Loan
21 Time Value Of Money
20 Seasonality
19 Valuing Servicing Matrix
18 Annuity
17 Decision Tree for Notice of Defaults
16 Two Mortgage Payoff Tables
15 Credit Card Payoff
14 Sum Amounts Sold During Month & Year
13 Delay Payments?
12 Simple Mortgage Refi Break-Even
11 Forward Rates Part 3: Spot Rates
10 Forward Rates Part 2: Forward Yield Table
9 Forward Rates Part 1: GAP Analysis
8 Lump Sum Calculator
7 Even Cash Flow Calculator
6 Interpolation
5 APR Calculator for PayDay Loans
4 Valuing Servicing With CPR
3 Dashboard for Bonuses
2 Total the amount for each date, on multiple sheets
1 No XNFV Excel Function? 
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.

4 Comments

  1. Hi Don,

    Thank you, your spreadsheets helped me through business school and developing a love for Excel.

    Blessings and good health,

    Marnus from South Africa

  2. Always nice to receive these posts. I hope I am as active as you are when I get to retirement! Thanks for sharing your knowledge.

Leave a Reply

Your email address will not be published. Required fields are marked *