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

If you search my blog, you will find dozens of amortization schedules produced by formulas. So why would you need an amortization produced with VBA? Every time you change any inputs, you have recalculate the schedule, while all my other amortizations are automatic. I created this spreadsheet years ago, just to practice my VBA. Even […]

This is not an academic or financial post, but a fun look at the large number of possible paths available in the binomial lattice used in certain types of financial models. Although there is a small model in the accompanying spreadsheet, this post is mostly concerned with calculating the number of paths and the probability […]

James was searching the web, when he ran across my Taxes workbook and the sheet called Required Minimum Distribution (RMD). He was looking for an Excel spreadsheet that calculated the net balance each year on tax differed accounts (IRR, 401k, etc.) after the return on investments and the withdrawals due to RMD. Rather than starting […]

A reader came across my post called  Loan Pool Named Formulas, Without An Amortization Schedule . This post and spreadsheet showed how, by using mathematical formulas, any month in a mortgaged backed security amortization schedule can be calculated, without creating the whole amortization schedule. The reader asked if the formulas could be adjusted to calculate the interest on a loan for any given […]

First a Warning!! To use this spreadsheet, you need excel 2013 or higher, due to the use of table slicers (not to be confused with pivot table slicers). Internal Audit (IA) frequently reports to the board or supervisory committee. Typically, these groups are concerned with the areas of the firm that have the highest risk to […]

For those that follow my blog, you know that I have used amortization schedules extensively in my analysis. This post and spreadsheet takes a closer look at the default and loss severity assumptions. In previous spreadsheets, the defaults and loss severity have taken place in the same month. In other words, there has been no delay […]

First a warning. To use the linked spreadsheet, you need Excel 2013 or higher, due to the use of table slicers (not to be confused with pivot table slicers). This spreadsheet is an Excel dashboard that can be used to analyze U.S. Gross Domestic Product. The percentage change in GDP from the preceding period is the headline number most […]

I was recently asked to provide a spreadsheet that would calculate the add-on interest rate (APR) that would allow the originator of a mortgage to pay for the origination expenses (title, escrow, etc.) of the borrower. Typically, the borrower would just add the expenses to the loan balance, but there may be reasons why the borrower does […]

A few weeks ago, I wrote a post called “Valuing a Mortgage Portfolio With Cash Flow Analysis” along with a spreadsheet using VBA to calculate the cash flows of a loan portfolio. Ideally, if an amortization schedule for each loan could be created and the cash flow matched, this would be a proof that the VBA cash flows […]

You can tell that I am running out of ideas to create new spreadsheets. I’m back to creating tax calculators. This one is concerned with the required minimum distribution (RMD) that everyone with an IRA or 401K will some day need to use. When you reach 70 1/2 (not 70 years old but 70 1/2 years […]

I added another calculator to the Taxes spreadsheet. The sheet is call “Withdrawals”. It is a simple calculator, but someone might find it helpful. I was asked by a friend (over 59 years old) how much he would need to withdraw from his IRA to pay for his monthly rent, net of taxes. So I decided to make […]

This Excel spreadsheet could help small to medium size banks, credit unions, and mortgage investors, to evaluate their mortgage portfolio. It could also be used for commercial and consumer loans as long as the user understands that the model uses a 30/360 mortgage day count. The difference between valuing a portfolio of mortgages and mortgage backed […]

I have made adjustments to the MBS mega formula that calculates the price of a mortgage-backed security. It now allows for default rates (CDR) and loss severity. In the past I have made other adjustments and I have included a review of these adjustments in the spreadsheet linked below.  I’m not sure who will use this […]

In 2014 I wrote a post and Excel workbook called Tax Calculators, in which I described some of the calculations I had run across during my career. One of the sheets was a taxable equivalent sheet. The sheet was full of tables describing various equations for different equivalent rates. The sheet was bloated and even I had trouble […]