Excel Formulas for Private Equity: IPMT

 
 
IPMT excel function.png
 

There are many Excel formulas in private equity and finance that are crucial to master so you can take on any Excel sheet. Many of these formulas can help determine cash flows, valuations, rates of return, and much more. The IPMT function is one the foremost utilized Excel formulas in Private Equity.

What is IPMT?

This IPMT formula gives the interest repayment for the chosen investment for each payment. It is based off of a constant payment schedule and a fixed interest rate. The parts of the function include interest, period number, periods, and amount.

IPMTinExcel.png

=IPMT(interest, period number, periods, amount)

  • Rate is the interest rate per period

  • Per is the period in which you would like to find the interest

  • Nper is the total number of payment periods in the total annuity. This number must be multiplied by 12 if the period is in years and not months

  • PV is the present value of the annuity

  • FV is the future value and will be 0 as the loan will be repaid in full in the future

IPMT excel function.png
 
ipmt excel function.png

As you can see, in this formula there is a PMT function, which utilizes the highlighted boxes in its calculation. It is embedded into a larger formula to solve for the payment.

ipmtintrealestate.png

IPMT in Real Estate

The IPMT formula is utilized in many real estate models to determine the interest repayment for specific investments over a chosen period of time. This is helpful to calculate the payment of recurring and fixed rate investments such as loans for homes. There are a few steps that can be changed to alter the period that is being calculated for. If the interest rate is fixed then there will be no change of the “rate” section, however the period will change based on which period (year, month, quarter) is being solved for.

Conclusion  

The IPMT formula is one of the foremost utilized formulas in private equity and in real estate alike. It calculates the repayment amount in interest for the chosen investment based off of the numbers given for interest, period number, periods, and amount. There are two errors that are common when utilizing the IPMT formula, #NUM! and #VALUE! #NUM! will occur if the value for the PER value is either 0 or less than the NPER amount. The #VALUE! error will occur when any of the arguments given are non-numeric in nature. Overall, if you need to find interest payments in a private equity setting or in a real estate model IPMT is the function to use.

 
IPMT EXCEL FUNCTION.png

About the Author

Brittany Martin is TSM’s Vice President who has developed real estate financial models for an extensive range of property types. She specializes in land, hotel, and storage models. Please reach out to her if you have any questions about IPMT or if she can help you with your modeling needs.

 
Brittany Martin