MSR (Mortgage Servicing Rights) VBA Function

I have tried to stay away from VBA calculations for my Excel spreadsheets, principally because my programming does not have the polished look of a good programmer. Programming in VBA for me was self taught, and based on the “Basic” programming language I learned in the 70’s. Enough excuses. Download Excel spreadsheet :”MSR“ There are times when […]

Relationships – XIRR(), Amortization, Present & Future Value

Why do I seem obsessed with amortization schedules?  I  think they make it easier to understand other calculations. In this spreadsheet we will consider the relationship between the Excel XIRR() function (Internal Rate of Return), Present and Future Values, and of course an amortization schedule. The first thing to discuss is the difference between APR (Annual Percentage Rate) and APY […]

Time & Dollar Weighted Rates of Return Calculator

Most asset managers will publish their results in both time-weighted (TWRR) and dollar-weighted (DWRR) rates of return. TWRR is typically used by portfolio manages to compare their portfolio’s return to either other managed accounts or to an index. TWRR is more difficult to calculate for individuals because it requires more data. One must have not […]

Amortization – Variable Terms, Rates, & Payments

In my last post (PV & FV of Periodic Uneven Cash Flows & Rates) I created  uneven cash flows, terms, and rates for the present and future value of an annuity. I decided that I would apply these options to an amortization schedule. Two other posts and spreadsheets that allow multiple changes to amortization schedules […]

PV & FV of Periodic Uneven Cash Flows

In the last post we looked at graduated annuities, where the cash flow changes at a given rate. Imagine you need the present value of an annuity with a cash flow that changes unevenly and that change stays the same for certain periods. Take for example the cash flow below: Here we have a 10-year annuity that pays $1,000 each month for […]

Approximating Duration, Modified Duration, and Convexity with Option Free Bonds

The use of Excel’s PRICE function can eliminate much of the math required to understand how modified duration and convexity can be used to predict the price change in option-free bonds. The spreadsheet for this post is called “Option Free” and like all of my spreadsheets, it can be downloaded for free. The sheet is […]

30/360 Present and Future Values with Dates

In my very first post back in August of last year titled “No XNFV Excel Function?” I showed formulas for calculating present value and future values when cash flows with dates are given. These formula were calculated based upon actual days between dates and a 365 day base year. I lamented that Excel has a function that calculates present values with dates […]