Back in January of this years I wrote a post (Mortgage Pool – Holding Period Return) accompanied by a spreadsheet, on how to calculate the holding period return and the internal rate of return, when buying and selling a pool of mortgages. This post uses the same logic, but is applied to option-free fixed income notes and bonds. I […]

# Category: Uncategorized

## Financial Ratio Trends

In my last post I talked about how important it is for boards to pay attention to financial ratios and I provided a dashboard to keep track of the most important ratios. It is also important to follow the trends in these financial rations. Are they getting better or worse? To that end I am providing […]

## Financial Ratio Gauges

When I first retired from the position of CFO, I thought I might try and help boards of directors of smaller financial institutions, that may not have a financial background, better understand their obligation to ask management the right questions. Typically, boards are given large board reports with dozens of pages of financial information, with one or […]

## Any Payment Required, Revisited

I want to revisit the post I wrote earlier in the month called “Payment Required to Earn “X” IRR”. The reason I want to revisit it is because when I said any cash flow can be changed, I meant any . I think this spreadsheet calculator can come in handy in a number of ways. Lets say your firm is […]

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

## Amortization Model – All Variable

What I mean by “All Variable” is that in previous spreadsheets I included options to make certain variables change throughout the term of the loan. This amortization schedule combines those options into one model. Why have I done this, you say. The answer is because it is my blog, and I can. No other reason that […]

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

## Payment Required to Earn “X” IRR

Back in December 2014 I wrote a post called “Solve For Last Payment Due“. The idea being that if you wanted to earn a certain return, you could easily calculate with a formula, the final payment required for you to earn that return on the last payment date. That seemed to me to be a useful calculator […]

## Present and Future Value Proofs

Back in Oct. 2014, I posted a spreadsheet that calculated the present and future values of a cash flow with even periods and uneven cash flows. I thought I might expand on that spreadsheet by adding a few ways to proof the answers you get using the calculator. These are the formulas used for the […]

## 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 & Rates

I am just fooling around with cash flows at this point. My last post was “PV & FV of Periodic Uneven Cash Flows, where I showed an easy way to enter cash flows that changed for different periods of time. I thought it might save time over entering all the cash flows separately. I am not going […]

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

## Graduated Annuities

A graduated annuity is like a regular annuity, except instead of getting the same cash flow each period, the cash flow grows at a given rate throughout the term. There a two basic types of graduated annuities, ordinary annuities (cash flow comes at the end of each period), and annuities due (cash flow starts at the beginning of each […]

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