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

# Category: Uncategorized

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

## Monte Carlo Yield Curve Forecasting

This is a stochastic tool that uses forecasts of what the Treasury yield curve will look like in six months, and generates holding period returns (total returns) at various points out the curve. The results are based entirely on the users best-guess estimates of the shape of future yield curves. This kind of analysis can be helpful in a number of […]

## 24 Time Value of Money Calculations in One Excel Workbook

I thought I would consolidate some of the past posts and spreadsheets into one workbook. It could come in handy when you need some quick answers, or just to check your calculations. I tried to give not only the Excel functions, but the math and alternative calculations. In addition, I included the option to calculate the […]

## 3 -Ways to Bootstrap Spot Rates for the Treasury Yield Curve

Note: I have added a fourth method of calculating spot rates for the Treasury yield curve. The workbook is the same. For a brief explanation of the forth method: http://pistulka.com/Other/?p=2812 I have used what I am calling the “spreadsheet method” of bootstrapping spot rate from the U.S. Treasury yield curve in two pervious posts. […]

## 4-Factor Prepayment Model

In previous posts, I have covered CPR, PSA, and a spreadsheet that allows for any prepayment vector you might want to create. There have been many attempts to describe what the prepayment pattern for MBS will look like in the future. One of the models that has been used extensively, particularly with ALM (Asset / Liability […]

## Over Six Decades of Yield Curve Animation

Important An improved version of this spreadsheet can be found on post: http://pistulka.com/Other/?p=3510 This spreadsheet is mostly for fun, but it does show the volatility of the Treasury yield curve. I took the yields from Federal Reserves H.15 Selected Interest Rates, downloaded using FRED. I used the 3M, 6M, 1Y, 2Y, 3Y, 5Y, 7Y, 10Y, 20Y, and […]

## Mortgage Pool Pricing Tool

In April I publish an amortization schedule and I did a poor job explaining what it could be used for. Amortization Model – All Variable was the post and AllVary is the spreadsheet. I added some more options to the spreadsheet, like: 1. A Yield/Market Value/Price table. 2. Interest Only 3. Amortize out to 40 years. […]

## The Math Behind Excel’s CUMIPMT() & CUMPRINC() Functions

I published the math behind the CUMIPMT() & CUMPRINC() functions at the bottom of my spreadsheet for the post Annuity – Excel Functions and Math. I think it got lost in all the annuity math. When I Google the math for these two functions, my blog doesn’t show up until the 6th or 7th page, depending […]

## Math for Excel Functions With Starting and Ending Values

At the beginning of this month I posted a spreadsheet that gave the math formulas for Excel functions that have even periodic cash flows called Annuity – Excel Functions and Math. This is a reminder that if you need the math equivalent formulas for Excel functions with only two cash flows (starting and ending values), […]

## Simple Amortization Schedule

A reader asked for a simple amortization schedule that could handle both interest only and a balloon payment. You can find it here: “SimpleA“.