Puzzle #1 posed the question: given a $100,000 mortgage at 5% with monthly payments of $536.82 for 30 years, what is the average life of the principal payments? The puzzle was to be solved with just a few simple calculations.

Ron Page provided the only correct answer in a comment to Puzzle #1:

(536.82 x 360 – 100,000) /(100,000 * .05) = 18.65

I then asked who could explain Ron’s calculation. Chun Lee NG explained it well in this discussion in the “Financial Modelling in Excel” group in LinkedIn.

The way I would explain the answer is with the formula for simple (not compounding) interest:

*I* =

***

**P*****

**r***, where*

**t*** I* is interest;

*is principal;*

**P***is the interest rate (as a decimal); and*

**r***is time.*

**t**The formula might seem trivial, but it is fundamental. You can use it for isolated calculations such as one month’s interest on a savings account, or the accrued interest on a bond. The formula also applies to complicated financial structures involving only simple interest.

For the mortgage puzzle, we let * t* represent the weighted average time that the principal is outstanding. This is the same as the weighted average life of the principal payments, also known as weighted average life (“WAL”). Now rewrite the formula to solve for

*:*

**t***t* =

*/(*

**I*****

**P***)*

**r**Now we just need * I* in order to find the WAL and solve the puzzle. This why the Clue for Puzzle #1 was “Interest.” To find total interest, note that the monthly payments include both principal and interest. The sum of the payments is 360*$536.82 =$193,255.20. Since the payments include $100,000 of principal repayment, the total interest must be $93,225.20. Divide by ($100,000*.05) and we have a WAL of 18.65 years.

The beauty of this method is that you do not have to go to the trouble of building a full amortization schedule and deriving 360 different principal payments. All you need is a good understanding of these simple but fundamental formulas.

These formulas do not require the payments to be level. You could have any kind of amortization as long as interest does not compound. For example, you could have some principal amortization followed by a balloon payment (hint for future puzzle).

You can apply these formulas to an issue of bonds involving many maturities with simple interest at different rates. If you know the weighted average coupon (“WAC”) and the total debt service, you can figure out the average life of the bond issue. If you know the average life and the total debt service, you can figure out the WAC. If you know the WAC and average life, you can figure out the total debt service.

I can’t overstate the importance of understanding these kinds of basic financial relationships. Once you do, it is much easier to check the reasonability and accuracy of your models. You can also estimate the impact on your model of changes in assumptions.

Full disclosure: Ron Page is a longtime friend and colleague, but I didn’t know he was looking at the puzzle until he posted his solution. Ron learned to analyze bonds using a HP12C calculator, and I bet he can get more out of that calculator than most people can with a spreadsheet.

**Other Posts on Puzzle #1:** Puzzle #1, Clue for Puzzle #1, Answer to Puzzle #1

**Other Puzzles:** Puzzle #2, Puzzle #3, Puzzle#4

Copyright 2011. All rights reserved.

I should caution that the weighted average coupon (“WAC”) can take different meanings in different contexts. For a bond issue, WAC is effectively weighted by both principal and time, but for a pool of loans, WAC is weighted only by principal.

LikeLike

Pingback: Puzzle #3 | The Well-Tempered Spreadsheet

Pingback: Puzzle #1 | The Well-Tempered Spreadsheet

Pingback: Clue for Puzzle #1 | The Well-Tempered Spreadsheet

Pingback: Answer to Puzzle #1 | The Well-Tempered Spreadsheet

Pingback: Fast Formulas #1: Average Life of Mortgage (as Scheduled) | The Well-Tempered Spreadsheet

Pingback: Fast Formulas #1: Average Life of Mortgage (as Scheduled) | The Well-Tempered Spreadsheet