An Overview Of IPMT and PPMT Excel Functions – Uses And Drawbacks (Model + Case Study)
First of all, I must tell you that the Q&A is a fascinating and great learning area of the A.CRE Real Estate Financial Modeling Accelerator where participants send us their questions related to the academic and professional training offered by this program.
IPMT and PPMT Excel Functions
The IPMT (Interest Payment) and PPMT (Principal Payment) functions in Excel are used to calculate the interest and principal portions of a loan payment, respectively. So, these functions could be useful in some real estate financial modeling cases, however, these functions have drawbacks. Let’s find out!
The IPMT function calculates the interest portion of a payment for a given period in an investment based on periodic, constant payments and a constant interest rate.
IPMT AND PPMT SYNTAX
The syntax for IPMT() is as follows:
IPMT(rate, per, nper, pv, [fv], [type]); where:
- rate: The interest rate for the period.
- per: The period for which you want to find the interest, and must be in the range 1 to nper.
- nper: The total number of payment periods in the investment.
- pv: The present value, or the total amount that a series of future payments is worth now.
- fv (optional): The future value, or a cash balance you want to attain after the last payment is made. If omitted, it’s assumed to be 0.
- type (optional): The timing of the payment. 0 (or omitted) means payments are due at the end of the period; 1 means payments are due at the beginning of the period.
For example, if you have a loan with an annual interest rate of 6% (0.5% monthly), a term of 10 years (120 months), and a present value of $10,000, the interest portion of the 1st payment can be calculated as:
=IPMT (0.005; 1; 120; 10000)
The PPMT function calculates the principal portion of a payment for a given period in an investment based on periodic, constant payments and a constant interest rate.
The syntax for PPMT() is as follows:
PPMT(rate, per, nper, pv, [fv], [type]); where:
- rate: The interest rate for the period.
- per: The period for which you want to find the principal payment, and must be in the range 1 to nper.
- nper: The total number of payment periods in the investment.
- pv: The present value, or the total amount that a series of future payments is worth now.
- fv (optional): The future value, or a cash balance you want to attain after the last payment is made. If omitted, it’s assumed to be 0.
- type (optional): The timing of the payment. 0 (or omitted) means payments are due at the end of the period; 1 means payments are due at the beginning of the period.
Using the same previous loan details, the principal portion of the 1st payment can be calculated as:
= PPMT (0.005; 1; 120; 10000)
I recommend you consult the Bite-Sized Real CRE Lessons – A.CRE 30 Second Video Tutorials and our Glossary of Terms, to clarify any doubts regarding a certain term.
Uses – Total Interest Paid
Both methods will give you the total interest paid over the life of the loan, with IPMT focusing directly on the interest component and PPMT deriving it from total payments and principal repayment.
Regarding the IPMT function, you should calculate the monthly interest for each period, and sum the interest payments for all periods. On the other hand, applying the PPMT function, you should calculate the total principal paid by adding the PPMT across all periods, and then subtracting it from the total amount paid, in other words; Total interest paid = Total amount paid – Total principal paid.
Case Study
Blue Ridge Realty, a real estate private equity firm, is considering the acquisition of Dallas View Apartments, a market-rate multifamily property located in Dallas, Texas. The property, built in 2010, consists of 150 units and has a stable occupancy rate of 95%. Blue Ridge Realty aims to acquire this property as part of its core acquisition strategy, focusing on properties with steady cash flow and minimal risk.
Loan Details:
- Acquisition Price: $30,000,000
- Loan Amount: $24,000,000 (80% LTV)
- Interest Rate: 4.5% annual (fixed)
- Loan Term: 10 years (120 months)
- Interest–Only Period: 3 years (36 months)
- Amortization period: 30 years (360 months)
To understand the financial implications of the loan, the investment manager at Blue Ridge Realty needs to calculate the total interest paid over the life of the loan, therefore using Excel’s IPMT and PPMT functions, the manager will determine the interest component of each payment and the total interest paid.
Do you want to create a real estate financial modeling case study that perfectly suits your educational or professional needs? Check out our A.CRE Real Estate Case Studies Creator Assistant!
Using IPMT Function:
In short, the IPMT function in Excel calculates the interest portion of a payment for a given period. By summing the interest payments for all periods, the total interest paid can be derived.
Using PPMT Function:
The PPMT function calculates the principal portion of a payment for a given period, in this sense, to find the total principal paid, the manager adds the PPMT across all periods. The total interest paid is then the difference between the total amount paid and the total principal paid.
MODELING TOTAL INTEREST PAID – MONTHLY PERIODS
This case study was modeled using an amortization schedule table to detail the principal, interest, and loan balance in each period of the process of paying off debt over time through regular payments. The loan amortization is also modeled using IPMT and PPMT functions showing different methods for the total interest paid calculation.
You can download it at the end of this post.
After performing these calculations in Excel, the investment manager finds that the total interest paid over the life of the loan is approximately $10,341,084.
You can note how in the ‘Monthly Basis – Comparative’ tab, the ‘Interest’ (Column F) is equal to ‘IPMT (Interest)’ (Column J), the ‘Principal’ (Column G) is equal to ‘PPMT (Principal)’ (Column K), so the ‘Ending Loan Balance’ (Column H) has the same performance using the ‘IPMT & PPMT’ functions (Column L).
MODELING LOAN PAYOFF DRAWBACKS – ANNUAL PERIODS
Now, IPMT and PPMT Excel functions have drawbacks, in particular, they are not recommended with annual periods when payments are monthly due to resulting loan payoff amounts that are not correct. Let me take the same case study to visualize that!
The ‘Annual Basis – Comparative’ tab calculates the ‘Debt Service’ and ‘Loan Payoff’ each year. You’ll note that the loan payoff amount in this tab (Row 7) using the PV method matches the amortization schedule at the end of every payment (‘Monthly Basis – Comparative’ tab, specifically, in the ‘Amortizations Schedule’ section – ‘Ending Loan Balance’ – Columns H & L).
However, when we use the IPMT and PPMT Excel functions using an annual basis (‘Annual Basis – Comparative’ tab – Row 17) the results don’t match, as shown in the ‘Comparative Analysis’ section (‘Annual Basis – Comparative’ tab – Row 24).
FINAL NOTES
By understanding the total interest paid using the different calculation methods, Blue Ridge Realty can better assess the long-term financial impact of the loan used to acquire Dallas View Apartments. This knowledge helps in making informed investment decisions and managing cash flow effectively.
Due to the drawbacks behind the IPMT and PPMT Excel functions, I would recommend going another way than having to use them in real estate financial modeling. The drawbacks related to the annual basis may be related to the compounding effect of monthly payments calculated annually and/or the interest calculation method built into the functions.
OVERVIEW – IPMT AND PPMT COMPARATIVE ANALYSIS MODEL – ANNUAL & MONTHLY BASIS
The IPMT and PPMT Comparative Analysis Model includes one primary inputs tab called ‘Monthly Basis – Comparative’, one primary report tab called ‘Annual Basis – Comparative’, and a tab to track version changes to the model called ‘Version’.
VERSION TAB
The model opens initially to this tab so you can see what changes have been made in the most recent version of the model. On this tab, you can also find links to model tutorials, guides, support, and other information.
MONTHLY BASIS – COMPARATIVE TAB
The Monthly Basis – Comparative tab is broken up into three sections, built from top to bottom. The sections can be accessed either by scrolling down to each. The three sections are ‘Loan Assumptions, ‘Total Interest Paid Calculations’, and ‘Amortization Schedule’.
This tab is where all of your primary loan inputs are entered. The tab models an amortization schedule detailing the principal, interest, and loan balance in each period of an amortizing loan. The loan amortization is also modeled using IPMT and PPMT Excel functions.
ANNUAL BASIS – COMPARATIVE TAB
The Annual Basis – Comparative is broken up into four sections, built from top to bottom. The sections can be accessed either by scrolling down to each. The four sections are ‘Loan Assumptions’, ‘Debt Service and Payoff’, ‘IPMT and PPMT Excel Functions’, and ‘Comparative Analysis’. It also models ‘DSCR’ and ‘Debt Yield’ using a hypothetic NOI.
This tab is where the debt service and loan payoff are modeled on an annual basis. Based on the primary loan inputs (Monthly Basis – Comparative tab), it models the IPMT and PPMT Excel functions drawbacks with annual periods when payments are monthly, due to resulting loan payoff amounts that are not correct.
DOWNLOAD THE IPMT AND PPMT COMPARATIVE ANALYSIS MODEL – ANNUAL & MONTHLY BASIS
To make this model accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you need) or maximum (your support helps keep the content coming – typical real estate development models sell for $200 – $500+ per license). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.
