When to Use CUMPRINC and CUMIPMT in Excel

Have you ever seen references to functions in Excel but weren’t quite sure what they did? The CUMPRINC and CUMIPMT often fall into this category for people. However, once you learn what these two functions can do for you, you’ll be happy you took the time to learn them.

If you have ever bought a home, you probably shopped for a mortgage. Your lender or mortgage broker gave you a rate and an amount they would be willing to lend to you. They evaluated your current financial situation and used that as the basis for their decision.

The standard term for most mortgages is 30 years, but other terms are possible. This helps keep the monthly payments low (on a relative basis.)

Potential buyers often learn how to amortize a loan using a spreadsheet program like Excel. It helps them see how the breakout of principal and interest payments progress throughout the loan. It also helps them see the effects of changes in interest rates on the mortgage. This is useful for comparison shopping for an initial mortgage and for refinancing.

Many loans are quoted in years for terms but are often amortized on a monthly basis. This is the case with most mortgages, too.

How to Find Out Your Monthly Payment

You can use the PMT() function in Excel to calculate your monthly payment.

From Microsoft Excel’s help, here is the syntax:

The parameters are as follows:

• Rate / 12 (since the payments are monthly)
• Number of Periods (nper) x 12 (again, for monthly payments)
• The initial principal value

The last two parameters ([fv] and [type]) are optional and are not often used. The defaults are fv = 0 and type = 0 (for end of period). You will likely not have to worry about these last parameters ever.

To see the PMT function in action, consider a 30-year loan for $250,000 at 3.8% interest:

Your payment for this mortgage would be $1,164.89 per month for the next 30 years, or 360 monthly payments. This doesn’t include property taxes and other fees that may be levied by your municipality.

PPMT and IPMT

If you want to create the entire mortgage amortization schedule, you’ll need to use the PPMT and IPMT functions. Usually, you would number a range of sequential cells from 1 to 360 in the first column. Then, you use the principal and interest functions (PPMT and IPMT) for each line item. The parameters are similar to PMT with the addition of a period for each sequence:

If you added up the principal and interest for each monthly period, the result will be $1184.89. Your total monthly payment stays the same for fixed-rate mortgages.

Time to Use the CUMPRINC and CUMIPMT Functions

It’s time to set aside your residential mortgage hat and put on your real estate investor hat. For residential mortgages, you’ll probably never have to worry about using the CUMPRINC and CUMIPMT functions. But real estate investors find them to be quite useful.

Investing in real estate relies on cash flows from rents. There are other factors, but rent is the core business revenue driver. If several renters stop paying rent or skip out on their lease, the real estate investment could wind up with a short fall. One or two months may not be a showstopper, but any extended period and the investment may suffer considerably.

If there are problems with the investment, you’ll want the ability to find which months are starting to show stress. However, combing through dozens of properties on a month-by-month basis is labor intensive. Remember that stresses may occur at different times for different tenants. They won’t all stop paying on the same month.

What’s needed is a high-level view to see any anomalies and then you can use that information to analyze the more granular data like months. Most real estate investors will start at the yearly summarized level and work from there.

The problem is properties are amortized on a monthly basis, even for investment properties. This means we need a way to roll up the monthly information into a summarized year view.

Without these two functions, we would need to subdivide each set of monthly buckets for each year and then find those summarized amounts and add them to another report view. This is for both principal and interest columns. That’s not exactly an easy feat to handle.

CUMPRINC() and CUMIPMT() do all the dirty work for us.

=CUMPRINC(rate / 12, nper * 12, pv, Start_period, End_period, 0)

=CUMIPMT(rate / 12, nper * 12, pv, Start_period, End_period, 0)

The first three parameters are the same for PMT(), i.e., the rate, nper, and pv. The next two parameters are the starting period and the ending period. For the first year, this would be 1 and 12 respectively.

It gets a little trickier when you get past that first year. But with a bit of creativity, you can set it up so that the formula can be copied for all periods.

Consider the following:

Here is one way to generate the correct start and end periods for the functions:

Start_period = Period * 12 – 11

End_period = Period * 12

For period 1, the start_period would be 1 x 12 – 11 = 1
The end_period would be 1 x 12 = 12

For period 2, the start_period would be 2 x 12 – 11 = 13
The end-period would be 2 x 12 = 24

This schema will work for as many twelve-month buckets you need to roll up using the CUM… functions.

Both the CUMPRINC() and CUMIPMT() would use this schema for the start and end period:

=CUMPRINC(rate / 12, nper * 12, pv, Start_period, End_period, 0)

=CUMIPMT(rate / 12, nper * 12, pv, Start_period, End_period, 0)

This schema is not the only way to handle the start and end periods. Feel free to use your own creative juices to find some other method. But this does work!

To see how the this works, we'll show an amortizing loan with 12 years but only show information for the first twelve months. The first method requires using the PPMT() and IPMT() for each month for the twelve months and then summing these to get the yearly principal and interest.

The second method uses CUMPRINC() and CUMIPMT(). These two functions accomplish the same result as the first, i.e., using PPMT() and IPMT() for each line and then summing all of them. Remember, this analysis is only for one year for one loan. Imagine if you had 30 years and 20 loans. These two functions are quite helpful!

Still Need Monthly Amortization Information

The rolling up allows us to quickly determine when loans start stressing. The totals will seem out of line with the parameters of the deal. Then, you can drill down to find which investments to take action on.

Conclusion

Working with time series data is often a challenge, which is why it’s sometimes easier to absorb the knowledge in small chunks. The CUMPRINC and CUMIPMT are useful functions to help you when you need principal and interest payments aggregated to a higher level, likely yearly.