When faced with modeling a commercial real estate refinancing (replacing an in-place loan by taking a new loan), you don’t always have to rely upon building out a 360-row tall amortization schedule to get the constant debt service payment amount and to solve for the outstanding balance at the point of the new loan being repaid. You can accomplish the same tasks by leveraging Excel’s PMT (constant amortizing loan payment) and CUMPRINC (cumulative loan principal paid) functions.
The PMT function
You can use the PMT function to solve for the periodic debt service payment associated with the new amortizing loan. PMT is very straightforward — you provide the following elements to compute the constant payment:
- RATE (annual interest rate)
- NPER (number of years of amortization)
- PV (present value of the loan i.e., the loan size)
The full PMT function syntax is PMT(rate, nper, pv, [fv], [type])
FV (future value) can be omitted unless you want an outstanding balance to remain after the final payment. Type can be omitted because loan payments are made in arrears, and omitting the inputting of a 1 or 0 for type defaults the value to be = 0, which is the selection for payments made at the end of the month (inputting 1 would mean that payments are made at the beginning of the month).
An example of the PMT function implemented for annual frequency payments is:
=PMT(4%, 30, 100000)
If you want to solve for the monthly frequency payment stream, you need to modify the formula slightly:
- RATE becomes RATE/12
- NPER must be expressed in months (e.g., 30 years x 12 months/year = 360 periodic payments)
The modified monthly version using the same loan as above is:
=PMT(4%/12, 30*12, 100000)
One item to note is that the PMT function will by default return a negative value, so if you want the payment amount represented in your spreadsheet as a positive value, you need to put a negative sign after the = sign (i.e., =-PMT(…)).
The CUMPRINC function
The CUMPRINC function returns the cumulative principal paid on an amortizing loan with constant monthly payments between the loan’s month 1 payment and a specified ending period payment. It can be used to solve for the outstanding loan amount at the point of property sale/loan repayment simply by adding the result to the original loan amount.
The formula syntax is:
=CUMPRINC(rate, nper, pv, start_period, end_period, type)
None of these function elements are in brackets, meaning they are all required to be inputted in to the function.
- RATE is the annual interest rate, or its monthly equivalent, the latter calculated as: annual rate/12
- NPER is the number of years or months of amortization
- PV is the loan size
- Start_Period is = 1 (signifying either year 1 or month 1, depending on the basis on which you are using the function)
- End_Period is = whatever point in time you’re targeting at which to see how much loan principal remains (e.g., end of year 4)
- Type = either 1 or 0, where 1 represent payments made at the beginning of the period, and 0 represents payments made at the end of the period
An example of the CUMPRINC function implemented for annual payment frequency, to find out how much loan principal is paid off over 4 years, is:
=CUMPRINC(4%, 30, 100000, 1, 4, 0)
An example of the CUMPRINC function implemented for monthly payment frequency, to find out how much loan principal is paid off over 48 months, is:
=CUMPRINC(4%/12, 30*12, 100000, 1, 48, 0)
One item to note is that the CUMPRINC function will by default return a negative value, so if you want the cumulative principal amount paid represented in your spreadsheet as a positive value, you need to put a negative sign after the = sign (i.e., =-CUMPRINC(…)).
Examples in Excel are embedded below. To fully interact with the sheet, download it via the download icon along the bottom border of the embed window.