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

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

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

MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity

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

Average Life VBA Function For MBS

This is the fourth  in a series of VBA, User Defined Functions for Mortgage Backed Securities (MBS). The other three are, solve for price given yield , yield given price and Macaulay Duration . Actually the seventh UDF, if you include the functions solving for the market value of Servicing, IO, and PO). As was true of the previous three functions, it allows […]

Macaulay Duration VBA Function For MBS (or single loans), Given Market Yield

This is the third in a series of VBA, User Defined Functions for Mortgage Backed Securities. The other two solved for price given yield and yield given price. Actually the sixth if you include the functions solving for the market value of servicing, IO, and PO). As was true of the previous two functions, it allows for […]