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

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

## Tax Equivalents

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

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

## Market Yield VBA Function For MBS (or single loans), Given Price

If this post looks familiar, it is supposed . My last post was “Market Price VBA Function For MBS (or single loans), Given Yield“. This Excel spreadsheet is almost the same function, except that this function solves for yield given price rather than price given yield. The main difference in the two functions is that yield can’t be solved directly through […]

## Market Price VBA Function For MBS (or single loans), Given Yield

I now also have another function to calculate yield given price see: Market Yield VBA Function For MBS (or single loans), Given Price Many of my previous posts and Excel spreadsheets have included amortization schedules that can calculate the price of MBS pools or single loans, given a discount rate (market yield). This spreadsheet includes […]

## Loan Pool Named Formulas, Without An Amortization Schedule

In a recent post I showed math formulas for a pool of mortgages that could calculate any amount, for any month, without an amortization schedule. This post and spreadsheet takes these formulas one step further and creates names for the formulas. After entering a description of the loan pool: Enter the month, in the future, that […]

## Market Value Functions for Servicing (MSR), Interest (IO), & Principal (PO)

Last year I posted an Excel workbook with a user defined function that calculated the market value of Mortgage Servicing Rights (MSR). I have taken the same workbook and added two more functions. These two functions are the market values of interest only (IO) and principal only (PO) on a mortgage loan. I am including the function […]

## When Cash Flows Don’t Match Compounding Periods

In a previous post Simple Formula for Converting Compound Interest Rates and spreadsheet, I showed a conversion formula that allowed the user to convert an interest rate from one compounding period to another equivalent compounding period. For example, you could calculate the annual equivalent for a monthly compound rate, or the semiannual equivalent rate of a quarterly compound rate, […]

## Variable Rate Amortization – Day/Year Count & Last Payment Options

Remember to enter dates and rates in yellow cells only. The worksheet is protected, except for the yellow input cells, so you are not tempted to type over a formula. There is no password, so you can unprotect the sheet. Back in November of 2014 I posted “Amortization Schedule With Variable Rates“, and an Excel […]

## Solve for return needed for one more period to equal a required APY

Back on September 8, 2015 I had a post call Chained Returns with an Excel spreadsheet. It was a calculator for chaining together periodic rates of return and calculating the annual yield (APY) and the periodic equivalent rate of return (APR). The data inputs (yellow cells) looked like this: After you entered the periods (fractions […]