Example of using the Future Value Formula on a Commercial Loan

My first post on this blog was in August of 2014 and it was called: No XNFV Excel Function? The idea was a formula for calculating future value with dates. There was a XNPV function for present value, but no function for future value. My formula looks like this: =SUMPRODUCT((1+APY)^((MAX(Dates)-Dates)/365)*Data) Of course there are other workarounds, […]

Effective Duration & Convexity of a Loan Pool Using My Mega Formula

I have posted previous calculations of effective duration and convexity for a pool of mortgage loans, however past spreadsheets required three amortization schedules, on separate sheets. Using the mega formula for pricing, this spreadsheet (in my opinion) is cleaner and easier to understand. Effective duration is used to analyze mortgage pools, rather then Macaulay or […]

Mortgage Loan Pool Pricing Table with CPR, CDR, & Loss Severity

Before I added default and loss severity to the mega formula for the price of a mortgage pool, I produced a price/yield table. That old post was http://pistulka.com/Other/?p=707.  This post and Excel workbook includes the added variables. As is true with all of my spreadsheets, only the yellow cells are input cells:    * Servicing – […]

A Fourth Way To Bootstrap Spot Rates

In May of 2015 I published a post called “3 -Ways to Bootstrap Spot Rates for the Treasury Yield Curve”. There was also an Excel workbook linked to that post. The workbook link is: http://pistulka.com/Excel_Shared/3_Spot_Rates.xlsm Rather than posting a new workbook, I am leaving the old workbook, but I added a new sheet called “Fourth Method”. […]

Required Minimum Distribution (RMD) 3.0

This is my third post on RMD, thus the 3 point 0. The other two post are: http://pistulka.com/Other/?p=2422 http://pistulka.com/Other/?p=2674 Read my first post if you are not familiar with RMD. This Excel workbook came about at the request of a reader who’s firm manages hundreds of retirement plans. I put together a spreadsheet to calculate […]