12 Feb 2020
A friend is trying to come up with a clever way to use Excel
CUMPRINC to figure out in what month a given mortgage’s principal would have dropped below 80% of the purchase price of the home (at which point they would know longer pay a supplemental fee known as private morgtage insurance, a.k.a. PMI).
If you took out a 30-year mortgage at 3.625%, borrowing $225,000 to pay for a $250,000 house, you pay PMI until your principal drops below 80% of $250,000 (that is, until you’ve paid $25,000 of principal).
Excel – not sure it can be done
Playing around with Excel, I realized that this might be asking a bit much of Excel – it seems to be more of a calculus problem (measuring things as their rate of change changes) than an algebra problem (plain-old “solving for X” where the equation doesn’t keep changing on you).
After all, you’re basically trying to solve for X, where X is the 2nd-to-last parameter of CUMPRINC, and you already know the cell’s output value (25,000).
What you’re trying to do is find the smallest X where
=CUMPRINC(0.03625/12, 30*12, 225000, 1, X, 0) results in a value greater than or equal to 25,000.
I’m no Excel whiz, but I’m not convinced you can compute a suggested parameter to
CUMPRINC by using
CUMPRINC’s job is to sum the “principal paid this month” values across a specific range of cells from an “amortization table.”
- In this case, you’d just always start with the “first month’s” cell.
CUMPRINCexpects you to tell it which cell of the “amortization table” to stop on – it doesn’t expect to have to figure that out for you.
Recently on the radio, I heard it explained that:
- Algebra is for answering, “If you leave your house at 40 miles per hour and your friend leaves at 30 miles per hour, heading towards each other as the crow flies, when and where will you meet?”
- Calculus is for answering, “If your car can accelerate ‘zero miles per hour to sixty miles per hour in 10 seconds,’ in how many seconds will you have gone 30 feet if you ‘floor it’ from a dead stop?”
This definitely seems like that 2nd kind of question.
Python – can definitely be done
In an imperative programming language, however, we can take advantage of the fact that the “acceleration” isn’t quite like accelerating a car – instead, the “acceleration” of paying principal is broken up into 360 discrete occurrences of paying a lump-sum.
We can cheat.
This is a pretty straightforward “loop over every possible value from 1 to 360 until you get there – then announce which ‘X’ got you there” algorithm.
Right now, you can use the
ppmt() function to behave like
CUMPRINC behaves when the start/end month are set to the same number.
- Note that NumPy plans to take
ppmt()out out of its available functions, so set yourself up with
numpy-financialwith a hyphen) instead.
Building upon Pythontic.com’s code at “Calculating Principal And Interest Payments For A Loan Using Python And Numpy”:
import numpy_financial as npf interestRate = 0.03625 loanDurationMonths = 30*12; principalBorrowed = 225000 salesPrice = 250000 magic80 = salesPrice*.8 #200,000 pmiGoneAfterPdPrnc = principalBorrowed - magic80 # 25,000 def paidThisMonth(whichMonthIsIt): principalPaidThisMonth = npf.ppmt(interestRate/12, whichMonthIsIt, loanDurationMonths, principalBorrowed); return principalPaidThisMonth def monthsToPayPrincipalBeyond20(): princPaidSoFar = 0 whichMonthIsIt = 1 while princPaidSoFar < pmiGoneAfterPdPrnc and whichMonthIsIt <= loanDurationMonths: princPaidSoFar += abs(paidThisMonth(whichMonthIsIt)) whichMonthIsIt += 1 return whichMonthIsIt print("Loan amount:%7.2f"%principalBorrowed); print("Loan duration in months:%d"%loanDurationMonths); print("Annual Interest Rate in percent:%2.3f"%(interestRate*100)); magicMonth = monthsToPayPrincipalBeyond20() print("Your principal will have dropped below %7.2f, eliminating PMI, after month #"%magic80 + str(magicMonth))
The output is:
Loan amount:225000.00 Loan duration in months:360 Annual Interest Rate in percent:3.625 Your principal will have dropped below 200000.00, eliminating PMI, after month #67
It’d take just over 5.5 years, in this 30-year mortgage, to be free of Private Mortgage Insurance with a 3.625% rate, a $225,000 loan, and a $250,000 purchase price.
Of course, now I have to teach my friend to install Anaconda and write Python. :)
We’d also make use of Python’s
pandas module to repeat this computation for lots of mortgages they’re considering taking out, feeding the data in from an Excel spreadsheet and writing it out to a new Excel spreadsheet each time the program is run.
If you’re not sure you’re ready to make the move from Excel to Python yourself, have a listen to Chris Moffitt’s appearance on the Talk Python To Me podcast, Episode 200 – transcript also available.
Chris pointed out that he went into much greater detail about computing amortization tables and projecting financial health in the case of mortgages over at his blog, Practical Business Python, a few years ago – go see his post, Building a Financial Model with Pandas