I have to be honest. I had never heard of a flat rate loan, until Rishi asked me to create an Excel spreadsheet for one. According to Wikipedia, flat rate loans are: “Loans with interest quoted using a flat rate originated before currency was invented and continued to feature regularly up to and beyond the […]

# Category: Uncategorized

## Required Minimum Distribution (RMD) 3.0

This is my third post on RMD, thus the 3 point 0. The other two post are: http://pistulka.com/Other/?p=2422 http://pistulka.com/Other/?p=2674 Read my first post if you are not familiar with RMD. This Excel workbook came about at the request of a reader who’s firm manages hundreds of retirement plans. I put together a spreadsheet to calculate […]

## Defeasance 3.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 method that guarantees the lender the same cash flow as the loan, in return for […]

## MBS Amortization With VBA

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

## Counting Binomial Lattice Paths

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

## Addition to Required Minimum Distribution

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

## Monthly Interest Using Actual Days, Without an Amortization

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

## Internal Audit – Interactive Report

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

## Mortgage Loan Pool – Default Recovery

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

## U.S. Gross Domestic Product Dashboard

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

## Add-On Interest Rate to Compensate for Financing Expenses

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

## Testing my VBA Mortgage Portfolio Cash Flows

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

## Required Minimum Distribution (RMD)

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

## How much to withdraw from a taxable account (IRA, 401K, etc.)

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

## Valuing a Mortgage Portfolio With Cash Flow Analysis

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