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.

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.

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


  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!


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


  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



    • 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!


  4. Win Smith says:

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


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

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

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

  8. Pingback: Puzzle #3 | The Well-Tempered Spreadsheet

  9. Pingback: Puzzle #2 | The Well-Tempered Spreadsheet

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

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

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

  13. Pingback: Puzzle #4 | The Well-Tempered Spreadsheet

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s