A friend is trying to come up with a clever way to use Excel CUMIPNT / 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.

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.
• But CUMPRINC expects 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.

Why Calculus

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 numpy module's 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_financial (install as numpy-financial with 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))

Result

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.

Next Steps

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.

Further Reading

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