Spreadsheets: Excel: cumprinc() function

The tutor shows an example using Excel’s cumprinc() function, which calculates the reduction of the amount owing over a specific duration.

A question a mortgage holder might ponder: “When I go to refinance after 5 years, how much will I still owe?”

From Microsoft Excel, help is available in the form of the cumprinc() function.

Example:

Imagine a mortgage as follows:

  • payment each month end
  • 4.6% interest compounded monthly
  • 25 year amortization
  • 150,000 principal

What is the remaining balance after 5 years?

Solution:

The cumprinc() function has the following format:

cumprinc(int_per_period, num_periods, present_value, first_period, last_period, when)

Observations:

  • int_per_period, in this case, would be 0.046/12
  • num_periods, in this case, is number of months: 25*12=300
  • first_period, in this case, is 1 (the first month of the five years)
  • last_period is 60 (the 60th month)
  • when means 0 (payment end of month) or 1 (at beginning): 0, in this case

In a cell, the cumprinc() function can be entered as follows:

=cumprinc(0.046/12,12*25,150000,1,60,0)

Hopefully, you’ll receive the result

-17992.7

which means the original balance of 150000 has been reduced by 17992.70. The amount owing after the first five years, therefore, is

150000-17992.70=132007.30

To do it all in one step, one could enter, in a cell,

=150000 + cumprinc(0.046/12,12*25,150000,1,60,0)

The + sign is used because cumprinc() returns an opposite-signed result from the principal.

HTH:)

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Tagged with: , , ,

Leave a Reply