## Puzzle #1: Mortgage Average Life

Here is the first in a series of puzzles that might have something to do with finance or math. The first to respond with a correct answer will be recognized in The Well-Tempered Spreadsheet Hall of Really Smart People.

First, let’s be clear about the concept of average life of a cash flow. The average life is the average amount of time from a starting date to future cash flows, as weighted by those cash flows. For example, if there is a \$100 cash flow in 1 year and a \$300 cash flow in 2 years, the average life is (1 year x \$100 + 2 years x \$300)/\$400 = 1.75 years.

Suppose there is a \$100,000 mortgage at 5% with monthly payments of \$536.82 for 30 years. Each monthly payment breaks down differently into a principal payment and an interest payment.

Here’s the question:

What is the average life (in years) of the principal payments?

Not hard, just a pain to set up a 360 row mortgage schedule and multiply the principal payments by the month numbers, etc.

But here’s the catch: You can’t use a spreadsheet! This is an unspreadsheet problem, as the Mad Hatter might say.

You must find the answer with no more than five calculations on a basic calculator.

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

Other Puzzles: Puzzle #2, Puzzle #3 (sequel to Puzzle #1), Puzzle#4

This entry was posted in Fun, Math, Puzzles and tagged , , , , , , . Bookmark the permalink.

### 21 Responses to Puzzle #1: Mortgage Average Life

1. Ronald W. Page says:

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

Like

2. Win Smith says:

We have a winner! Ronald W. Page has solved the puzzle.

I will hold his solution until Monday, so let’s see who else can do it!

Like

• Ken says:

How would you calculate this for the next month? Would you use the original balance and payment or would you use the new balance and last payment (assuming there were prepayments and payment was not the same)?

Like

• Win Smith says:

Ken,

I’m afraid I don’t understand your question. Could you explain in more detail?

Thanks,

Win

Like

3. Kenny Chew says:

Hi Win

That is a very interesting challenge that you have posted up. It certainly does challenge a person, who uses Excel on a daily basis – me, to sit down and write down the problem and come up with a solution.

The solution to your problem is first solving the arithmetic progression of 1 to 360 and multiply that with the monthly repayments.

180(1+360) x 536.82 = 34,882,564

Then it is a matter of dividing that by monthly repayment multiply by 360

536.82*360 = 193,255

The solution is then 34,882,564/193,255 = 180.5 months = ~15 years

Kenny

Like

• Win Smith says:

Kenny, thank you very much. Please note though that I am calling for the average life of the principal part of the total payments. As you know, the principal payments start small and grow each month until they represent almost all of the total payment by the end.

Your calculation reminds me of the famous story about young Gauss whose schoolmaster posed a problem he was sure would keep them quiet for a while. He told the class to add up the numbers 1 through 100. Gauss thought for a moment, wrote down the answer, and brough it up to the teacher. Gauss realized that the sum could be written with fifty pairs of numbers:

(1+100) + (2+99) + (3+98) + . . . = 50*101 = 5,050.

This generalizes to the formula n*(n+1)/2 that you used.

Best of luck if you would like another go!

Like

4. Win Smith says:

I’m going to extend this contest to Friday, Feb. 18 to give more people a chance. Thanks!

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.