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.
The answer is:
(536.82 x 360 – 100,000) /(100,000 * .05) = 18.65 years.
The formula calculates total interest by subtracting the principal ($100,000) from the total payments ($536.82 x 360). The total interest is then divided by one year’s interest ($100,000 x 5%) to get the average amount of time that the principal is outstanding. This is the same as the average life of the principal payments. For a more complete discussion of Puzzle #1 and its implications, please see Puzzle #1: Epilogue.
Another example would be if the mortgage were 15 years, with a monthly payment of $790.79. Adapting the above formula, we get an average life of:
(790.79 x 180 – 100,000) /(100,000 * .05) = 8.47 years.
Now suppose the original mortgage (30 years, monthly payment of $536.82) pays on schedule for 15 years but then ends with a balloon payment. Can you figure out the principal average life, again with only a few basic calculations? Good luck!
Related Post: Puzzle #3: A Better Answer
Other Puzzles: Puzzle #1, Puzzle #2
Copyright 2011. All rights reserved.
The Well-Tempered Spreadsheet 
Let $P be the principal of amortizing mortgage paid back entirely in 15 years with monthly payment of $536.82.
P = { 536.82 * ( (1+0.05/12)^(15*12) – 1) } / { (0.05/12) * (1+0.05/12)^(15*12) }
~ $67,884
This mortgage of principal $P has an average life of 8.47 years. The remaining principal (100000 – 67884) ~ $32,116 is paid at the end of 15 years in one balloon payment along with remaining interest payable till the end of 30 years. Hence for total $100,000 mortgage, average life is
WAL = {(67884 * 8.47) + (32116 * 15)}/100000 ~ 10.57 years
LikeLike
Ashwani,
Thank you very much for working on my puzzle. I’m sorry though but your solution does not quite work. Remember that during the first 15 years, the $536.82 payment includes interest on all of the principal, including the balloon payment. So it is not possible to amortize $67,884 during the first 15 years.
I’m sure if you think it over a little more you will come up with the correct solution. Only a few calculations are needed, and exponents are not necessary.
Regards,
Win
LikeLike
Thanks for your comments Win, I see the mistake in my first solution. So here is another trial.
In this mortgage, every month a part of the initial principal P = $100,000 is paid back till 30 years and these part principal payments form a geometric progression with ith term as
term_i = a*r^(i-1)
for i = 1, 2, …, 360 where
a = 536.82 – {(0.05/12)*10000} = 120.1533
r = 1 + (0.05/12) = 1.0042
In the first 15 years, i.e. 180 months, principal paid back is sum of term_i for all i = 1 to 180, lets call this P1
P1 = P/(1+r^180) = $32,115.66
By simple algebra, WAL for P1 is, say WAL1,
WAL1 = [1 – {(180*a*r^180*(1+r^180))/P}]/[1-r]
= 8.47 yrs
Principal paid at the end of 15 years in a balloon payment is, say P2,
P2 = 100,000 – P1 = $67,884.34
with WAL, say WAL2 = 15 years
Hence, net
WAL = {(WAL1*P1)+(WAL2*P2)}/P
= 12.9 yrs
Does this look right?
LikeLike
Ashwani,
Your answer is correct and impressive. But there is a much easier way to get there, with no exponents needed. If you look closely at the original post, as well as your first response, I think you’ll see a simpler method.
Best,
Win
LikeLike
Thanks Win. I know it had few calculations with derived formulae, though no spreadsheet was used. Here I try again, with fewer calculations and no exponents. 🙂
Let $X be the principal (part of $100,000) paid back by the end of 15 years in 15*12 = 180 equal EMIs of $536.82.
[ Note: WAL of $X would be 8.47 years. This conclusion is from the example in your post above, with 15 years version of the same mortgage. ]
Remaining part (100000 – X) is paid as a balloon payment at the end of 15 years.
Let N be the new WAL of the total principal, $100,000.
It should hold,
N = {(X*8.47) + ((100000-X)*15)} / 100000
Also, we know that the interest part we have paid by EMIs in 15 years, (180 * 536.82) -X, is the only interest we have paid so far, which changes the WAL of original principal, $100,000, to N. So
N = {(180 * 536.82) -X}/(100000*0.05)
Solving above two linear equations in N and X, we get
X = $32,112.25 and N = 12.9 years.
LikeLike
Ashwani,
This is brilliant work. I am learning from you.
If you are willing to try one more time, my simplistic solution takes seven operations that could be done on a basic calculator. It will seem pretty trivial after what you’ve done.
Remember, you can use any of the information in the original post.
Best,
Win
LikeLike
Four basic operations,
WAL = 18.65 – (536.82/790.79)*8.47 = 12.9 years
Lets see if somone can explain this. 🙂
LikeLike
Ashwani, that is elegant, and it beats my solution, which takes 7 steps:
( (536.82/790.72)*100,000+180*536.82 – 100,000 )/(100,000*.05) = 12.90
The key to my solution is that we are provided information about both 15- and 30-year mortgages. We can use the 15-year information not only to think about the first 15 years, but also about the last 15 years of the 30-year mortgage that are eliminated by the balloon payment. If the payment in the last 15 years were $790.72, we could amortize $100,000. But the payment is only $536.82, so multiplying the ratio 536.82/790.72 by $100,000 gives $67,890.02 as the amount that the 30-year mortgage amortizes in its last 15 years. This has to be the balloon payment at 15 years. We then add the first 15 years of scheduled payments to get total payments. Then we subtract the total principal to get total interest. Dividing by annual interest, we get the correct average life.
I worked out the equivalance of our formulas but I don’t yet have an intuitive explanation for your solution.
Thanks for the new challenge!
LikeLike
An explanation for Ashwani’s solution occured to me in the shower this morning. The way his solution works is truly elegant.
Ashwani, why don’t we give everyone a few more days to give it a try, and then would you post your explanation? Thanks!
LikeLike
Poking my nose.
Could you please tell me why Shiwani needs a balloon payment at the end of 15th year. If that is the case then the monthly instalment (interest + principal) will go up also.
What I understood from Shiwani’s; she wants to pay $536.82 (calc for 30yrs) as a monthly instalment and at the end of 15th year she want to pay outstanding balance incl. interest & principal as a balloon payment.
LikeLike
Khurrum,
Thank you for your interest. It seems like you are wondering about the premise of this puzzle. It could be that the mortgage was designed to pay for 15 years, but at the payment level of a 30 year mortgage, with the remaining balance paid at the end of the 15 years. This kind of loan structure is unusual for residential mortgages, but there are some commercial loans that work like this.
Another possibility is a scenario with a simple 30-year mortgage, but we assume that the homeowner exercises his or her right to pay off the mortgage at 15 years.
There is no need to increase the monthly installment. It already includes all of the interest due on the $100,000 loan.
By the way, Ashwani did not pose the puzzle, but he has provided brilliant solutions.
Thanks,
Win
LikeLike
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: Puzzle #1: Epilogue | 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 #2: Average Life of Mortgage (Amortizing with Balloon) | The Well-Tempered Spreadsheet
Pingback: Fast Formulas #3: Pool Average Life with CPR Prepayments | The Well-Tempered Spreadsheet