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.

Please show your work. Good luck!

**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

*Copyright 2011. All Rights Reserved.*

### Like this:

Like Loading...

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

LikeLike

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!

LikeLike

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)?

LikeLike

Ken,

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

Thanks,

Win

LikeLike

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

LikeLike

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!

LikeLike

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

LikeLike

How to do incorporate prepayment speeds in the calculation?

LikeLike

Ken, please take a look at the link below. The article explains how to calculate the average life of a pool of mortgages under a constant prepayment assumption:

https://welltemperedspreadsheet.wordpress.com/2011/07/22/fast-formulas-3-pool-average-life-with-cpr-prepayments/

LikeLike

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

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

Pingback: Puzzle #1: Epilogue | The Well-Tempered Spreadsheet

Pingback: Puzzle #3 | The Well-Tempered Spreadsheet

Pingback: Puzzle #2 | The Well-Tempered Spreadsheet

Pingback: Puzzle #2: Follow-up Questions | The Well-Tempered Spreadsheet

Pingback: Puzzle #2: Answers to Follow-up Questions | The Well-Tempered Spreadsheet

Pingback: Puzzle #3: A Better Answer | The Well-Tempered Spreadsheet

Pingback: Puzzle #4 | 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

Pingback: Fast Formulas #2: Average Life of Mortgage (Amortizing with Balloon) | The Well-Tempered Spreadsheet