Effective Duration, Convexity, and Convexity Adjustment For MBS

This is an update of a post and spreadsheet I wrote in Oct. of 2014 called: “Modified & Effective Duration, Plus Convexity of a Loan”.  The previous post was hard-wired for a plus or minus 100 basis point change in yields. These new formulas and inputs allow for changes in yield other than 100 basis […]

Simplified Linear Interpolation of Treasury Rates

In August of 2014 I showed a spreadsheet for interpolating a yield, given a range of dollar prices. That post was call Interpolation. I thought of another way to use the same linear interpolation. Lets say you want to know what the Treasury yield would be for an investment. Unfortunately, you only have the current Treasury […]

Math for amortization rows with CPR, Servicing, CDR, & Loss Severity

First, let me say that this is my 100th post and spreadsheet. I started in August of last year. If this spreadsheet looks a lot like my last post, it is very close. My last spreadsheet showed how to calculate any row of an amortization schedule, including starting balance, scheduled  payments, interest, principal, servicing, prepayment (CPR) and […]

Math for amortization rows with CPR and Servicing

  After you read this post, check out my updated post   In my post called The Math Behind Excel’s CUMIPMT() & CUMPRINC() Functions I showed how to calculate a single row of a simple amortization schedule, using math instead of the amortization schedule. In this post and spreadsheet I show how to calculate the starting balance, payment, interest, […]