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

# Category: Uncategorized

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

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

## Amortization Consolidation

If you have looked at very many of my posts and spreadsheets, it is obvious that a good deal of them involve loan amortization schedules. So does this one. The first sheet in the workbook “consolidate” is called “Amortization Loan”. That is because it contains a standard monthly amortizing loan schedule. There are times when […]

## Solve for a target net worth ratio

Math majors can skip this one. I found it looking through my files and thought it might be useful to some credit union financial managers. The math problem is: How much of a change in capital would it take to move the net worth ratio from the current level, to another level. At first glance it seems […]

## Simple Interest

Both Child Support #1 and #2 have been simplified in order to correct a problem which includes the prepayment of support. This results in the payer earning the stated interest rate on the positive balance prepaid, which is typically much higher than market rates, and may not be allowed in some jurisdictions. Although there are […]