## 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 […]

## Amortization Consolidation

If you have looked at very many of my posts and spreadsheets, it is obvious that a good deal of them involve loan amortization schedules. So does this one. The first sheet in the workbook “consolidate” is called “Amortization Loan”. That is because it contains a standard monthly amortizing loan schedule. There are times when […]

## Solve for a target net worth ratio

Math majors can skip this one. I found it looking through my files and thought it might be useful to some credit union financial managers. The math problem is: How much of a change in capital would it take to move the net worth ratio from the current level, to another level. At first glance it seems […]

## Simple Interest

Both Child Support #1 and #2 have been simplified in order to correct a problem which includes the prepayment of support. This results in the payer earning the stated interest rate on the positive balance prepaid, which is typically much higher than market rates, and may not be allowed in some jurisdictions. Although there are […]

## 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 […]

## Build your own CPR model

Last November I posted an amortization spreadsheet that allowed for variable interest rates on mortgages. I used that spreadsheet and added a CPR variable. That way you can create your own CPR model, rather than using one CPR for the full term or using the PSA model. The default numbers above show the user wants interest rates […]

## 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 […]

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 […]

## Prepayment Penalty

Once upon a time (all my numbers and loans are fictitious) the loan department was negotiating the sale of a parking lot. It seemed that the buyer and we were at a stalemate. We wanted \$5,700,000 for the lot and the buyer was only willing to pay \$5,500,000. The market rate for this type of loan was […]

## Defeasance

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 […]

## Asset Growth vs. ROA – Net Worth Ratio

This concept came from a calculator that was readily available on the internet years ago. I would give credit to the original author, but I don’t remember the name. It was made available online by either CUNA or one of the other credit union associations. I have only embellished it with the addition of charts and a […]

## Why you can’t trust median home prices

This spreadsheet may come in handy if you ever have to explain to someone why you can’t trust home prices published in the media. Typically, home prices are based upon the median sales price. In my area (Sothern California) it is not uncommon to see home prices down 20% or more (YoY) in one city, […]

## Skip-a-Payment

Around this time of year, banks and credit unions will sometimes offer borrowers the option to skip a payment or two in return for a fee. This offering is a spreadsheet that calculates the APR on a loan after the borrower accepts the offer to skip one or more payments. There are two amortization schedules […]

## Simple Formula for Converting Compound Interest Rates

This workbook has a simple formula to convert compound interest rates. =IF(t>500,f*LN(1+g/f),IF(f>=500,t*(EXP(g/t)-1),(t*((1+g/f)^(f/t))-t)))  Where: g = Current interest rate f = Number of times the current rate compounds per year t = Number of times the converted new rate compounds per year This lookup table grabs two of the numbers for the formula Continuous compounding in Excel […]