PV & FV of Periodic Uneven Cash Flows

In the last post we looked at graduated annuities, where the cash flow changes at a given rate. Imagine you need the present value of an annuity with a cash flow that changes unevenly and that change stays the same for certain periods. Take for example the cash flow below: Here we have a 10-year annuity that pays $1,000 each month for […]

Approximating Duration, Modified Duration, and Convexity with Option Free Bonds

The use of Excel’s PRICE function can eliminate much of the math required to understand how modified duration and convexity can be used to predict the price change in option-free bonds. The spreadsheet for this post is called “Option Free” and like all of my spreadsheets, it can be downloaded for free. The sheet is […]

30/360 Present and Future Values with Dates

In my very first post back in August of last year titled “No XNFV Excel Function?” I showed formulas for calculating present value and future values when cash flows with dates are given. These formula were calculated based upon actual days between dates and a 365 day base year. I lamented that Excel has a function that calculates present values with dates […]

Mortgage Pool – Holding Period Return

This spreadsheet was originally compiled to help make the decision to either sell mortgages that were originated, or keep them. It can also be used to help with the decision to purchase a pool of whole loans, or a securitized mortgage pools. Two different concepts of return are referred to in this post. The two are […]

Effective Duration – No VBA or Amortization

Back in October I showed how to calculate effective duration and convexity on a pool of fixed rate mortgages with the help of amortization schedules. In November I posted a formula that could calculate the price of a fixed rate pool of mortgages, including the use of CPR and servicing, in one cell. That same month I […]

California Home Prices Dashboard

The California Association of Realtors® (CAR) publishes monthly home price data for each county in California. I produced a dashboard using their data and the 2010 census for income and population data. In order to keep the dashboard up to date, the “All Counties” sheet stores the data from CAR. There is a link to […]

Purchasing Power – FRED Add-In

If you are interested in the economy, interest rates, stock prices, etc., you will want to get the Federal Reserve Bank of St. Louis Economic Data (FRED) Add-In. The FRED add-in provides free access to over 240,000 data series from various sources (e.g., BEA, BLS, Census, and OECD) directly through Microsoft Excel. I created a spreadsheet […]


Important This post and spreadsheet has been updated. Please click this link to Defeasance 4.0 If you are involved in commercial lending or perhaps municipal bonds you are probably familiar with the term defeasance. Commercial loans normally have hefty prepayment penalties, while some loan documents prohibit prepayment altogether. Commercial loan defeasance is a collateral substitution […]