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 […]
Month: July 2015
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“.
Breaking Down the Mortgage Mega-Formula
Additional update to formula: MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity http://pistulka.com/Other/?p=2384 This post will probably not interest the average reader, but I have been so fascinated by this 40 some year old formula that I stuck away and have recently rediscovered. The formula fits in one Excel cell, calculates the […]
Annuity – Excel Functions and Math
In August of 2014 I wrote a post called “Even Cash Flow Calculator” and published a spreadsheet along with it called “EvenMultCashFlows“. Read “Even Cash Flow Calculator” for more about the spreadsheet. I made some improvements to the spreadsheet, including the math equivalent for each Excel function I used, except IRR() which requires an iteration. The math […]
Defeasance 2.0
Important This post and spreadsheet has been updated. Please click this link to Defeasance 4.0 A reader was asking about my original post on defeasance where I used the Treasury strip yields to discount a stream of loan payments to provide an estimate of the prepayment penalty on a loan that has a defeasance clause […]